package storage

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

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

// Trash + staged deletion (Papierkorb + gestaffeltes Löschkonzept).
//
// A document with deleted_at IS NOT NULL sits in the trash. Its WORM file on
// disk stays physically untouched (chmod 0440) until a delete request reaches
// status='executed'. Final deletion requires a two-person rule (Vier-Augen-
// Prinzip): one user requests it, a *different* user with domain_admin role
// confirms it. Both the request and the confirm re-check retain_until, since
// time may have passed or a new retention lock may have appeared in between.
//
// On execution the physical file is removed but the DB row is kept as a
// tombstone (GoBD Nachvollziehbarkeit): storage_path and ocr_text are cleared,
// content_hash is retained so the former existence + integrity fingerprint of
// the document stays provable.

var (
	// ErrDocumentNotInTrash is returned when a trash operation targets a
	// document that is not (or no longer) soft-deleted for the tenant.
	ErrDocumentNotInTrash = errors.New("storage: document not found in trash for tenant")

	// ErrAlreadyInTrash is returned by SoftDeleteDocument when the document is
	// already soft-deleted.
	ErrAlreadyInTrash = errors.New("storage: document already in trash")

	// ErrDeleteRequestExists is returned when a pending delete request already
	// exists for the document.
	ErrDeleteRequestExists = errors.New("storage: a pending delete request already exists for this document")

	// ErrDeleteRequestNotFound is returned when a delete request lookup does
	// not match a pending request owned by the tenant/document.
	ErrDeleteRequestNotFound = errors.New("storage: delete request not found or not pending")

	// ErrSelfConfirm is returned when the confirming user is the same as the
	// requesting user (Vier-Augen-Prinzip violation).
	ErrSelfConfirm = errors.New("storage: delete request must be confirmed by a different user")

	// ErrRetentionActive is returned when retain_until still blocks final
	// deletion (retain_until IS NOT NULL AND retain_until >= now()).
	ErrRetentionActive = errors.New("storage: document is under retention and cannot be finally deleted")
)

// DeleteRequest is a single staged-deletion request row.
type DeleteRequest struct {
	ID          int64      `json:"id"`
	DocumentID  int64      `json:"document_id"`
	TenantID    int64      `json:"tenant_id"`
	RequestedBy int64      `json:"requested_by"`
	RequestedAt time.Time  `json:"requested_at"`
	ConfirmedBy *int64     `json:"confirmed_by,omitempty"`
	ConfirmedAt *time.Time `json:"confirmed_at,omitempty"`
	Status      string     `json:"status"`
}

// TrashDocument is a soft-deleted document plus its trash metadata.
type TrashDocument struct {
	Document
	DeletedAt *time.Time `json:"deleted_at,omitempty"`
	DeletedBy *int64     `json:"deleted_by,omitempty"`
}

// ExecutedDelete carries the result of a confirmed + executed final deletion,
// used by the handler to build the two audit entries (requester + confirmer)
// and, crucially, the GoBD-taugliche Löschprotokoll (ecoDMS-Muster): who
// requested, who confirmed, when, which document (title + content_hash as the
// tamper-evident fingerprint of the now-removed WORM file) and the retention
// state at execution time (RetainUntil, expected nil or already elapsed).
type ExecutedDelete struct {
	RequestID   int64
	RequestedBy int64
	RequestedAt time.Time
	StoragePath string
	ContentHash string
	Title       string
	RetainUntil *time.Time
}

func (s *Store) initTrashSchema(ctx context.Context) error {
	_, err := s.db.Exec(ctx, `
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
		ALTER TABLE documents ADD COLUMN IF NOT EXISTS deleted_by BIGINT;

		CREATE TABLE IF NOT EXISTS document_delete_requests (
			id             BIGSERIAL PRIMARY KEY,
			document_id    BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
			tenant_id      BIGINT NOT NULL,
			requested_by   BIGINT NOT NULL,
			requested_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
			confirmed_by   BIGINT,
			confirmed_at   TIMESTAMPTZ,
			status         TEXT NOT NULL DEFAULT 'pending'
				CHECK (status IN ('pending','confirmed','executed','cancelled','blocked_retention')),
			UNIQUE(document_id, status)
		);
		CREATE INDEX IF NOT EXISTS idx_ddr_tenant_status ON document_delete_requests(tenant_id, status);
	`)
	if err != nil {
		return fmt.Errorf("storage: create trash schema: %w", err)
	}
	return nil
}

// SoftDeleteDocument moves a document into the trash (sets deleted_at/deleted_by).
// The WORM file is left untouched. Returns ErrAlreadyInTrash if the document is
// already trashed, ErrDocumentNotInTrash if it does not exist for the tenant.
func (s *Store) SoftDeleteDocument(ctx context.Context, id, tenantID, userID int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE documents SET deleted_at = now(), deleted_by = $3, updated_at = now()
		WHERE id = $1 AND tenant_id = $2 AND deleted_at IS NULL
	`, id, tenantID, userID)
	if err != nil {
		return fmt.Errorf("storage: soft delete document: %w", err)
	}
	if tag.RowsAffected() == 0 {
		// Distinguish "does not exist" from "already trashed" for a clearer error.
		var deleted bool
		qerr := s.db.QueryRow(ctx,
			`SELECT deleted_at IS NOT NULL FROM documents WHERE id = $1 AND tenant_id = $2`,
			id, tenantID).Scan(&deleted)
		if qerr != nil {
			return ErrDocumentNotInTrash
		}
		if deleted {
			return ErrAlreadyInTrash
		}
		return ErrDocumentNotInTrash
	}
	// Trashed documents must not surface in search. The WORM file and DB row
	// stay put (restore re-indexes); only the index entry is dropped.
	// Best-effort.
	s.DeleteFromIndex(ctx, id, tenantID)
	return nil
}

// ListTrash returns all soft-deleted documents for a tenant, newest deletion
// first.
func (s *Store) ListTrash(ctx context.Context, tenantID int64) ([]TrashDocument, 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_at, updated_at, deleted_at, deleted_by
		FROM documents WHERE tenant_id = $1 AND deleted_at IS NOT NULL ORDER BY deleted_at DESC
	`, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list trash: %w", err)
	}
	defer rows.Close()

	out := make([]TrashDocument, 0)
	for rows.Next() {
		var d TrashDocument
		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.CreatedAt, &d.UpdatedAt, &d.DeletedAt, &d.DeletedBy); err != nil {
			return nil, fmt.Errorf("storage: scan trash document: %w", err)
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

// RestoreDocument brings a document back out of the trash: clears deleted_at/
// deleted_by and cancels any pending delete request. Returns
// ErrDocumentNotInTrash if the document is not currently trashed.
func (s *Store) RestoreDocument(ctx context.Context, id, tenantID, userID int64) error {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return fmt.Errorf("storage: restore begin: %w", err)
	}
	defer tx.Rollback(ctx)

	tag, err := tx.Exec(ctx, `
		UPDATE documents SET deleted_at = NULL, deleted_by = NULL, updated_at = now()
		WHERE id = $1 AND tenant_id = $2 AND deleted_at IS NOT NULL
	`, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: restore document: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrDocumentNotInTrash
	}

	// Cancel any pending request so a later trash cycle can start clean.
	if _, err := tx.Exec(ctx, `
		UPDATE document_delete_requests SET status = 'cancelled', confirmed_by = $3, confirmed_at = now()
		WHERE document_id = $1 AND tenant_id = $2 AND status = 'pending'
	`, id, tenantID, userID); err != nil {
		return fmt.Errorf("storage: cancel pending requests on restore: %w", err)
	}

	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("storage: restore commit: %w", err)
	}
	// Back out of the trash -> re-index so it is findable again. Best-effort.
	s.SyncIndex(ctx, id)
	return nil
}

// CreateDeleteRequest records a final-deletion request (User A). It re-checks
// retention: if retain_until still blocks deletion, a row with
// status='blocked_retention' is written (for Nachvollziehbarkeit of the attempt)
// and ErrRetentionActive is returned. Otherwise a 'pending' request is created.
// The document must currently be in the trash.
func (s *Store) CreateDeleteRequest(ctx context.Context, docID, tenantID, userID int64) (*DeleteRequest, error) {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return nil, fmt.Errorf("storage: delete request begin: %w", err)
	}
	defer tx.Rollback(ctx)

	var retainUntil *time.Time
	err = tx.QueryRow(ctx, `
		SELECT retain_until FROM documents
		WHERE id = $1 AND tenant_id = $2 AND deleted_at IS NOT NULL
		FOR UPDATE
	`, docID, tenantID).Scan(&retainUntil)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrDocumentNotInTrash
	}
	if err != nil {
		return nil, fmt.Errorf("storage: load document for delete request: %w", err)
	}

	if retentionActive(retainUntil) {
		req, berr := upsertBlockedRequest(ctx, tx, docID, tenantID, userID)
		if berr != nil {
			return nil, berr
		}
		if cerr := tx.Commit(ctx); cerr != nil {
			return nil, fmt.Errorf("storage: delete request commit (blocked): %w", cerr)
		}
		return req, ErrRetentionActive
	}

	var req DeleteRequest
	err = tx.QueryRow(ctx, `
		INSERT INTO document_delete_requests (document_id, tenant_id, requested_by, status)
		VALUES ($1, $2, $3, 'pending')
		ON CONFLICT (document_id, status) DO NOTHING
		RETURNING id, document_id, tenant_id, requested_by, requested_at, confirmed_by, confirmed_at, status
	`, docID, tenantID, userID).Scan(&req.ID, &req.DocumentID, &req.TenantID, &req.RequestedBy, &req.RequestedAt, &req.ConfirmedBy, &req.ConfirmedAt, &req.Status)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrDeleteRequestExists
	}
	if err != nil {
		return nil, fmt.Errorf("storage: insert delete request: %w", err)
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, fmt.Errorf("storage: delete request commit: %w", err)
	}
	return &req, nil
}

// upsertBlockedRequest records/refreshes a blocked_retention request row.
func upsertBlockedRequest(ctx context.Context, tx pgx.Tx, docID, tenantID, userID int64) (*DeleteRequest, error) {
	var req DeleteRequest
	err := tx.QueryRow(ctx, `
		INSERT INTO document_delete_requests (document_id, tenant_id, requested_by, status)
		VALUES ($1, $2, $3, 'blocked_retention')
		ON CONFLICT (document_id, status)
		DO UPDATE SET requested_by = EXCLUDED.requested_by, requested_at = now()
		RETURNING id, document_id, tenant_id, requested_by, requested_at, confirmed_by, confirmed_at, status
	`, docID, tenantID, userID).Scan(&req.ID, &req.DocumentID, &req.TenantID, &req.RequestedBy, &req.RequestedAt, &req.ConfirmedBy, &req.ConfirmedAt, &req.Status)
	if err != nil {
		return nil, fmt.Errorf("storage: record blocked_retention request: %w", err)
	}
	return &req, nil
}

// ListDeleteRequests returns all delete requests for a document (status/history),
// scoped to tenant, newest first.
func (s *Store) ListDeleteRequests(ctx context.Context, docID, tenantID int64) ([]DeleteRequest, error) {
	rows, err := s.db.Query(ctx, `
		SELECT id, document_id, tenant_id, requested_by, requested_at, confirmed_by, confirmed_at, status
		FROM document_delete_requests
		WHERE document_id = $1 AND tenant_id = $2
		ORDER BY requested_at DESC, id DESC
	`, docID, tenantID)
	if err != nil {
		return nil, fmt.Errorf("storage: list delete requests: %w", err)
	}
	defer rows.Close()

	out := make([]DeleteRequest, 0)
	for rows.Next() {
		var req DeleteRequest
		if err := rows.Scan(&req.ID, &req.DocumentID, &req.TenantID, &req.RequestedBy, &req.RequestedAt, &req.ConfirmedBy, &req.ConfirmedAt, &req.Status); err != nil {
			return nil, fmt.Errorf("storage: scan delete request: %w", err)
		}
		out = append(out, req)
	}
	return out, rows.Err()
}

// CancelDeleteRequest withdraws a pending delete request (status='cancelled').
func (s *Store) CancelDeleteRequest(ctx context.Context, docID, reqID, tenantID, userID int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE document_delete_requests
		SET status = 'cancelled', confirmed_by = $4, confirmed_at = now()
		WHERE id = $1 AND document_id = $2 AND tenant_id = $3 AND status = 'pending'
	`, reqID, docID, tenantID, userID)
	if err != nil {
		return fmt.Errorf("storage: cancel delete request: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrDeleteRequestNotFound
	}
	return nil
}

// ConfirmDeleteRequest confirms a pending request (User B) and, if retention
// still allows it, executes the final deletion: it removes the physical WORM
// file and turns the DB row into a tombstone (storage_path/ocr_text cleared,
// content_hash kept). The confirmer must differ from the requester
// (ErrSelfConfirm) and must be enforced to hold domain_admin by the caller.
//
// If retain_until now blocks deletion, the request is moved to
// status='blocked_retention' (recording the attempt) and ErrRetentionActive is
// returned. On any other outcome the returned *ExecutedDelete is nil.
func (s *Store) ConfirmDeleteRequest(ctx context.Context, docID, reqID, tenantID, confirmerID int64) (*ExecutedDelete, error) {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return nil, fmt.Errorf("storage: confirm begin: %w", err)
	}
	defer tx.Rollback(ctx)

	var (
		requestedBy int64
		requestedAt time.Time
	)
	err = tx.QueryRow(ctx, `
		SELECT requested_by, requested_at FROM document_delete_requests
		WHERE id = $1 AND document_id = $2 AND tenant_id = $3 AND status = 'pending'
		FOR UPDATE
	`, reqID, docID, tenantID).Scan(&requestedBy, &requestedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrDeleteRequestNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("storage: load delete request: %w", err)
	}

	if confirmerID == requestedBy {
		// Vier-Augen-Prinzip: refuse and leave the request pending.
		return nil, ErrSelfConfirm
	}

	var (
		retainUntil *time.Time
		storagePath string
		contentHash string
		title       string
	)
	err = tx.QueryRow(ctx, `
		SELECT retain_until, storage_path, COALESCE(content_hash, ''), COALESCE(title, '') FROM documents
		WHERE id = $1 AND tenant_id = $2 AND deleted_at IS NOT NULL
		FOR UPDATE
	`, docID, tenantID).Scan(&retainUntil, &storagePath, &contentHash, &title)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrDocumentNotInTrash
	}
	if err != nil {
		return nil, fmt.Errorf("storage: load document for confirm: %w", err)
	}

	if retentionActive(retainUntil) {
		// Record the blocked attempt on the (previously pending) request.
		if _, uerr := tx.Exec(ctx, `
			UPDATE document_delete_requests
			SET status = 'blocked_retention', confirmed_by = $2, confirmed_at = now()
			WHERE id = $1
		`, reqID, confirmerID); uerr != nil {
			return nil, fmt.Errorf("storage: record blocked confirm: %w", uerr)
		}
		if cerr := tx.Commit(ctx); cerr != nil {
			return nil, fmt.Errorf("storage: confirm commit (blocked): %w", cerr)
		}
		return nil, ErrRetentionActive
	}

	// Mark executed and tombstone the document (keep content_hash).
	if _, uerr := tx.Exec(ctx, `
		UPDATE document_delete_requests
		SET status = 'executed', confirmed_by = $2, confirmed_at = now()
		WHERE id = $1
	`, reqID, confirmerID); uerr != nil {
		return nil, fmt.Errorf("storage: mark request executed: %w", uerr)
	}
	if _, uerr := tx.Exec(ctx, `
		UPDATE documents
		SET storage_path = '', ocr_text = NULL, updated_at = now()
		WHERE id = $1 AND tenant_id = $2
	`, docID, tenantID); uerr != nil {
		return nil, fmt.Errorf("storage: tombstone document: %w", uerr)
	}

	// Remove the physical WORM file. The store directory is writable by the
	// process even though the file itself is 0440, so os.Remove succeeds. A
	// missing file is tolerated (already gone) — anything else aborts the tx so
	// the DB stays consistent with the filesystem.
	if storagePath != "" {
		if rerr := os.Remove(storagePath); rerr != nil && !os.IsNotExist(rerr) {
			return nil, fmt.Errorf("storage: remove WORM file: %w", rerr)
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return nil, fmt.Errorf("storage: confirm commit: %w", err)
	}
	// GoBD-critical: a finally-deleted document must not remain findable in the
	// search index, even though the search endpoint only lands in a later
	// phase. Best-effort, but consistency is enforced from day one.
	s.DeleteFromIndex(ctx, docID, tenantID)
	return &ExecutedDelete{
		RequestID:   reqID,
		RequestedBy: requestedBy,
		RequestedAt: requestedAt,
		StoragePath: storagePath,
		ContentHash: contentHash,
		Title:       title,
		RetainUntil: retainUntil,
	}, nil
}

// retentionActive reports whether retain_until still blocks final deletion:
// a NULL retain_until never blocks; a date in the future (>= today) blocks.
func retentionActive(retainUntil *time.Time) bool {
	if retainUntil == nil {
		return false
	}
	// retain_until is a DATE; deletion is allowed once it is strictly in the
	// past (retain_until < now()). Equal-to-today still blocks.
	return !retainUntil.Before(time.Now())
}