"""Database schema.

Kept apart from store.py so the SQL is readable in one place.

The `complaints` table started life as a classification log and is now the real
complaint entity. Rather than adding a parallel table, the extra columns are
added by an idempotent migration in store.migrate() -- existing rows (from the
classifier demo page) simply have no user and stay in status 'Submitted'.
"""

BASE_SCHEMA = """
CREATE TABLE IF NOT EXISTS labels (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL UNIQUE,
    description TEXT    NOT NULL DEFAULT '',
    active      INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS complaints (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    original_text   TEXT    NOT NULL,
    translated_text TEXT    NOT NULL,
    source_lang     TEXT    NOT NULL DEFAULT 'unknown',
    was_translated  INTEGER NOT NULL DEFAULT 0,
    predicted_label TEXT    NOT NULL,
    confidence      REAL    NOT NULL,
    scores_json     TEXT    NOT NULL DEFAULT '[]',
    engine          TEXT    NOT NULL DEFAULT '',
    took_ms         INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_complaints_created ON complaints(created_at DESC);

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name     TEXT    NOT NULL,
    email         TEXT    NOT NULL UNIQUE,
    phone         TEXT    NOT NULL DEFAULT '',
    password_hash TEXT    NOT NULL,
    language      TEXT    NOT NULL DEFAULT 'en',
    is_admin      INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS complaint_photos (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
    filename     TEXT    NOT NULL,
    mime         TEXT    NOT NULL DEFAULT 'image/jpeg',
    bytes        INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_photos_complaint ON complaint_photos(complaint_id);

-- One row per status change: this is what the tracking timeline renders.
CREATE TABLE IF NOT EXISTS status_events (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
    status       TEXT    NOT NULL,
    note         TEXT    NOT NULL DEFAULT '',
    created_at   TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_events_complaint ON status_events(complaint_id, id);

CREATE TABLE IF NOT EXISTS notifications (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    complaint_id INTEGER REFERENCES complaints(id) ON DELETE CASCADE,
    message      TEXT    NOT NULL,
    is_read      INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, id DESC);

CREATE TABLE IF NOT EXISTS feedback (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL UNIQUE REFERENCES complaints(id) ON DELETE CASCADE,
    rating       INTEGER NOT NULL,
    comment      TEXT    NOT NULL DEFAULT '',
    created_at   TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS departments (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL UNIQUE,
    created_at TEXT    NOT NULL
);
"""

# (column, DDL type/default) added to `complaints` if not already present.
COMPLAINT_COLUMNS = [
    ("ref", "TEXT"),                                  # UC2026-000123
    ("user_id", "INTEGER"),
    ("category", "TEXT NOT NULL DEFAULT ''"),         # final, admin may override
    ("location", "TEXT NOT NULL DEFAULT ''"),
    ("latitude", "REAL"),
    ("longitude", "REAL"),
    ("status", "TEXT NOT NULL DEFAULT 'Submitted'"),
    ("department", "TEXT NOT NULL DEFAULT ''"),
    ("extra_details", "TEXT NOT NULL DEFAULT ''"),
    ("updated_at", "TEXT"),
]

# The lifecycle a complaint moves through, in order.
STATUSES = ["Submitted", "Under Review", "Assigned", "In Progress", "Resolved"]

DEFAULT_DEPARTMENTS = [
    "Municipal Engineering Department",
    "Water Board",
    "Electricity Board",
    "Public Health Department",
    "Waste Management Unit",
    "Roads & Highways Authority",
    "General Administration",
]

# Which department a category routes to by default when an admin assigns.
CATEGORY_DEPARTMENT = {
    "Water Supply": "Water Board",
    "Electricity": "Electricity Board",
    "Street Lighting": "Electricity Board",
    "Road & Transport": "Roads & Highways Authority",
    "Waste & Sanitation": "Waste Management Unit",
    "Public Health": "Public Health Department",
    "Noise & Pollution": "Public Health Department",
    "Building & Property": "Municipal Engineering Department",
}