import Database from 'better-sqlite3';

import { ulid } from 'ulid';

import fs from 'fs';

import path from 'path';

const DATA_DIR = process.env.ZPM_DATA_DIR || path.join(process.cwd(), 'data');

fs.mkdirSync(DATA_DIR, { recursive: true });

const DB_PATH = path.join(DATA_DIR, 'zionpm.db');

export const db = new Database(DB_PATH);

db.pragma('journal_mode = WAL');

db.exec(`

CREATE TABLE IF NOT EXISTS channels(

  id            TEXT PRIMARY KEY,

  name          TEXT NOT NULL UNIQUE,

  default_model TEXT NOT NULL,

  created_at    INTEGER NOT NULL

);

CREATE TABLE IF NOT EXISTS messages(

  id            TEXT PRIMARY KEY,

  channel_id    TEXT NOT NULL REFERENCES channels(id),

  role          TEXT NOT NULL,

  author_model  TEXT,

  body          TEXT NOT NULL,

  parent_id     TEXT REFERENCES messages(id),

  created_at    INTEGER NOT NULL

);

CREATE TABLE IF NOT EXISTS tasks(

  id             TEXT PRIMARY KEY,

  title          TEXT NOT NULL,

  status         TEXT NOT NULL DEFAULT 'todo',

  assigned_model TEXT,

  channel_id     TEXT REFERENCES channels(id),

  created_at     INTEGER NOT NULL,

  updated_at     INTEGER NOT NULL

);

CREATE TABLE IF NOT EXISTS outbox(

  id           TEXT PRIMARY KEY,

  action_type  TEXT NOT NULL,

  payload_json TEXT NOT NULL,

  status       TEXT NOT NULL DEFAULT 'draft',

  created_at   INTEGER NOT NULL,

  approved_at  INTEGER

);

CREATE TABLE IF NOT EXISTS cap_state(

  model        TEXT PRIMARY KEY,

  available    INTEGER NOT NULL,

  reason       TEXT,

  last_checked INTEGER NOT NULL

);

`);

export const now = () => Date.now();

export { ulid };

function seed() {

  const chanCount = (db.prepare('SELECT COUNT(*) c FROM channels').get() as { c: number }).c;

  if (chanCount === 0) {

    const ins = db.prepare(

      'INSERT INTO channels(id,name,default_model,created_at) VALUES(?,?,?,?)'

    );

    const seedCh: [string, string][] = [

      ['pm', 'glm'],

      ['claude', 'claude'],

      ['codex', 'codex'],

      ['ollama', 'ollama'],

    ];

    const t = now();

    for (const [name, model] of seedCh) ins.run(ulid(), name, model, t);

  }

  const capIns = db.prepare(

    `INSERT INTO cap_state(model,available,reason,last_checked)

     VALUES(?,?,?,?) ON CONFLICT(model) DO NOTHING`

  );

  const t = now();

  for (const m of ['claude', 'codex', 'ollama']) capIns.run(m, 1, 'seeded', t);

}

seed();

export interface Channel { id: string; name: string; default_model: string; created_at: number; }

export interface Message { id: string; channel_id: string; role: string; author_model: string | null; body: string; parent_id: string | null; created_at: number; }

export interface Task { id: string; title: string; status: string; assigned_model: string | null; channel_id: string | null; created_at: number; updated_at: number; }

export interface Outbox { id: string; action_type: string; payload_json: string; status: string; created_at: number; approved_at: number | null; }

export interface CapState { model: string; available: number; reason: string | null; last_checked: number; }

export const listChannels = () => db.prepare('SELECT * FROM channels ORDER BY created_at').all() as Channel[];

export const getChannel = (id: string) => db.prepare('SELECT * FROM channels WHERE id=?').get(id) as Channel | undefined;

export const getChannelByName = (name: string) => db.prepare('SELECT * FROM channels WHERE name=?').get(name) as Channel | undefined;

export function createChannel(name: string, defaultModel: string): Channel {

  const id = ulid();

  db.prepare('INSERT INTO channels(id,name,default_model,created_at) VALUES(?,?,?,?)').run(id, name, defaultModel, now());

  return getChannel(id)!;

}

export const listMessages = (channelId: string) =>

  db.prepare('SELECT * FROM messages WHERE channel_id=? ORDER BY created_at').all(channelId) as Message[];

export function addMessage(channelId: string, role: string, body: string, authorModel: string | null = null, parentId: string | null = null): Message {

  const id = ulid();

  db.prepare('INSERT INTO messages(id,channel_id,role,author_model,body,parent_id,created_at) VALUES(?,?,?,?,?,?,?)')

    .run(id, channelId, role, authorModel, body, parentId, now());

  return db.prepare('SELECT * FROM messages WHERE id=?').get(id) as Message;

}

export const listTasks = () => db.prepare('SELECT * FROM tasks ORDER BY created_at DESC').all() as Task[];

export function createTask(title: string, opts: { status?: string; assignedModel?: string | null; channelId?: string | null } = {}): Task {

  const id = ulid();

  const t = now();

  db.prepare('INSERT INTO tasks(id,title,status,assigned_model,channel_id,created_at,updated_at) VALUES(?,?,?,?,?,?,?)')

    .run(id, title, opts.status || 'todo', opts.assignedModel ?? null, opts.channelId ?? null, t, t);

  return db.prepare('SELECT * FROM tasks WHERE id=?').get(id) as Task;

}

export function updateTask(id: string, patch: { status?: string; assignedModel?: string | null; title?: string }): Task | undefined {

  const cur = db.prepare('SELECT * FROM tasks WHERE id=?').get(id) as Task | undefined;

  if (!cur) return undefined;

  db.prepare('UPDATE tasks SET status=?, assigned_model=?, title=?, updated_at=? WHERE id=?')

    .run(patch.status ?? cur.status, patch.assignedModel ?? cur.assigned_model, patch.title ?? cur.title, now(), id);

  return db.prepare('SELECT * FROM tasks WHERE id=?').get(id) as Task;

}

export const listOutbox = () => db.prepare('SELECT * FROM outbox ORDER BY created_at DESC').all() as Outbox[];

export function draftOutbox(actionType: string, payload: unknown): Outbox {

  const id = ulid();

  db.prepare('INSERT INTO outbox(id,action_type,payload_json,status,created_at) VALUES(?,?,?,?,?)')

    .run(id, actionType, JSON.stringify(payload), 'draft', now());

  return db.prepare('SELECT * FROM outbox WHERE id=?').get(id) as Outbox;

}

export function setOutboxStatus(id: string, status: 'approved' | 'rejected' | 'sent'): Outbox | undefined {

  const approvedAt = status === 'approved' ? now() : null;

  db.prepare('UPDATE outbox SET status=?, approved_at=COALESCE(?,approved_at) WHERE id=?').run(status, approvedAt, id);

  return db.prepare('SELECT * FROM outbox WHERE id=?').get(id) as Outbox | undefined;

}

export const listCaps = () => db.prepare('SELECT * FROM cap_state ORDER BY model').all() as CapState[];

export const getCap = (model: string) => db.prepare('SELECT * FROM cap_state WHERE model=?').get(model) as CapState | undefined;

export function setCap(model: string, available: number, reason: string) {

  db.prepare(`INSERT INTO cap_state(model,available,reason,last_checked) VALUES(?,?,?,?)

              ON CONFLICT(model) DO UPDATE SET available=excluded.available, reason=excluded.reason, last_checked=excluded.last_checked`)

    .run(model, available, reason, now());

}

export const touchCap = (model: string) => db.prepare('UPDATE cap_state SET last_checked=? WHERE model=?').run(now(), model);