package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"github.com/google/uuid"
)

func (s *PostgresStore) ListAllMailboxes() []Mailbox {
	rows, err := s.db.QueryContext(context.Background(), `
		SELECT id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		       smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error
		FROM mailboxes
		ORDER BY updated_at DESC
	`)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	return scanMailboxes(rows)
}

func (s *PostgresStore) ListMailboxes(workspaceSlug string) []Mailbox {
	rows, err := s.db.QueryContext(context.Background(), `
		SELECT id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		       smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error
		FROM mailboxes
		WHERE workspace_slug = $1
		ORDER BY updated_at DESC
	`, workspaceSlug)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	return scanMailboxes(rows)
}

func (s *PostgresStore) GetMailboxByID(mailboxID string) (Mailbox, error) {
	row := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		       smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error
		FROM mailboxes
		WHERE id = $1
	`, mailboxID)
	return scanMailbox(row)
}

func (s *PostgresStore) GetMailboxConnection(mailboxID string) (MailboxConnection, error) {
	var (
		connection MailboxConnection
		lastSynced sql.NullTime
		syncError  sql.NullString
	)

	err := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		       smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error,
		       imap_password_ciphertext, smtp_password_ciphertext
		FROM mailboxes
		WHERE id = $1
	`, mailboxID).Scan(
		&connection.ID,
		&connection.WorkspaceSlug,
		&connection.Label,
		&connection.Email,
		&connection.DisplayName,
		&connection.IMAPHost,
		&connection.IMAPPort,
		&connection.IMAPUsername,
		&connection.IMAPUseTLS,
		&connection.SMTPHost,
		&connection.SMTPPort,
		&connection.SMTPUsername,
		&connection.SMTPUseTLS,
		&connection.CreatedAt,
		&connection.UpdatedAt,
		&lastSynced,
		&connection.SyncStatus,
		&syncError,
		&connection.IMAPPasswordCiphertext,
		&connection.SMTPPasswordCiphertext,
	)
	if err != nil {
		return MailboxConnection{}, err
	}

	connection.LastSyncedAt = timePtr(lastSynced)
	connection.SyncError = syncError.String
	return connection, nil
}

func (s *PostgresStore) CreateMailbox(input CreateMailboxRecordInput) (Mailbox, error) {
	now := time.Now().UTC()
	row := s.db.QueryRowContext(context.Background(), `
		INSERT INTO mailboxes (
			id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_password_ciphertext, imap_use_tls,
			smtp_host, smtp_port, smtp_username, smtp_password_ciphertext, smtp_use_tls, sync_status, sync_error, created_at, updated_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, 'idle', '', $16, $16)
		RETURNING id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		          smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error
	`,
		uuid.NewString(),
		input.WorkspaceSlug,
		defaultString(input.Label, input.Email),
		input.Email,
		input.DisplayName,
		input.IMAPHost,
		input.IMAPPort,
		input.IMAPUsername,
		input.IMAPPasswordCiphertext,
		input.IMAPUseTLS,
		input.SMTPHost,
		input.SMTPPort,
		input.SMTPUsername,
		input.SMTPPasswordCiphertext,
		input.SMTPUseTLS,
		now,
	)

	mailbox, err := scanMailbox(row)
	if err != nil {
		return Mailbox{}, err
	}

	if err := appendActivity(context.Background(), s.queries, mailbox.WorkspaceSlug, "Mailbox connected", fmt.Sprintf("%s is ready for sync.", mailbox.Email)); err != nil {
		return Mailbox{}, err
	}

	return mailbox, nil
}

func (s *PostgresStore) UpdateMailboxSyncStatus(mailboxID string, input UpdateMailboxSyncStatusInput) (Mailbox, error) {
	row := s.db.QueryRowContext(context.Background(), `
		UPDATE mailboxes
		SET sync_status = COALESCE(NULLIF($2, ''), sync_status),
		    sync_error = COALESCE($3, sync_error),
		    last_synced_at = COALESCE($4, last_synced_at),
		    updated_at = $5
		WHERE id = $1
		RETURNING id, workspace_slug, label, email, display_name, imap_host, imap_port, imap_username, imap_use_tls,
		          smtp_host, smtp_port, smtp_username, smtp_use_tls, created_at, updated_at, last_synced_at, sync_status, sync_error
	`, mailboxID, input.SyncStatus, nullableString(input.SyncError), nullableTime(input.LastSyncedAt), time.Now().UTC())

	return scanMailbox(row)
}

func (s *PostgresStore) ListMailMessages(workspaceSlug string, mailboxID string) []MailMessage {
	var (
		rows *sql.Rows
		err  error
	)
	if mailboxID == "" {
		rows, err = s.db.QueryContext(context.Background(), `
			SELECT id, workspace_slug, mailbox_id, remote_uid, message_id, folder, from_address, to_recipients, cc_recipients,
			       subject, snippet, text_body, html_body, received_at, is_read, linked_task_id, created_at, updated_at
			FROM mail_messages
			WHERE workspace_slug = $1
			ORDER BY received_at DESC
			LIMIT 120
		`, workspaceSlug)
	} else {
		rows, err = s.db.QueryContext(context.Background(), `
			SELECT id, workspace_slug, mailbox_id, remote_uid, message_id, folder, from_address, to_recipients, cc_recipients,
			       subject, snippet, text_body, html_body, received_at, is_read, linked_task_id, created_at, updated_at
			FROM mail_messages
			WHERE workspace_slug = $1 AND mailbox_id = $2
			ORDER BY received_at DESC
			LIMIT 120
		`, workspaceSlug, mailboxID)
	}
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	return scanMailMessages(rows)
}

func (s *PostgresStore) GetMailMessageByID(messageID string) (MailMessage, error) {
	row := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, mailbox_id, remote_uid, message_id, folder, from_address, to_recipients, cc_recipients,
		       subject, snippet, text_body, html_body, received_at, is_read, linked_task_id, created_at, updated_at
		FROM mail_messages
		WHERE id = $1
	`, messageID)
	return scanMailMessage(row)
}

func (s *PostgresStore) UpsertMailMessages(mailboxID string, messages []InboundMailMessage) error {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	now := time.Now().UTC()
	for _, message := range messages {
		_, err := tx.ExecContext(ctx, `
			INSERT INTO mail_messages (
				id, workspace_slug, mailbox_id, remote_uid, message_id, folder, from_address, to_recipients, cc_recipients,
				subject, snippet, text_body, html_body, received_at, is_read, linked_task_id, created_at, updated_at
			)
			VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NULL, $16, $16)
			ON CONFLICT (mailbox_id, folder, remote_uid)
			DO UPDATE SET
				message_id = EXCLUDED.message_id,
				from_address = EXCLUDED.from_address,
				to_recipients = EXCLUDED.to_recipients,
				cc_recipients = EXCLUDED.cc_recipients,
				subject = EXCLUDED.subject,
				snippet = EXCLUDED.snippet,
				text_body = EXCLUDED.text_body,
				html_body = EXCLUDED.html_body,
				received_at = EXCLUDED.received_at,
				is_read = EXCLUDED.is_read,
				updated_at = EXCLUDED.updated_at
		`,
			uuid.NewString(),
			message.WorkspaceSlug,
			mailboxID,
			message.RemoteUID,
			message.MessageID,
			defaultString(message.Folder, "INBOX"),
			mustJSON(message.From),
			mustJSON(message.To),
			mustJSON(message.Cc),
			message.Subject,
			message.Snippet,
			message.TextBody,
			message.HTMLBody,
			message.ReceivedAt,
			message.IsRead,
			now,
		)
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}

func (s *PostgresStore) LinkMailMessageTask(messageID string, taskID string) (MailMessage, error) {
	row := s.db.QueryRowContext(context.Background(), `
		UPDATE mail_messages
		SET linked_task_id = $2, updated_at = $3
		WHERE id = $1
		RETURNING id, workspace_slug, mailbox_id, remote_uid, message_id, folder, from_address, to_recipients, cc_recipients,
		          subject, snippet, text_body, html_body, received_at, is_read, linked_task_id, created_at, updated_at
	`, messageID, taskID, time.Now().UTC())
	return scanMailMessage(row)
}

func (s *PostgresStore) ListOutgoingMails(workspaceSlug string, mailboxID string) []OutgoingMail {
	var (
		rows *sql.Rows
		err  error
	)
	if mailboxID == "" {
		rows, err = s.db.QueryContext(context.Background(), `
			SELECT id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
			       status, scheduled_for, sent_at, error_message, created_at, updated_at
			FROM outgoing_mails
			WHERE workspace_slug = $1
			ORDER BY created_at DESC
			LIMIT 120
		`, workspaceSlug)
	} else {
		rows, err = s.db.QueryContext(context.Background(), `
			SELECT id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
			       status, scheduled_for, sent_at, error_message, created_at, updated_at
			FROM outgoing_mails
			WHERE workspace_slug = $1 AND mailbox_id = $2
			ORDER BY created_at DESC
			LIMIT 120
		`, workspaceSlug, mailboxID)
	}
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	return scanOutgoingMails(rows)
}

func (s *PostgresStore) ListDueOutgoingMails(now time.Time, limit int) []OutgoingMail {
	rows, err := s.db.QueryContext(context.Background(), `
		SELECT id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
		       status, scheduled_for, sent_at, error_message, created_at, updated_at
		FROM outgoing_mails
		WHERE status IN ('queued', 'scheduled')
		  AND (scheduled_for IS NULL OR scheduled_for <= $1)
		ORDER BY created_at ASC
		LIMIT $2
	`, now, limit)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	return scanOutgoingMails(rows)
}

func (s *PostgresStore) GetOutgoingMailByID(outgoingMailID string) (OutgoingMail, error) {
	row := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
		       status, scheduled_for, sent_at, error_message, created_at, updated_at
		FROM outgoing_mails
		WHERE id = $1
	`, outgoingMailID)
	return scanOutgoingMail(row)
}

func (s *PostgresStore) CreateOutgoingMail(input CreateOutgoingMailInput) (OutgoingMail, error) {
	now := time.Now().UTC()
	row := s.db.QueryRowContext(context.Background(), `
		INSERT INTO outgoing_mails (
			id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
			status, scheduled_for, sent_at, error_message, created_at, updated_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NULL, '', $12, $12)
		RETURNING id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
		          status, scheduled_for, sent_at, error_message, created_at, updated_at
	`,
		uuid.NewString(),
		input.WorkspaceSlug,
		input.MailboxID,
		mustJSON(input.To),
		mustJSON(input.Cc),
		mustJSON(input.Bcc),
		input.Subject,
		input.TextBody,
		input.HTMLBody,
		input.Status,
		nullableTime(input.ScheduledFor),
		now,
	)
	return scanOutgoingMail(row)
}

func (s *PostgresStore) UpdateOutgoingMailStatus(outgoingMailID string, input UpdateOutgoingMailStatusInput) (OutgoingMail, error) {
	row := s.db.QueryRowContext(context.Background(), `
		UPDATE outgoing_mails
		SET status = COALESCE(NULLIF($2, ''), status),
		    sent_at = COALESCE($3, sent_at),
		    error_message = COALESCE($4, error_message),
		    updated_at = $5
		WHERE id = $1
		RETURNING id, workspace_slug, mailbox_id, to_recipients, cc_recipients, bcc_recipients, subject, text_body, html_body,
		          status, scheduled_for, sent_at, error_message, created_at, updated_at
	`, outgoingMailID, input.Status, nullableTime(input.SentAt), nullableString(input.Error), time.Now().UTC())
	return scanOutgoingMail(row)
}

type rowScanner interface {
	Scan(dest ...any) error
}

func scanMailboxes(rows *sql.Rows) []Mailbox {
	items := make([]Mailbox, 0)
	for rows.Next() {
		item, err := scanMailbox(rows)
		if err != nil {
			panic(err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		panic(err)
	}
	return items
}

func scanMailbox(row rowScanner) (Mailbox, error) {
	var (
		item       Mailbox
		lastSynced sql.NullTime
		syncError  sql.NullString
	)
	err := row.Scan(
		&item.ID,
		&item.WorkspaceSlug,
		&item.Label,
		&item.Email,
		&item.DisplayName,
		&item.IMAPHost,
		&item.IMAPPort,
		&item.IMAPUsername,
		&item.IMAPUseTLS,
		&item.SMTPHost,
		&item.SMTPPort,
		&item.SMTPUsername,
		&item.SMTPUseTLS,
		&item.CreatedAt,
		&item.UpdatedAt,
		&lastSynced,
		&item.SyncStatus,
		&syncError,
	)
	if err != nil {
		return Mailbox{}, err
	}
	item.LastSyncedAt = timePtr(lastSynced)
	item.SyncError = syncError.String
	return item, nil
}

func scanMailMessages(rows *sql.Rows) []MailMessage {
	items := make([]MailMessage, 0)
	for rows.Next() {
		item, err := scanMailMessage(rows)
		if err != nil {
			panic(err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		panic(err)
	}
	return items
}

func scanMailMessage(row rowScanner) (MailMessage, error) {
	var (
		item         MailMessage
		fromJSON     []byte
		toJSON       []byte
		ccJSON       []byte
		linkedTaskID sql.NullString
	)
	err := row.Scan(
		&item.ID,
		&item.WorkspaceSlug,
		&item.MailboxID,
		&item.RemoteUID,
		&item.MessageID,
		&item.Folder,
		&fromJSON,
		&toJSON,
		&ccJSON,
		&item.Subject,
		&item.Snippet,
		&item.TextBody,
		&item.HTMLBody,
		&item.ReceivedAt,
		&item.IsRead,
		&linkedTaskID,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if err != nil {
		return MailMessage{}, err
	}
	if err := decodeJSONValue(fromJSON, &item.From); err != nil {
		return MailMessage{}, err
	}
	to, err := decodeJSONSlice[MailAddress](toJSON)
	if err != nil {
		return MailMessage{}, err
	}
	cc, err := decodeJSONSlice[MailAddress](ccJSON)
	if err != nil {
		return MailMessage{}, err
	}
	item.To = to
	item.Cc = cc
	item.LinkedTaskID = stringPtr(linkedTaskID)
	return item, nil
}

func scanOutgoingMails(rows *sql.Rows) []OutgoingMail {
	items := make([]OutgoingMail, 0)
	for rows.Next() {
		item, err := scanOutgoingMail(rows)
		if err != nil {
			panic(err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		panic(err)
	}
	return items
}

func scanOutgoingMail(row rowScanner) (OutgoingMail, error) {
	var (
		item         OutgoingMail
		toJSON       []byte
		ccJSON       []byte
		bccJSON      []byte
		scheduledFor sql.NullTime
		sentAt       sql.NullTime
		errorMessage sql.NullString
	)
	err := row.Scan(
		&item.ID,
		&item.WorkspaceSlug,
		&item.MailboxID,
		&toJSON,
		&ccJSON,
		&bccJSON,
		&item.Subject,
		&item.TextBody,
		&item.HTMLBody,
		&item.Status,
		&scheduledFor,
		&sentAt,
		&errorMessage,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if err != nil {
		return OutgoingMail{}, err
	}
	to, err := decodeJSONSlice[MailAddress](toJSON)
	if err != nil {
		return OutgoingMail{}, err
	}
	cc, err := decodeJSONSlice[MailAddress](ccJSON)
	if err != nil {
		return OutgoingMail{}, err
	}
	bcc, err := decodeJSONSlice[MailAddress](bccJSON)
	if err != nil {
		return OutgoingMail{}, err
	}
	item.To = to
	item.Cc = cc
	item.Bcc = bcc
	item.ScheduledFor = timePtr(scheduledFor)
	item.SentAt = timePtr(sentAt)
	item.Error = errorMessage.String
	return item, nil
}

func decodeJSONValue(raw []byte, target any) error {
	if len(raw) == 0 {
		return nil
	}
	return json.Unmarshal(raw, target)
}
