package db

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

	_ "modernc.org/sqlite"
)

var schemaVersion = len(schemaChanges())

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

func schemaChanges() []string {

	schema := []string{`

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;

CREATE TABLE IF NOT EXISTS schema_version (
    version INT NOT NULL
) STRICT;
`}

	schema = append(schema, `
CREATE TABLE kv_store (
    key        TEXT PRIMARY KEY,
    value      ANY,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
) WITHOUT ROWID; -- WITHOUT ROWID optimizes key-value lookups
`)

	return schema
}

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 {
	var currentVersion int
	// Ignore the error on purpose in case schema_version does not exist _yet_.
	// The very first schema can be applied idempotently.
	_ = db.read.QueryRowContext(ctx, `SELECT version FROM schema_version`).Scan(&currentVersion)

	for version := currentVersion; version < schemaVersion; version++ {
		newVersion := version + 1

		slog.InfoContext(ctx, "updating schema version", "from", currentVersion, "to", newVersion)

		tx, err := db.GetWriteTx(ctx)
		if err != nil {
			return errors.Join(fmt.Errorf("migration %d transaction not started", newVersion), err)
		}

		if _, err := tx.ExecContext(ctx, schemaChanges()[version]); err != nil {
			errRollback := tx.Rollback()
			return errors.Join(fmt.Errorf("migration %d rolled back", newVersion), err, errRollback)
		}

		if _, err := tx.ExecContext(ctx, `DELETE FROM schema_version`); err != nil {
			errRollback := tx.Rollback()
			return errors.Join(fmt.Errorf("migration %d rolled back", newVersion), err, errRollback)
		}

		if _, err := tx.ExecContext(ctx, `INSERT INTO schema_version (version) VALUES (?)`, newVersion); err != nil {
			errRollback := tx.Rollback()
			return errors.Join(fmt.Errorf("migration %d rolled back", newVersion), err, errRollback)
		}

		if err := tx.Commit(); err != nil {
			return errors.Join(fmt.Errorf("migration %d not commited", newVersion), err)
		}
	}

	slog.InfoContext(ctx, "applying schema", "version", schemaVersion)
	return nil
}

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

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