"""SQLite helpers and schema."""
import sqlite3
from datetime import datetime
from pathlib import Path

import config

SCHEMA = """
CREATE TABLE IF NOT EXISTS tasks (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    title        TEXT NOT NULL,
    notes        TEXT NOT NULL DEFAULT '',
    due_date     TEXT,              -- YYYY-MM-DD (local), NULL = someday
    due_time     TEXT,              -- HH:MM (local), optional
    rrule        TEXT,              -- e.g. FREQ=WEEKLY;BYDAY=MO (no 'RRULE:' prefix)
    recur_from   TEXT NOT NULL DEFAULT 'due',   -- 'due' | 'done'
    priority     INTEGER NOT NULL DEFAULT 0,    -- 0 none, 1 low, 2 medium, 3 high
    tags         TEXT NOT NULL DEFAULT '',      -- ' work home/garden ' (space-wrapped)
    completed_at TEXT,              -- ISO timestamp; only set for non-recurring tasks
    created_at   TEXT NOT NULL,
    updated_at   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_tasks_due ON tasks(completed_at, due_date);

-- One row per completion. For recurring tasks this is how "undo" works.
CREATE TABLE IF NOT EXISTS task_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id      INTEGER NOT NULL,
    title        TEXT NOT NULL,
    due_date     TEXT,              -- the due date that was completed
    completed_at TEXT NOT NULL
);

-- Google calendars you've connected (refreshed from Google on each sync).
CREATE TABLE IF NOT EXISTS calendars (
    id          TEXT PRIMARY KEY,
    summary     TEXT NOT NULL,
    color       TEXT NOT NULL DEFAULT '#4285f4',
    access_role TEXT NOT NULL DEFAULT 'reader',
    visible     INTEGER NOT NULL DEFAULT 1,
    is_default  INTEGER NOT NULL DEFAULT 0
);

-- Local copy of upcoming Google events (a cache; Google is the source of truth).
CREATE TABLE IF NOT EXISTS events (
    calendar_id  TEXT NOT NULL,
    id           TEXT NOT NULL,
    summary      TEXT NOT NULL,
    start        TEXT NOT NULL,     -- all-day: YYYY-MM-DD, timed: ISO with offset
    end          TEXT NOT NULL,     -- all-day end is exclusive (Google convention)
    start_ts     INTEGER NOT NULL,  -- unix seconds (all-day: local midnight) for fast day lookups
    end_ts       INTEGER NOT NULL,
    all_day      INTEGER NOT NULL,
    location     TEXT NOT NULL DEFAULT '',
    html_link    TEXT NOT NULL DEFAULT '',
    recurring    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (calendar_id, id)
);
CREATE INDEX IF NOT EXISTS idx_events_time ON events(start_ts, end_ts);

-- Wrong-password counts per network address (brute-force protection).
CREATE TABLE IF NOT EXISTS login_attempts (
    ip      TEXT PRIMARY KEY,
    fails   INTEGER NOT NULL,
    last_ts INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);
"""


def connect() -> sqlite3.Connection:
    Path(config.DB_PATH).parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(config.DB_PATH, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA busy_timeout = 15000")
    return conn


# Columns added after the first release: (table, column, definition).
# init_db() adds any that an existing database is missing.
MIGRATIONS = [
    ("tasks", "tags", "TEXT NOT NULL DEFAULT ''"),
]


def init_db() -> None:
    conn = connect()
    try:
        conn.execute("PRAGMA journal_mode = WAL")   # persistent: set once, not per connection
        # `events` is only a local copy of Google Calendar. If it was made by an older
        # version (missing the start_ts/end_ts columns), drop it; the next sync refills it.
        ev_cols = {r["name"] for r in conn.execute("PRAGMA table_info(events)")}
        if ev_cols and not {"start_ts", "end_ts"} <= ev_cols:
            conn.execute("DROP TABLE events")
        conn.executescript(SCHEMA)
        for table, column, definition in MIGRATIONS:
            have = {r["name"] for r in conn.execute(f"PRAGMA table_info({table})")}
            if column not in have:
                conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")
        conn.commit()
    finally:
        conn.close()


def now_iso() -> str:
    return datetime.now().astimezone().isoformat(timespec="seconds")


def get_setting(conn, key, default=None):
    row = conn.execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
    return row["value"] if row else default


def set_setting(conn, key, value) -> None:
    conn.execute(
        "INSERT INTO settings(key, value) VALUES(?, ?) "
        "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
        (key, value),
    )


def delete_setting(conn, key) -> None:
    conn.execute("DELETE FROM settings WHERE key = ?", (key,))
