"""SQLite storage (time-series friendly schema + indexes). Session 10."""
import sqlite3
from contextlib import contextmanager

from werkzeug.security import generate_password_hash

import config

SCHEMA = """
CREATE TABLE IF NOT EXISTS nodes (
    node_id   TEXT PRIMARY KEY,
    name      TEXT,
    zone      TEXT,                -- greenhouse / field
    lat       REAL,
    lon       REAL,
    last_seen TEXT,
    firmware  TEXT
);
CREATE TABLE IF NOT EXISTS readings (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id           TEXT NOT NULL,
    ts                TEXT NOT NULL,     -- ISO-8601 local time
    air_temp_c        REAL,
    air_humidity_pct  REAL,
    soil_moisture_pct REAL,
    light             INTEGER,
    battery_v         REAL,
    rssi              INTEGER
);
CREATE INDEX IF NOT EXISTS ix_readings_node_ts ON readings (node_id, ts);
CREATE INDEX IF NOT EXISTS ix_readings_ts ON readings (ts);
CREATE TABLE IF NOT EXISTS commands (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id      TEXT NOT NULL,
    actuator     TEXT NOT NULL,          -- pump | fan
    action       TEXT NOT NULL,          -- on | off
    duration_min INTEGER DEFAULT 0,
    status       TEXT DEFAULT 'pending', -- pending -> delivered -> done | expired
    source       TEXT DEFAULT 'manual',  -- manual | rule | ml
    created_by   TEXT,
    created_at   TEXT,
    delivered_at TEXT,
    acked_at     TEXT
);
CREATE TABLE IF NOT EXISTS decisions (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    ts       TEXT,
    node_id  TEXT,
    source   TEXT,      -- rule | ml | manual | system
    level    TEXT,      -- info | warning | critical
    message  TEXT
);
CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT UNIQUE,
    password_hash TEXT
);
"""

DEFAULT_NODES = [
    ("greenhouse_01", "الصوبة التجريبية", "greenhouse", 31.1052, 30.9431),
    ("field_A", "الحقل A — خضر", "field", 31.1047, 30.9442),
    ("field_B", "الحقل B — ذرة", "field", 31.1041, 30.9436),
]


@contextmanager
def connect():
    con = sqlite3.connect(config.DB_PATH, timeout=10)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")      # readers don't block the MQTT writer
    try:
        yield con
        con.commit()
    finally:
        con.close()


def init_db():
    with connect() as con:
        con.executescript(SCHEMA)
        for node in DEFAULT_NODES:
            con.execute("INSERT OR IGNORE INTO nodes (node_id, name, zone, lat, lon) VALUES (?,?,?,?,?)", node)
        if not con.execute("SELECT 1 FROM users WHERE username=?", (config.ADMIN_USER,)).fetchone():
            con.execute("INSERT INTO users (username, password_hash) VALUES (?,?)",
                        (config.ADMIN_USER, generate_password_hash(config.ADMIN_PASSWORD)))


def rows(sql, params=()):
    with connect() as con:
        return [dict(r) for r in con.execute(sql, params).fetchall()]


def execute(sql, params=()):
    with connect() as con:
        cur = con.execute(sql, params)
        return cur.lastrowid


def log_decision(ts, node_id, source, level, message):
    execute("INSERT INTO decisions (ts, node_id, source, level, message) VALUES (?,?,?,?,?)",
            (ts, node_id, source, level, message))