package storage

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

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

// MarkedForDeletionInfo reports the deletion-mark state of one mail.
type MarkedForDeletionInfo struct {
	Marked bool
	By     string
	At     *time.Time
}

// SetMarkedForDeletion sets or clears the marked_for_deletion flag for a
// single mail (PROJ-56c). This is a deliberate, per-mail user action — the
// cron-driven purge only ever deletes mails that are BOTH past retain_until
// AND marked here; an expired retention date alone is never sufficient.
func (s *Store) SetMarkedForDeletion(ctx context.Context, id string, marked bool, username string) error {
	if s.db == nil {
		return nil
	}
	if id == "" {
		return errors.New("storage: SetMarkedForDeletion: empty id")
	}
	if marked {
		_, err := s.db.Exec(ctx,
			`UPDATE emails SET marked_for_deletion = TRUE, marked_for_deletion_by = $1, marked_for_deletion_at = NOW() WHERE id = $2`,
			username, id)
		if err != nil {
			return fmt.Errorf("storage: set marked for deletion: %w", err)
		}
		return nil
	}
	_, err := s.db.Exec(ctx,
		`UPDATE emails SET marked_for_deletion = FALSE, marked_for_deletion_by = NULL, marked_for_deletion_at = NULL WHERE id = $1`,
		id)
	if err != nil {
		return fmt.Errorf("storage: clear marked for deletion: %w", err)
	}
	return nil
}

// GetMarkedForDeletion returns the current deletion-mark state for one mail.
func (s *Store) GetMarkedForDeletion(ctx context.Context, id string) (MarkedForDeletionInfo, error) {
	if s.db == nil {
		return MarkedForDeletionInfo{}, nil
	}
	var info MarkedForDeletionInfo
	var by *string
	row := s.db.QueryRow(ctx,
		`SELECT COALESCE(marked_for_deletion, FALSE), marked_for_deletion_by, marked_for_deletion_at
		   FROM emails WHERE id = $1`, id)
	if err := row.Scan(&info.Marked, &by, &info.At); err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return MarkedForDeletionInfo{}, nil
		}
		return MarkedForDeletionInfo{}, fmt.Errorf("storage: get marked for deletion: %w", err)
	}
	if by != nil {
		info.By = *by
	}
	return info, nil
}

// ExpiredMailMeta is metadata-only info about a mail past retain_until,
// for the admin "mark for deletion" review list. Deliberately omits body
// content — domain_admin/superadmin may manage retention without having
// mail-content read access (SEC-29 separation of duties).
type ExpiredMailMeta struct {
	ID          string
	From        string
	Subject     string
	ReceivedAt  time.Time
	RetainUntil time.Time
	Marked      bool
	MarkedBy    string
}

// ListExpiredMails returns metadata for all mails whose retain_until has
// passed, regardless of marked_for_deletion — this is what the admin UI
// shows so a human can review and mark individual mails (PROJ-56c).
// If tenantID is non-nil, results are restricted to that tenant.
func (s *Store) ListExpiredMails(ctx context.Context, tenantID *int64) ([]ExpiredMailMeta, error) {
	if s.db == nil {
		return nil, nil
	}
	query := `SELECT id, COALESCE(mail_from, ''), COALESCE(subject, ''), received_at,
	                  retain_until, COALESCE(marked_for_deletion, FALSE), COALESCE(marked_for_deletion_by, '')
	             FROM emails
	            WHERE retain_until IS NOT NULL AND retain_until < NOW()`
	args := []interface{}{}
	if tenantID != nil {
		args = append(args, *tenantID)
		query += fmt.Sprintf(" AND tenant_id = $%d", len(args))
	}
	query += " ORDER BY retain_until ASC LIMIT 500"

	rows, err := s.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("storage: list expired mails: %w", err)
	}
	defer rows.Close()

	var out []ExpiredMailMeta
	for rows.Next() {
		var m ExpiredMailMeta
		if err := rows.Scan(&m.ID, &m.From, &m.Subject, &m.ReceivedAt, &m.RetainUntil, &m.Marked, &m.MarkedBy); err == nil {
			out = append(out, m)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("storage: list expired mails rows: %w", err)
	}
	return out, nil
}

// ListExpiredMarkedMailIDs returns IDs of mails that are BOTH past
// retain_until AND explicitly marked_for_deletion=TRUE (PROJ-56c). This is
// the query the cron-driven purge uses — unlike ListExpiredMailIDs (used by
// the manual admin "Jetzt löschen" button), it never deletes anything based
// on the date alone.
func (s *Store) ListExpiredMarkedMailIDs(ctx context.Context) ([]string, error) {
	if s.db == nil {
		return nil, nil
	}
	rows, err := s.db.Query(ctx,
		`SELECT id FROM emails WHERE retain_until IS NOT NULL AND retain_until < NOW() AND marked_for_deletion = TRUE`)
	if err != nil {
		return nil, fmt.Errorf("storage: list expired marked query: %w", err)
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			ids = append(ids, id)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("storage: list expired marked rows: %w", err)
	}
	return ids, nil
}
