package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log/slog"

	"postern/internal/model"
)

func (db *DB) GetUserMailbox(ctx context.Context, userID int, mailboxName string) (model.Mailbox, error) {
	mailboxes, err := db.GetUserMailboxes(ctx, userID)
	if err != nil {
		return model.Mailbox{}, err
	}

	for _, mb := range mailboxes {
		if mb.Name == mailboxName {
			return mb, nil
		}
	}
	return model.Mailbox{}, ErrMailboxNotFound
}

// GetUserMailboxes returns all existing mailboxes with their calculated IMAP status values.
func (db *DB) GetUserMailboxes(ctx context.Context, userID int) ([]model.Mailbox, error) {
	// This query safely aggregates counts using subqueries, preventing
	// Cartesian products if a message has multiple flags.
	query := `
		SELECT
		    mb.id,
			mb.name,
			mb.special_use,
			mb.uidnext,
			mb.uidvalidity,
			mb.highest_modseq,
			(s.id IS NOT NULL) AS is_subscribed,
			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL) AS num_messages,
			(SELECT COALESCE(SUM(m.size), 0) FROM mailbox_messages mm JOIN messages m ON mm.message_id = m.id WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL) AS size,
			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL AND NOT EXISTS (SELECT 1 FROM message_flags mf WHERE mf.mailbox_id = mm.mailbox_id AND mf.uid = mm.uid AND mf.flag = '\Seen')) AS num_unseen,
			(SELECT COUNT(*) FROM mailbox_messages mm WHERE mm.mailbox_id = mb.id AND mm.expunged_modseq IS NULL AND EXISTS (SELECT 1 FROM message_flags mf WHERE mf.mailbox_id = mm.mailbox_id AND mf.uid = mm.uid AND mf.flag = '\Deleted')) AS num_deleted
		FROM mailboxes mb
		LEFT JOIN subscriptions s ON mb.id = s.mailbox_id
		WHERE mb.user_id = ?
	`

	rows, err := db.read.QueryContext(ctx, query, userID)
	if err != nil {
		return nil, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	var mailboxes []model.Mailbox
	for rows.Next() {
		var mb model.Mailbox
		var specialUse sql.NullString

		err := rows.Scan(
			&mb.ID,
			&mb.Name,
			&specialUse,
			&mb.UIDNext,
			&mb.UIDValidity,
			&mb.HighestModSeq,
			&mb.IsSubscribed,
			&mb.NumMessages,
			&mb.Size,
			&mb.NumUnseen,
			&mb.NumDeleted,
		)
		if err != nil {
			return nil, err
		}

		if specialUse.Valid {
			mb.SpecialUse = new(specialUse.String)
		}
		mailboxes = append(mailboxes, mb)
	}

	return mailboxes, rows.Err()
}

func (db *DB) CreateMailbox(ctx context.Context, userID int, mailbox string) error {
	tx, err := db.write.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("beginning transaction: %w", err)
	}
	defer txRollback(tx)

	nextUID, err := nextUIDValidity(tx)
	if err != nil {
		return err
	}

	_, err = tx.ExecContext(ctx, `
            INSERT INTO mailboxes
                (user_id, name, uidvalidity, uidnext, highest_modseq)
            VALUES
                (?, ?, ?, 1, 0)`,
		userID, mailbox, nextUID,
	)
	if err != nil {
		return fmt.Errorf("creating mailbox %q: %w", mailbox, err)
	}

	return tx.Commit()
}

func (db *DB) DeleteMailbox(ctx context.Context, userID int, mailbox string) error {
	_, err := db.write.ExecContext(ctx, `DELETE FROM mailboxes WHERE user_id = ? AND name = ?`, userID, mailbox)
	if err != nil {
		return fmt.Errorf("deleting mailbox %q: %w", mailbox, err)
	}

	return nil
}

func (db *DB) RenameMailbox(ctx context.Context, userID int, oldName, newName string) error {
	tx, err := db.write.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("beginning transaction: %w", err)
	}
	defer txRollback(tx)

	_, err = tx.ExecContext(ctx, `
    UPDATE mailboxes 
    SET name = ? || SUBSTR(name, LENGTH(?)+1)
    WHERE user_id = ? 
    AND (name = ? OR name LIKE ? ESCAPE '\')
`, newName, oldName, userID, oldName, escapeLike(oldName)+"/%")
	if err != nil {
		return fmt.Errorf("renaming mailbox %q: %v", oldName, err.Error())
	}

	return tx.Commit()
}

// GetMailboxID returns the mailbox ID und UID Validity for the given mailbox name, scoped to the user ID
func (db *DB) GetMailboxID(ctx context.Context, userID int, mailbox string) (int, uint32, error) {
	var id int
	var uidValidity uint32
	err := db.read.QueryRowContext(ctx, "SELECT id, uidvalidity FROM mailboxes WHERE name = ? AND user_id = ?", mailbox, userID).Scan(&id, &uidValidity)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, 0, ErrMailboxNotFound
	}
	return id, uidValidity, err
}

// CreateAndMoveAllMessagesFromINBOXToMailbox is special handling for RENAME of mailbox INBOX
func (db *DB) CreateAndMoveAllMessagesFromINBOXToMailbox(ctx context.Context, userID int, toMailbox string) error {
	tx, err := db.write.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func(tx *sql.Tx) {
		err := tx.Rollback()
		if err != nil {
			slog.Error("failed to rollback transaction", "error", err.Error())
		}
	}(tx)

	// Rename INBOX -> toMailbox
	_, err = tx.ExecContext(ctx, `UPDATE mailboxes SET name = ? WHERE user_id = ? AND name = 'INBOX'`, toMailbox, userID)
	if err != nil {
		return err
	}

	// Create new INBOX mailbox
	nextUID, err := nextUIDValidity(tx)
	if err != nil {
		return err
	}

	_, err = tx.ExecContext(ctx, `
            INSERT INTO mailboxes
                (user_id, name, uidvalidity, uidnext, highest_modseq)
            VALUES
                (?, 'INBOX', ?, 1, 0)`,
		userID, nextUID,
	)
	if err != nil {
		return err
	}

	return tx.Commit()
}

// ListSubscribed fulfills IMAP LIST (SUBSCRIBED).
// It returns ONLY mailboxes that are subscribed to. The boolean map value
// indicates if the mailbox still exists (true) or was deleted (false).
func (db *DB) ListSubscribed(ctx context.Context, userID int) (map[model.Mailbox]bool, error) {
	// INNER JOIN ensures we only get existing mailboxes the user is actually subscribed to.
	// The UNION ALL handles mailboxes that were deleted (mailbox_id IS NULL)
	// but still have a subscription record.
	query := `
		SELECT mb.name, mb.special_use, 1 AS mb_exists
		FROM subscriptions s
		INNER JOIN mailboxes mb ON s.mailbox_id = mb.id
		WHERE s.user_id = ?
		
		UNION ALL
		
		SELECT s.mailbox_name AS name, NULL AS special_use, 0 AS mb_exists
		FROM subscriptions s
		WHERE s.user_id = ? AND s.mailbox_id IS NULL
	`

	rows, err := db.read.QueryContext(ctx, query, userID, userID)
	if err != nil {
		return nil, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	out := make(map[model.Mailbox]bool)
	for rows.Next() {
		var mb model.Mailbox
		var exists bool
		var specialUse sql.NullString

		if err := rows.Scan(&mb.Name, &specialUse, &exists); err != nil {
			return nil, err
		}

		if specialUse.Valid {
			mb.SpecialUse = new(specialUse.String)
		}

		mb.IsSubscribed = true
		out[mb] = exists
	}

	return out, rows.Err()
}

func (db *DB) UIDToServerSeq(ctx context.Context, mailboxID int, uid uint32) (uint32, error) {
	var seq uint32
	err := db.read.QueryRowContext(ctx, `
        SELECT COUNT(*)
        FROM mailbox_messages
        WHERE mailbox_id = ? AND expunged_modseq IS NULL AND uid <= ?
    `, mailboxID, uid).Scan(&seq)
	return seq, err // 0 means "no live message at or below this uid"
}

func (db *DB) ServerSeqToUID(ctx context.Context, mailboxID int, seq uint32) (uint32, error) {
	var uid uint32
	err := db.read.QueryRowContext(ctx, `
        SELECT uid FROM mailbox_messages
        WHERE mailbox_id = ? AND expunged_modseq IS NULL
        ORDER BY uid LIMIT 1 OFFSET ?
    `, mailboxID, seq-1).Scan(&uid) // OFFSET is 0-based, seqnum 1-based
	return uid, err
}

// MailboxMessageCount returns the number of live (non-expunged) messages in the
// mailbox: the server-view EXISTS value, which is what RFC 9051 binds "*" to.
func (db *DB) MailboxMessageCount(ctx context.Context, mailboxID int) (uint32, error) {
	var count uint32
	err := db.read.QueryRowContext(ctx, `
        SELECT COUNT(*) FROM mailbox_messages
        WHERE mailbox_id = ? AND expunged_modseq IS NULL
    `, mailboxID).Scan(&count)
	return count, err
}