package storage

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
)

// ErrAkteNotFound is returned when a tenant-scoped akte lookup/update affects
// zero rows (wrong id or wrong tenant).
var ErrAkteNotFound = errors.New("storage: akte not found")

// Akte is a digital file folder ("digitaler Aktenordner") grouping documents
// in a strict 1:n relationship via documents.akte_id. It has NO own ACL — an
// akte's visibility is derived from the documents it contains (see
// ListAkteDocuments, which applies the exact same document_visibility EXISTS
// clause as ListDocuments). See project_akte_konzept_plan.md.
type Akte struct {
	ID              int64      `json:"id"`
	TenantID        int64      `json:"tenant_id"`
	Titel           string     `json:"titel"`
	Beschreibung    string     `json:"beschreibung"`
	Status          string     `json:"status"`
	CorrespondentID *int64     `json:"correspondent_id,omitempty"`
	CreatedBy       *int64     `json:"created_by,omitempty"`
	CreatedAt       time.Time  `json:"created_at"`
	ClosedAt        *time.Time `json:"closed_at,omitempty"`
	// DocumentCount is a computed field (COUNT of documents with this akte_id),
	// not a stored column. Populated by ListAkten/GetAkte for the list view.
	DocumentCount int `json:"document_count"`
}

// initAktenSchema creates the akten table and adds the documents.akte_id FK
// column. Order matters: the akten table must exist BEFORE the ALTER TABLE on
// documents because akte_id references akten(id). Idempotent. Wired into
// (*Store).initSchema (documents.go) after the other schema hooks.
func (s *Store) initAktenSchema(ctx context.Context) error {
	_, err := s.db.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS akten (
			id               BIGSERIAL PRIMARY KEY,
			tenant_id        BIGINT NOT NULL,
			titel            TEXT NOT NULL,
			beschreibung     TEXT NOT NULL DEFAULT '',
			status           TEXT NOT NULL DEFAULT 'offen' CHECK (status IN ('offen','geschlossen')),
			correspondent_id BIGINT REFERENCES correspondents(id),
			created_by       BIGINT,
			created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
			closed_at        TIMESTAMPTZ
		);
		CREATE INDEX IF NOT EXISTS idx_akten_tenant ON akten (tenant_id);
	`)
	if err != nil {
		return fmt.Errorf("storage: create akten table: %w", err)
	}

	// documents.akte_id: strict 1:n container link. ON DELETE SET NULL is the
	// GoBD safeguard — deleting an akte can NEVER delete documents, it only
	// decouples them (see project_akte_konzept_plan.md).
	_, err = s.db.Exec(ctx, `
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS akte_id BIGINT REFERENCES akten(id) ON DELETE SET NULL;
	`)
	if err != nil {
		return fmt.Errorf("storage: alter documents add akte_id: %w", err)
	}
	return nil
}

// CreateAkte inserts a new akte and returns it (document_count is 0 for a
// freshly created akte).
func (s *Store) CreateAkte(ctx context.Context, tenantID int64, titel, beschreibung string, correspondentID *int64, createdBy int64) (*Akte, error) {
	var a Akte
	err := s.db.QueryRow(ctx, `
		INSERT INTO akten (tenant_id, titel, beschreibung, correspondent_id, created_by)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING id, tenant_id, titel, beschreibung, status, correspondent_id, created_by, created_at, closed_at
	`, tenantID, titel, beschreibung, correspondentID, createdBy).Scan(
		&a.ID, &a.TenantID, &a.Titel, &a.Beschreibung, &a.Status, &a.CorrespondentID, &a.CreatedBy, &a.CreatedAt, &a.ClosedAt)
	if err != nil {
		return nil, fmt.Errorf("storage: create akte: %w", err)
	}
	return &a, nil
}

// ListAkten returns all akten for a tenant, newest first, each with its
// document_count. No ACL filter here: an akte itself is metadata; its contained
// documents are ACL-filtered at ListAkteDocuments time.
func (s *Store) ListAkten(ctx context.Context, tenantID int64) ([]Akte, error) {
	rows, err := s.db.Query(ctx, `
		SELECT a.id, a.tenant_id, a.titel, a.beschreibung, a.status, a.correspondent_id, a.created_by, a.created_at, a.closed_at,
			(SELECT COUNT(*) FROM documents d WHERE d.akte_id = a.id AND d.deleted_at IS NULL) AS document_count
		FROM akten a
		WHERE a.tenant_id = $1
		ORDER BY a.created_at DESC
	`, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list akten: %w", err)
	}
	defer rows.Close()

	out := make([]Akte, 0)
	for rows.Next() {
		var a Akte
		if err := rows.Scan(&a.ID, &a.TenantID, &a.Titel, &a.Beschreibung, &a.Status, &a.CorrespondentID, &a.CreatedBy, &a.CreatedAt, &a.ClosedAt, &a.DocumentCount); err != nil {
			return nil, fmt.Errorf("storage: scan akte: %w", err)
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// GetAkte retrieves a single akte by id, scoped to tenant, with document_count.
// Returns ErrAkteNotFound when no row matches.
func (s *Store) GetAkte(ctx context.Context, id, tenantID int64) (*Akte, error) {
	var a Akte
	err := s.db.QueryRow(ctx, `
		SELECT a.id, a.tenant_id, a.titel, a.beschreibung, a.status, a.correspondent_id, a.created_by, a.created_at, a.closed_at,
			(SELECT COUNT(*) FROM documents d WHERE d.akte_id = a.id AND d.deleted_at IS NULL) AS document_count
		FROM akten a
		WHERE a.id = $1 AND a.tenant_id = $2
	`, id, tenantID).Scan(&a.ID, &a.TenantID, &a.Titel, &a.Beschreibung, &a.Status, &a.CorrespondentID, &a.CreatedBy, &a.CreatedAt, &a.ClosedAt, &a.DocumentCount)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrAkteNotFound
		}
		return nil, fmt.Errorf("storage: get akte: %w", err)
	}
	return &a, nil
}

// UpdateAkte changes an akte's titel/beschreibung/correspondent, scoped to
// tenant. Returns ErrAkteNotFound when no row matches.
func (s *Store) UpdateAkte(ctx context.Context, id, tenantID int64, titel, beschreibung string, correspondentID *int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE akten SET titel = $1, beschreibung = $2, correspondent_id = $3
		WHERE id = $4 AND tenant_id = $5
	`, titel, beschreibung, correspondentID, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: update akte: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrAkteNotFound
	}
	return nil
}

// CloseAkte marks an akte as geschlossen and stamps closed_at, scoped to
// tenant. Returns ErrAkteNotFound when no row matches.
func (s *Store) CloseAkte(ctx context.Context, id, tenantID int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE akten SET status = 'geschlossen', closed_at = now()
		WHERE id = $1 AND tenant_id = $2
	`, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: close akte: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrAkteNotFound
	}
	return nil
}

// DeleteAkte hard-deletes an akte, scoped to tenant. Documents are NOT deleted:
// the ON DELETE SET NULL FK on documents.akte_id automatically decouples them.
// Returns ErrAkteNotFound when no row matches.
func (s *Store) DeleteAkte(ctx context.Context, id, tenantID int64) error {
	tag, err := s.db.Exec(ctx, `DELETE FROM akten WHERE id = $1 AND tenant_id = $2`, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: delete akte: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrAkteNotFound
	}
	return nil
}

// ListAkteDocuments returns all documents assigned to an akte, ACL-filtered
// exactly like ListDocuments (aclUserID non-nil for role 'user', nil for
// domain_admin/superadmin). Same document_visibility EXISTS clause plus the
// created_by ownership fallback.
func (s *Store) ListAkteDocuments(ctx context.Context, akteID, tenantID int64, aclUserID *int64) ([]Document, error) {
	rows, err := s.db.Query(ctx, `
		SELECT id, tenant_id, title, COALESCE(doc_type, ''), COALESCE(correspondent, ''), doc_type_id, correspondent_id, storage_path, content_hash,
			COALESCE(ocr_text, ''), retain_until, COALESCE(source, ''), COALESCE(source_ref, ''), created_by, title_manually_set, created_at, updated_at
		FROM documents
		WHERE tenant_id = $1 AND akte_id = $2 AND deleted_at IS NULL
		  AND ($3::bigint IS NULL OR documents.created_by = $3 OR EXISTS (
			SELECT 1 FROM document_visibility dv
			JOIN permission_group_members pgm ON pgm.group_id = dv.group_id
			WHERE dv.document_id = documents.id AND pgm.user_id = $3
		  ))
		ORDER BY created_at DESC
	`, tenantID, akteID, aclUserID)
	if err != nil {
		return nil, fmt.Errorf("storage: list akte documents: %w", err)
	}
	defer rows.Close()

	out := make([]Document, 0)
	for rows.Next() {
		var d Document
		if err := rows.Scan(&d.ID, &d.TenantID, &d.Title, &d.DocType, &d.Correspondent, &d.DocTypeID, &d.CorrespondentID, &d.StoragePath, &d.ContentHash,
			&d.OCRText, &d.RetainUntil, &d.Source, &d.SourceRef, &d.CreatedBy, &d.TitleManuallySet, &d.CreatedAt, &d.UpdatedAt); err != nil {
			return nil, fmt.Errorf("storage: scan akte document: %w", err)
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

// SetDocumentAkte assigns (akteID non-nil) or removes (akteID nil) a document's
// akte membership, scoped to tenant. Analogous to SetDocumentDocType, but the
// akte is not part of the document ACL, so no visibility recompute is needed —
// only a search-index re-sync. Returns ErrDocumentNotFound when no row matches.
func (s *Store) SetDocumentAkte(ctx context.Context, documentID, tenantID int64, akteID *int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE documents SET akte_id = $1, updated_at = now()
		WHERE id = $2 AND tenant_id = $3
	`, akteID, documentID, tenantID)
	if err != nil {
		return fmt.Errorf("storage: set document akte: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrDocumentNotFound
	}
	s.SyncIndex(ctx, documentID)
	return nil
}