PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS client_accounts (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL,
  token_prefix TEXT NOT NULL,
  credits_remaining INTEGER NOT NULL DEFAULT 0 CHECK (credits_remaining >= 0),
  enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_client_accounts_token_hash
ON client_accounts(token_hash);

CREATE TABLE IF NOT EXISTS usage_sessions (
  id TEXT PRIMARY KEY NOT NULL,
  account_id TEXT NOT NULL REFERENCES client_accounts(id) ON DELETE CASCADE,
  status TEXT NOT NULL,
  started_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL,
  billed_seconds INTEGER NOT NULL DEFAULT 0 CHECK (billed_seconds >= 0),
  max_seconds INTEGER NOT NULL CHECK (max_seconds > 0),
  expires_at INTEGER NOT NULL,
  ended_at INTEGER
);

CREATE INDEX IF NOT EXISTS idx_usage_sessions_account_status
ON usage_sessions(account_id, status);

CREATE UNIQUE INDEX IF NOT EXISTS idx_usage_sessions_one_active
ON usage_sessions(account_id)
WHERE status = 'active';

CREATE TRIGGER IF NOT EXISTS trg_usage_sessions_bill_credits
AFTER UPDATE OF billed_seconds ON usage_sessions
WHEN NEW.billed_seconds > OLD.billed_seconds
BEGIN
  UPDATE client_accounts
  SET credits_remaining = MAX(
    0,
    credits_remaining - ((NEW.billed_seconds - OLD.billed_seconds) * 2)
  ),
  updated_at = CAST(strftime('%s', 'now') AS INTEGER) * 1000
  WHERE id = NEW.account_id;
END;

PRAGMA optimize;