"""SQLite data layer for ApprovalFlow."""
import json
import os
import secrets
import sqlite3
from datetime import datetime, timezone

from werkzeug.security import generate_password_hash

DB_PATH = os.environ.get("DATABASE_PATH", os.path.join(os.path.dirname(__file__), "data", "approvalflow.db"))
ATTACH_DIR = os.path.join(os.path.dirname(DB_PATH), "attachments")
EMAIL_IMG_DIR = os.path.join(os.path.dirname(DB_PATH), "email_images")

MAX_EMAIL_IMAGE_BYTES = 2 * 1024 * 1024  # 2 MB per email image
EMAIL_IMAGE_TYPES = {".png": "png", ".jpg": "jpeg", ".jpeg": "jpeg", ".gif": "gif"}

MAX_ATTACHMENT_BYTES = 15 * 1024 * 1024  # 15 MB


def attachment_path(stored_name):
    return os.path.join(ATTACH_DIR, stored_name)

SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    email TEXT DEFAULT '',
    first_name TEXT NOT NULL DEFAULT '',
    last_name TEXT NOT NULL DEFAULT '',
    unit_location TEXT NOT NULL DEFAULT '',
    mfa_secret TEXT NOT NULL DEFAULT '',     -- empty = MFA disabled
    is_admin INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS workflows (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT DEFAULT '',
    approvers TEXT NOT NULL DEFAULT '[]',    -- JSON list, in approval order
    knowledge TEXT NOT NULL DEFAULT '[]',    -- JSON list: informed of every event, never approve
    active INTEGER NOT NULL DEFAULT 1,
    -- personalization (templates support {placeholders})
    approval_subject TEXT NOT NULL,
    approval_body TEXT NOT NULL,
    approved_subject TEXT NOT NULL,
    approved_body TEXT NOT NULL,
    denied_subject TEXT NOT NULL,
    denied_body TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user_workflows (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    workflow_id INTEGER NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, workflow_id)
);

CREATE TABLE IF NOT EXISTS requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_id INTEGER NOT NULL REFERENCES workflows(id),
    source TEXT NOT NULL DEFAULT 'web',      -- requests are created on the site
    requester TEXT NOT NULL,                 -- email address (or username for web)
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    participants TEXT NOT NULL DEFAULT '[]', -- JSON list of everyone involved so far
    status TEXT NOT NULL DEFAULT 'pending',  -- pending / approved / denied
    current_step INTEGER NOT NULL DEFAULT 0, -- index into the workflow's approver chain
    token TEXT UNIQUE NOT NULL,              -- secret for the CURRENT step's approve/deny links
    decided_by TEXT DEFAULT '',
    deny_reason TEXT NOT NULL DEFAULT '',
    attachment TEXT NOT NULL DEFAULT '[]',   -- JSON list of {"stored": ..., "name": ...}
    created_at TEXT NOT NULL,
    decided_at TEXT
);

CREATE TABLE IF NOT EXISTS decisions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id INTEGER NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
    step INTEGER NOT NULL,                   -- 1-based position in the chain
    decided_by TEXT NOT NULL,
    decision TEXT NOT NULL,                  -- approved / denied
    at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS request_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id INTEGER REFERENCES requests(id) ON DELETE CASCADE,
    at TEXT NOT NULL,
    event TEXT NOT NULL,
    detail TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS mail_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    at TEXT NOT NULL,
    request_id INTEGER,                      -- related request, if any
    recipients TEXT NOT NULL,
    subject TEXT NOT NULL,
    status TEXT NOT NULL,                    -- sent / error / dev
    detail TEXT DEFAULT '',                  -- SMTP error message, if any
    sent_copy TEXT DEFAULT ''                -- result of saving to the Sent folder
);
"""

DEFAULT_TEMPLATES = {
    "approval_subject": "[{workflow}] Approval needed ({step}/{total_steps}): {subject}",
    "approval_body": (
        "A request needs your decision (approval step {step} of {total_steps}).\n\n"
        "Workflow:  {workflow}\n"
        "From:      {requester}\n"
        "Created:   {created_at}\n"
        "Subject:   {subject}\n\n"
        "Message:\n{body}\n\n"
        "Approve:   {approve_url}\n"
        "Deny:      {deny_url}\n\n"
        "If you approve, the request moves on to the next approver.\n"
        "If you deny, the workflow stops and everyone involved is notified.\n"
    ),
    "approved_subject": "[{workflow}] Approved: {subject}",
    "approved_body": (
        "The following request was APPROVED.\n\n"
        "Workflow:  {workflow}\n"
        "From:      {requester}\n"
        "Created:   {created_at}\n"
        "Subject:   {subject}\n\n"
        "Message:\n{body}\n\n"
        "Summary:\n{summary}\n"
    ),
    "denied_subject": "[{workflow}] Denied: {subject}",
    "denied_body": (
        "The following request was DENIED by {decided_by}.\n\n"
        "Workflow:  {workflow}\n"
        "From:      {requester}\n"
        "Created:   {created_at}\n"
        "Subject:   {subject}\n\n"
        "Message:\n{body}\n\n"
        "Reason for denial:\n{deny_reason}\n"
    ),
}


def now():
    return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")


def get_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    con = sqlite3.connect(DB_PATH, timeout=15)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA foreign_keys = ON")
    con.execute("PRAGMA journal_mode = WAL")
    con.execute("PRAGMA busy_timeout = 15000")
    return con


def _migrate(con):
    """Upgrade a database created by the single-approver version, if present."""
    cols = {r["name"] for r in con.execute("PRAGMA table_info(workflows)")}
    if "approver_email" in cols and "approvers" not in cols:
        con.execute("ALTER TABLE workflows ADD COLUMN approvers TEXT NOT NULL DEFAULT '[]'")
        for w in con.execute("SELECT id, approver_email FROM workflows").fetchall():
            con.execute("UPDATE workflows SET approvers=? WHERE id=?",
                        (json.dumps([w["approver_email"]]), w["id"]))
        print("[migrate] Converted single approver_email to approver chains.")
    rcols = {r["name"] for r in con.execute("PRAGMA table_info(requests)")}
    if rcols and "current_step" not in rcols:
        con.execute("ALTER TABLE requests ADD COLUMN current_step INTEGER NOT NULL DEFAULT 0")
        print("[migrate] Added current_step to requests.")
    if rcols and "deny_reason" not in rcols:
        con.execute("ALTER TABLE requests ADD COLUMN deny_reason TEXT NOT NULL DEFAULT ''")
        print("[migrate] Added deny_reason to requests.")
    if rcols and "attachment" not in rcols:
        con.execute("ALTER TABLE requests ADD COLUMN attachment TEXT NOT NULL DEFAULT ''")
        print("[migrate] Added attachment to requests.")
    ucols = {r["name"] for r in con.execute("PRAGMA table_info(users)")}
    if ucols and "mfa_secret" not in ucols:
        con.execute("ALTER TABLE users ADD COLUMN mfa_secret TEXT NOT NULL DEFAULT ''")
        print("[migrate] Added mfa_secret to users.")
    for col in ("first_name", "last_name", "unit_location"):
        if ucols and col not in ucols:
            con.execute(f"ALTER TABLE users ADD COLUMN {col} TEXT NOT NULL DEFAULT ''")
            print(f"[migrate] Added {col} to users.")
    wcols = {r["name"] for r in con.execute("PRAGMA table_info(workflows)")}
    if wcols and "knowledge" not in wcols:
        con.execute("ALTER TABLE workflows ADD COLUMN knowledge TEXT NOT NULL DEFAULT '[]'")
        print("[migrate] Added knowledge chain to workflows.")
    # single-attachment values -> JSON list format
    if rcols and "attachment" in rcols:
        for row in con.execute("SELECT id, attachment FROM requests "
                               "WHERE attachment != '' AND attachment NOT LIKE '[%'").fetchall():
            stored = row["attachment"]
            entry = [{"stored": stored, "name": stored.split("_", 1)[-1]}]
            con.execute("UPDATE requests SET attachment=? WHERE id=?",
                        (json.dumps(entry), row["id"]))
            print(f"[migrate] Converted attachment of request #{row['id']} to list format.")
        con.execute("UPDATE requests SET attachment='[]' WHERE attachment=''")
    con.commit()


def init_db():
    con = get_db()
    _migrate(con)
    con.executescript(SCHEMA)
    # bootstrap admin user on first run
    if con.execute("SELECT COUNT(*) c FROM users").fetchone()["c"] == 0:
        username = os.environ.get("ADMIN_USERNAME", "admin")
        password = os.environ.get("ADMIN_PASSWORD", "changeme")
        con.execute(
            "INSERT INTO users (username, password_hash, is_admin, created_at) VALUES (?,?,1,?)",
            (username, generate_password_hash(password), now()),
        )
        print(f"[init] Created admin user '{username}'. Change the password immediately.")
    con.commit()
    con.close()


def log(con, request_id, event, detail=""):
    con.execute(
        "INSERT INTO request_log (request_id, at, event, detail) VALUES (?,?,?,?)",
        (request_id, now(), event, detail),
    )


def add_participants(con, request_id, emails):
    """Add people to the 'involved so far' list of a request."""
    row = con.execute("SELECT participants FROM requests WHERE id=?", (request_id,)).fetchone()
    current = json.loads(row["participants"])
    for e in emails:
        e = (e or "").strip().lower()
        if e and e not in current:
            current.append(e)
    con.execute("UPDATE requests SET participants=? WHERE id=?", (json.dumps(current), request_id))
    return current


def create_request(con, workflow_id, source, requester, subject, body, extra_participants=()):
    token = secrets.token_urlsafe(32)
    cur = con.execute(
        "INSERT INTO requests (workflow_id, source, requester, subject, body, token, created_at) "
        "VALUES (?,?,?,?,?,?,?)",
        (workflow_id, source, requester, subject, body, token, now()),
    )
    rid = cur.lastrowid
    add_participants(con, rid, [requester, *extra_participants])
    log(con, rid, "received", f"Request received via {source} from {requester}")
    return rid, token