package db

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

	"github.com/emersion/go-imap/v2"
	_ "modernc.org/sqlite"
)

const pragmas = `
PRAGMA journal_mode  = WAL;
PRAGMA synchronous   = NORMAL;
PRAGMA foreign_keys  = ON;
PRAGMA busy_timeout  = 5000;
PRAGMA temp_store    = MEMORY;
`

const schema = `

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL UNIQUE,
    password_hash BLOB    NOT NULL,
    created_at    INTEGER NOT NULL
) STRICT;

CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);

CREATE TABLE IF NOT EXISTS addresses (
    id            INTEGER PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name          TEXT    NOT NULL UNIQUE,
    created_at    INTEGER NOT NULL
) STRICT;

CREATE INDEX IF NOT EXISTS idx_addresses_user_id ON addresses(user_id);
CREATE INDEX IF NOT EXISTS idx_addresses_name ON addresses(name);

CREATE TABLE IF NOT EXISTS gatekeepers (
    id            INTEGER PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    from_address  TEXT    NOT NULL,
    destination   TEXT    NOT NULL,
    created_at    INTEGER NOT NULL,
    UNIQUE (user_id, from_address)
) STRICT;

CREATE TABLE IF NOT EXISTS mailboxes (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name           TEXT    NOT NULL,
    special_use    TEXT,
    uidvalidity    INTEGER NOT NULL,
    uidnext        INTEGER NOT NULL DEFAULT 1,
    highest_modseq INTEGER NOT NULL DEFAULT 0,
    UNIQUE (user_id, name)
) STRICT;

CREATE TABLE IF NOT EXISTS subscriptions (
    id             INTEGER PRIMARY KEY,
    user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    mailbox_id     INTEGER REFERENCES mailboxes(id) ON DELETE SET NULL,
    mailbox_name   TEXT, -- mailboxes can be subscribed even if mailbox does not exist, hence duplicate name
    UNIQUE (user_id, mailbox_id)
) STRICT;

CREATE TABLE IF NOT EXISTS messages (
    id            INTEGER PRIMARY KEY,
    blob_address  TEXT    NOT NULL UNIQUE,
    size          INTEGER NOT NULL,
    subject       TEXT,
    message_id    TEXT,
    in_reply_to   TEXT,

    -- Envelope fields
    date          TEXT,
    from_addr     TEXT,             -- JSON array of addresses
    sender        TEXT,             -- JSON array of addresses
    reply_to      TEXT,             -- JSON array of addresses
    to_addr       TEXT,             -- JSON array of addresses
    cc            TEXT,             -- JSON array of addresses
    bcc           TEXT              -- JSON array of addresses
) STRICT;

CREATE TABLE IF NOT EXISTS mailbox_messages (
    mailbox_id      INTEGER NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
    uid             INTEGER NOT NULL,
    message_id      INTEGER NOT NULL REFERENCES messages(id),
    modseq          INTEGER NOT NULL,
    internal_date   TEXT NOT NULL,
    expunged_modseq INTEGER,
    PRIMARY KEY (mailbox_id, uid)
) STRICT;

CREATE TABLE IF NOT EXISTS message_flags (
    mailbox_id INTEGER NOT NULL,
    uid        INTEGER NOT NULL,
    flag       TEXT    NOT NULL,
    PRIMARY KEY (mailbox_id, uid, flag),
    FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
    FOREIGN KEY (mailbox_id, uid) REFERENCES mailbox_messages(mailbox_id, uid) ON DELETE CASCADE ON UPDATE CASCADE
) STRICT;

CREATE TABLE IF NOT EXISTS dkim (
    id             INTEGER PRIMARY KEY,
    created_at     INTEGER NOT NULL,
    domain         TEXT NOT NULL,
    key_type       TEXT NOT NULL,
    blob_address   TEXT NOT NULL,
    selector       TEXT NOT NULL,
    enabled        INTEGER NOT NULL,
    UNIQUE (domain, selector)
) STRICT;

CREATE TABLE IF NOT EXISTS smtp_auth (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL UNIQUE,
    password_hash BLOB    NOT NULL,
    created_at    INTEGER NOT NULL
) STRICT;

CREATE INDEX IF NOT EXISTS idx_mailbox_messages_modseq
    ON mailbox_messages(mailbox_id, modseq);

CREATE INDEX IF NOT EXISTS idx_mailbox_messages_message
    ON mailbox_messages(message_id);

CREATE TABLE IF NOT EXISTS keywords (
    message_id INTEGER NOT NULL REFERENCES messages(id),
    keyword    TEXT    NOT NULL,
    PRIMARY KEY (message_id, keyword)
) STRICT;

CREATE INDEX IF NOT EXISTS idx_keywords_keyword
    ON keywords(keyword);

CREATE TABLE IF NOT EXISTS uidvalidity_counter (
    id      INTEGER PRIMARY KEY CHECK (id = 1),
    counter INTEGER NOT NULL DEFAULT 0
) STRICT;

INSERT INTO uidvalidity_counter (id, counter)
VALUES (1, 0)
ON CONFLICT(id) DO NOTHING;
`

func (db *DB) applyPragmas(ctx context.Context) error {
	for _, conn := range []*sql.DB{db.write, db.read} {
		if _, err := conn.ExecContext(ctx, pragmas); err != nil {
			return fmt.Errorf("applying pragmas: %w", err)
		}
	}
	return nil
}

func (db *DB) applySchema(ctx context.Context) error {
	if _, err := db.write.ExecContext(ctx, schema); err != nil {
		return fmt.Errorf("applying schema: %w", err)
	}
	return nil
}

func (db *DB) Close() error {
	werr := db.write.Close()
	rerr := db.read.Close()
	if werr != nil {
		return werr
	}
	return rerr
}

func (db *DB) GetLastUID(ctx context.Context, userID, mailboxID int) (imap.UID, error) {
	var uid imap.UID
	err := db.read.QueryRowContext(ctx, `
        SELECT COALESCE(MAX(mm.uid), 0)
        FROM mailbox_messages mm
        JOIN mailboxes mb ON mb.id = mm.mailbox_id
        WHERE mm.mailbox_id = ?
          AND mb.user_id = ?
          AND mm.expunged_modseq IS NULL
    `, mailboxID, userID).Scan(&uid)
	return uid, err
}

// nextUIDValidity is db roll-back save. Even with a played-back backup, the counter will be increasing.
// It will also be increasing if it is called more than once per second.
func nextUIDValidity(tx *sql.Tx) (uint32, error) {
	var v int64
	err := tx.QueryRow(`
        UPDATE uidvalidity_counter
        SET counter = MAX(counter + 1, CAST(strftime('%s','now') AS INTEGER))
        RETURNING counter
    `).Scan(&v)
	if err != nil {
		return 0, err
	}
	return uint32(v), nil
}

func txRollback(tx *sql.Tx) {
	err := tx.Rollback()
	if !errors.Is(err, sql.ErrTxDone) {
		slog.Error("failed to roll back transaction", "error", err)
	}
}