"""Database helpers: connection + schema bootstrap."""
import pymysql
from pymysql.cursors import DictCursor

import config

SCHEMA = """
CREATE TABLE IF NOT EXISTS inboxes (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    address     VARCHAR(255) NOT NULL UNIQUE,   -- e.g. abc123@tempmail.local
    token       VARCHAR(64)  NOT NULL UNIQUE,    -- secret used in the inbox URL
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  DATETIME     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS emails (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    inbox_id      BIGINT NOT NULL,
    from_addr     VARCHAR(320) NOT NULL DEFAULT '',
    to_addr       VARCHAR(320) NOT NULL DEFAULT '',
    subject       VARCHAR(1000) NOT NULL DEFAULT '',
    body_text     MEDIUMTEXT,
    body_html     MEDIUMTEXT,
    received_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    seen          TINYINT(1) NOT NULL DEFAULT 0,
    INDEX (inbox_id),
    CONSTRAINT fk_email_inbox FOREIGN KEY (inbox_id)
        REFERENCES inboxes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""


def _connect(with_db: bool = True):
    kwargs = dict(
        host=config.DB_HOST,
        port=config.DB_PORT,
        user=config.DB_USER,
        password=config.DB_PASSWORD,
        charset="utf8mb4",
        cursorclass=DictCursor,
        autocommit=True,
    )
    if with_db:
        kwargs["database"] = config.DB_NAME
    return pymysql.connect(**kwargs)


def get_conn():
    """Return a live connection to the tempmail database."""
    return _connect(with_db=True)


def init_db():
    """Create the database and tables if they don't exist yet."""
    root = _connect(with_db=False)
    with root.cursor() as cur:
        cur.execute(
            f"CREATE DATABASE IF NOT EXISTS {config.DB_NAME} "
            f"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        )
    root.close()

    conn = get_conn()
    with conn.cursor() as cur:
        for stmt in SCHEMA.strip().split(";"):
            if stmt.strip():
                cur.execute(stmt)
    conn.close()
    print(f"[db] ready -> database '{config.DB_NAME}' on "
          f"{config.DB_HOST}:{config.DB_PORT}")


if __name__ == "__main__":
    init_db()