import { DatabaseSync } from "node:sqlite";
import { mkdirSync, chmodSync } from "node:fs";
import { dirname } from "node:path";
import { randomUUID } from "node:crypto";
import type {
  AppSettings,
  CheckResult,
  ClientSite,
  EmailAlertLog,
  Incident,
} from "../src/types.ts";
import { DEFAULT_SETTINGS } from "./validation.ts";
export class Store {
  db: DatabaseSync;
  constructor(filename: string) {
    if (filename !== ":memory:")
      mkdirSync(dirname(filename), { recursive: true, mode: 0o700 });
    this.db = new DatabaseSync(filename);
    if (filename !== ":memory:") chmodSync(filename, 0o600);
    this.db
      .exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;
      CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY CHECK(id=1), data TEXT NOT NULL);
      CREATE TABLE IF NOT EXISTS sites (id TEXT PRIMARY KEY, url TEXT UNIQUE NOT NULL, data TEXT NOT NULL, failures INTEGER NOT NULL DEFAULT 0, last_result TEXT);
      CREATE TABLE IF NOT EXISTS checks (id INTEGER PRIMARY KEY, site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE, checked_at TEXT NOT NULL, result TEXT NOT NULL);
      CREATE INDEX IF NOT EXISTS checks_site_time ON checks(site_id, checked_at);
      CREATE TABLE IF NOT EXISTS incidents (id TEXT PRIMARY KEY, site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE, opened_at TEXT NOT NULL, resolved_at TEXT, error_type TEXT NOT NULL, error_message TEXT NOT NULL, notes TEXT NOT NULL DEFAULT '');
      CREATE UNIQUE INDEX IF NOT EXISTS one_open_incident ON incidents(site_id) WHERE resolved_at IS NULL;
      CREATE TABLE IF NOT EXISTS notifications (id TEXT PRIMARY KEY, event_key TEXT UNIQUE NOT NULL, data TEXT NOT NULL, status TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_attempt INTEGER NOT NULL DEFAULT 0);
      CREATE TABLE IF NOT EXISTS admins (username TEXT PRIMARY KEY, password_hash TEXT NOT NULL);
      CREATE TABLE IF NOT EXISTS sessions (token_hash TEXT PRIMARY KEY, username TEXT NOT NULL REFERENCES admins(username) ON DELETE CASCADE, csrf TEXT NOT NULL, expires_at INTEGER NOT NULL);
      CREATE TABLE IF NOT EXISTS audit (id INTEGER PRIMARY KEY, at TEXT NOT NULL, actor TEXT NOT NULL, action TEXT NOT NULL, target TEXT NOT NULL);
    `);
    this.db
      .prepare("INSERT OR IGNORE INTO settings VALUES(1, ?)")
      .run(JSON.stringify(DEFAULT_SETTINGS));
  }
  settings(): AppSettings {
    return JSON.parse(
      (
        this.db.prepare("SELECT data FROM settings WHERE id=1").get() as {
          data: string;
        }
      ).data,
    );
  }
  saveSettings(value: AppSettings) {
    this.db
      .prepare("UPDATE settings SET data=? WHERE id=1")
      .run(JSON.stringify(value));
  }
  sites(): ClientSite[] {
    return this.db
      .prepare("SELECT * FROM sites ORDER BY rowid DESC")
      .all()
      .map((r) => this.decodeSite(r));
  }
  site(id: string): ClientSite | undefined {
    const r = this.db.prepare("SELECT * FROM sites WHERE id=?").get(id);
    return r ? this.decodeSite(r) : undefined;
  }
  private decodeSite(r: Record<string, unknown>): ClientSite {
    return {
      ...JSON.parse(r.data as string),
      id: r.id as string,
      consecutiveFailures: r.failures as number,
      lastResult: r.last_result
        ? JSON.parse(r.last_result as string)
        : undefined,
    };
  }
  saveSite(
    value: Omit<ClientSite, "id">,
    id: string = randomUUID(),
  ): ClientSite {
    const old = this.site(id);
    this.db
      .prepare(
        `INSERT INTO sites(id,url,data) VALUES(?,?,?) ON CONFLICT(id) DO UPDATE SET url=excluded.url, data=excluded.data`,
      )
      .run(id, value.url, JSON.stringify(value));
    if (old && (old.url !== value.url || old.restApiUrl !== value.restApiUrl)) {
      this.db
        .prepare("UPDATE sites SET failures=0,last_result=NULL WHERE id=?")
        .run(id);
      this.db.prepare("DELETE FROM checks WHERE site_id=?").run(id);
      this.db
        .prepare(
          "UPDATE incidents SET resolved_at=? WHERE site_id=? AND resolved_at IS NULL",
        )
        .run(new Date().toISOString(), id);
    }
    return this.site(id)!;
  }
  removeSite(id: string) {
    const site = this.site(id);
    if (site)
      this.db
        .prepare(
          "UPDATE notifications SET status='failed',data=json_set(data,'$.deliveryError','Website dipadam; notifikasi dibatalkan.') WHERE status='pending' AND json_extract(data,'$.siteUrl')=?",
        )
        .run(site.url);
    this.db.prepare("DELETE FROM sites WHERE id=?").run(id);
  }
  transaction<T>(fn: () => T): T {
    this.db.exec("BEGIN IMMEDIATE");
    try {
      const r = fn();
      this.db.exec("COMMIT");
      return r;
    } catch (e) {
      this.db.exec("ROLLBACK");
      throw e;
    }
  }
  record(
    site: ClientSite,
    result: CheckResult,
  ): { opened?: Incident; recovered?: Incident } {
    return this.transaction(() => {
      const current = this.site(site.id);
      if (
        !current ||
        current.url !== site.url ||
        current.restApiUrl !== site.restApiUrl
      )
        return {};
      const bad = result.status === "down" || result.status === "error";
      const quiet =
        !!current.maintenanceUntil &&
        Date.parse(current.maintenanceUntil) > Date.now();
      const failures = quiet
        ? 0
        : result.status === "unknown"
          ? current.consecutiveFailures || 0
          : bad
            ? (current.consecutiveFailures || 0) + 1
            : 0;
      this.db
        .prepare("UPDATE sites SET failures=?,last_result=? WHERE id=?")
        .run(failures, JSON.stringify(result), site.id);
      this.db
        .prepare("INSERT INTO checks(site_id,checked_at,result) VALUES(?,?,?)")
        .run(site.id, result.checkedAt, JSON.stringify(result));
      const active = this.db
        .prepare(
          "SELECT id FROM incidents WHERE site_id=? AND resolved_at IS NULL",
        )
        .get(site.id) as { id: string } | undefined;
      if (
        !quiet &&
        bad &&
        failures >= this.settings().failureThreshold &&
        !active
      ) {
        const id: string = randomUUID();
        const first = this.db
          .prepare(
            "SELECT checked_at FROM checks WHERE site_id=? ORDER BY id DESC LIMIT 1 OFFSET ?",
          )
          .get(site.id, failures - 1) as { checked_at: string } | undefined;
        this.db
          .prepare(
            "INSERT INTO incidents(id,site_id,opened_at,error_type,error_message) VALUES(?,?,?,?,?)",
          )
          .run(
            id,
            site.id,
            first?.checked_at || result.checkedAt,
            result.wpErrorType || "http_error",
            result.wpErrorMessage || result.statusText,
          );
        return { opened: this.incidents().find((i) => i.id === id) };
      }
      if (!bad && result.status !== "unknown" && active) {
        this.db
          .prepare("UPDATE incidents SET resolved_at=? WHERE id=?")
          .run(result.checkedAt, active.id);
        return { recovered: this.incidents().find((i) => i.id === active.id) };
      }
      return {};
    });
  }
  incidents(): Incident[] {
    return this.db
      .prepare(
        "SELECT i.*,s.data,s.url FROM incidents i JOIN sites s ON s.id=i.site_id ORDER BY i.opened_at DESC LIMIT 200",
      )
      .all()
      .map((r) => ({
        id: r.id as string,
        siteId: r.site_id as string,
        siteName: JSON.parse(r.data as string).name,
        siteUrl: r.url as string,
        openedAt: r.opened_at as string,
        resolvedAt: (r.resolved_at as string) || undefined,
        errorType: r.error_type as string,
        errorMessage: r.error_message as string,
        notes: r.notes as string,
      }));
  }
  logs(): EmailAlertLog[] {
    return this.db
      .prepare(
        "SELECT data,status,attempts FROM notifications ORDER BY rowid DESC LIMIT 200",
      )
      .all()
      .map((r) => ({
        ...JSON.parse(r.data as string),
        status: r.status,
        attempts: r.attempts,
      }));
  }
  queueMail(
    eventKey: string,
    value: Omit<EmailAlertLog, "id" | "sentAt" | "status">,
  ): string {
    const data = {
      ...value,
      id: randomUUID(),
      sentAt: new Date().toISOString(),
      status: "pending",
    };
    this.db
      .prepare(
        "INSERT OR IGNORE INTO notifications(id,event_key,data,status) VALUES(?,?,?,?)",
      )
      .run(data.id, eventKey, JSON.stringify(data), "pending");
    return (
      this.db
        .prepare("SELECT id FROM notifications WHERE event_key=?")
        .get(eventKey) as { id: string }
    ).id;
  }
  log(id: string): EmailAlertLog {
    const r = this.db
      .prepare("SELECT data,status,attempts FROM notifications WHERE id=?")
      .get(id)!;
    return {
      ...JSON.parse(r.data as string),
      status: r.status,
      attempts: r.attempts,
    };
  }
  audit(actor: string, action: string, target: string) {
    this.db
      .prepare("INSERT INTO audit(at,actor,action,target) VALUES(?,?,?,?)")
      .run(new Date().toISOString(), actor, action, target);
  }
  history(id: string) {
    return this.db
      .prepare(
        "SELECT result FROM checks WHERE site_id=? ORDER BY id DESC LIMIT 500",
      )
      .all(id)
      .map((r) => JSON.parse(r.result as string) as CheckResult)
      .reverse();
  }
  prune(days: number) {
    const before = new Date(Date.now() - days * 86400000).toISOString();
    this.db.prepare("DELETE FROM checks WHERE checked_at<?").run(before);
    this.db.prepare("DELETE FROM sessions WHERE expires_at<?").run(Date.now());
    this.db.prepare("DELETE FROM audit WHERE at<?").run(before);
    this.db
      .prepare(
        "DELETE FROM notifications WHERE status!='pending' AND json_extract(data,'$.sentAt')<?",
      )
      .run(before);
  }
  close() {
    this.db.close();
  }
}