-- Chat SPBoucher AI — initial schema (PostgreSQL 17 + pgvector)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ---------------------------------------------------------------- owner & auth
CREATE TABLE IF NOT EXISTS owner (
  id            smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  username      text NOT NULL,
  password_hash text NOT NULL,              -- argon2id
  totp_secret   text,                        -- optional 2FA (encrypted at rest by app key)
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sessions (
  id          text PRIMARY KEY,              -- sha256(token)
  created_at  timestamptz NOT NULL DEFAULT now(),
  expires_at  timestamptz NOT NULL,
  last_seen   timestamptz NOT NULL DEFAULT now(),
  user_agent  text,
  ip          text,
  revoked_at  timestamptz
);
CREATE INDEX IF NOT EXISTS sessions_expires_idx ON sessions (expires_at);

CREATE TABLE IF NOT EXISTS login_attempts (
  id        bigserial PRIMARY KEY,
  ip        text NOT NULL,
  at        timestamptz NOT NULL DEFAULT now(),
  success   boolean NOT NULL
);
CREATE INDEX IF NOT EXISTS login_attempts_ip_at_idx ON login_attempts (ip, at DESC);

CREATE TABLE IF NOT EXISTS webauthn_credentials (
  id            text PRIMARY KEY,
  public_key    bytea NOT NULL,
  counter       bigint NOT NULL DEFAULT 0,
  transports    text[],
  label         text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------- settings (key/value)
CREATE TABLE IF NOT EXISTS settings (
  key        text PRIMARY KEY,
  value      jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------- model registry
CREATE TABLE IF NOT EXISTS models (
  id                text PRIMARY KEY,
  provider          text NOT NULL CHECK (provider IN ('openai','anthropic','local')),
  display_name      text NOT NULL,
  kind              text NOT NULL DEFAULT 'chat',   -- chat | chat-pro | search | image | tts | stt | embedding | moderation | realtime | video | legacy-completion | deep-research | audio-chat
  api               text,                           -- responses | chat | messages
  status            text NOT NULL DEFAULT 'untested', -- verified | unavailable | unsupported | untested
  available         boolean NOT NULL DEFAULT false,
  tested            boolean NOT NULL DEFAULT false,
  unavailable_reason text,
  capabilities      jsonb NOT NULL DEFAULT '{}'::jsonb,
  context_window    integer,
  max_output_tokens integer,
  pricing           jsonb,
  benchmark         jsonb,
  hidden            boolean NOT NULL DEFAULT false,
  favorite          boolean NOT NULL DEFAULT false,
  sort_order        integer NOT NULL DEFAULT 0,
  last_verified_at  timestamptz,
  first_seen_at     timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------- projects
CREATE TABLE IF NOT EXISTS projects (
  id              text PRIMARY KEY,
  name            text NOT NULL,
  description     text,
  instructions    text,
  default_model   text,
  color           text,
  icon            text,
  tool_permissions jsonb NOT NULL DEFAULT '{}'::jsonb,
  notes           text,
  sort_order      integer NOT NULL DEFAULT 0,
  archived_at     timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------- conversations & messages
CREATE TABLE IF NOT EXISTS conversations (
  id               text PRIMARY KEY,
  project_id       text REFERENCES projects(id) ON DELETE SET NULL,
  title            text NOT NULL DEFAULT 'Nouvelle conversation',
  model_id         text,
  settings         jsonb NOT NULL DEFAULT '{}'::jsonb,   -- effort, routing mode, tools enabled, system override…
  pinned           boolean NOT NULL DEFAULT false,
  archived_at      timestamptz,
  parent_id        text REFERENCES conversations(id) ON DELETE SET NULL,   -- branch origin
  branch_message_id text,
  mode             text NOT NULL DEFAULT 'chat',          -- chat | compare | research
  message_count    integer NOT NULL DEFAULT 0,
  total_cost_usd   numeric(12,6) NOT NULL DEFAULT 0,
  last_message_at  timestamptz,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  tsv              tsvector
);
CREATE INDEX IF NOT EXISTS conversations_project_idx ON conversations (project_id);
CREATE INDEX IF NOT EXISTS conversations_updated_idx ON conversations (updated_at DESC);
CREATE INDEX IF NOT EXISTS conversations_tsv_idx ON conversations USING gin (tsv);

CREATE TABLE IF NOT EXISTS messages (
  id              text PRIMARY KEY,
  conversation_id text NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  parent_id       text REFERENCES messages(id) ON DELETE SET NULL,   -- message tree (branching / regenerate)
  role            text NOT NULL CHECK (role IN ('user','assistant','system','tool')),
  parts           jsonb NOT NULL DEFAULT '[]'::jsonb,    -- [{type:text|image|file|tool_call|tool_result|reasoning|artifact|citation,...}]
  text            text NOT NULL DEFAULT '',             -- plain text (search + context)
  model_id        text,
  provider        text,
  settings        jsonb,                                 -- effort/routing used for THIS response
  routing         jsonb,                                 -- {mode, requested, selected, reason}
  usage           jsonb,                                 -- {input, output, cached, reasoning}
  cost_usd        numeric(12,6),
  latency_ms      integer,
  ttft_ms         integer,
  status          text NOT NULL DEFAULT 'complete',      -- streaming | complete | error | cancelled
  error           text,
  active          boolean NOT NULL DEFAULT true,         -- false for superseded siblings (regenerate/edit)
  created_at      timestamptz NOT NULL DEFAULT now(),
  tsv             tsvector GENERATED ALWAYS AS (to_tsvector('simple', coalesce(text,''))) STORED
);
CREATE INDEX IF NOT EXISTS messages_conv_idx ON messages (conversation_id, created_at);
CREATE INDEX IF NOT EXISTS messages_parent_idx ON messages (parent_id);
CREATE INDEX IF NOT EXISTS messages_tsv_idx ON messages USING gin (tsv);

CREATE TABLE IF NOT EXISTS message_embeddings (
  message_id text PRIMARY KEY REFERENCES messages(id) ON DELETE CASCADE,
  embedding  vector(1536) NOT NULL,
  model      text NOT NULL DEFAULT 'text-embedding-3-small',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS message_embeddings_idx ON message_embeddings USING hnsw (embedding vector_cosine_ops);

-- ---------------------------------------------------------------- attachments & document chunks
CREATE TABLE IF NOT EXISTS attachments (
  id              text PRIMARY KEY,
  conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,
  project_id      text REFERENCES projects(id) ON DELETE SET NULL,
  message_id      text REFERENCES messages(id) ON DELETE SET NULL,
  kind            text NOT NULL DEFAULT 'upload',       -- upload | generated-image | audio | export
  filename        text NOT NULL,
  mime            text NOT NULL,
  size_bytes      bigint NOT NULL,
  path            text NOT NULL,                        -- relative to DATA_DIR/uploads
  sha256          text,
  extracted_text  text,
  page_count      integer,
  meta            jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS attachments_conv_idx ON attachments (conversation_id);
CREATE INDEX IF NOT EXISTS attachments_project_idx ON attachments (project_id);

CREATE TABLE IF NOT EXISTS document_chunks (
  id            bigserial PRIMARY KEY,
  attachment_id text NOT NULL REFERENCES attachments(id) ON DELETE CASCADE,
  idx           integer NOT NULL,
  page          integer,
  content       text NOT NULL,
  embedding     vector(1536),
  tsv           tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED
);
CREATE INDEX IF NOT EXISTS document_chunks_att_idx ON document_chunks (attachment_id, idx);
CREATE INDEX IF NOT EXISTS document_chunks_tsv_idx ON document_chunks USING gin (tsv);
CREATE INDEX IF NOT EXISTS document_chunks_emb_idx ON document_chunks USING hnsw (embedding vector_cosine_ops);

-- ---------------------------------------------------------------- memory
CREATE TABLE IF NOT EXISTS memories (
  id                     text PRIMARY KEY,
  category               text NOT NULL DEFAULT 'context',   -- preference | writing | research | project | infrastructure | objective | concept | context
  content                text NOT NULL,
  source                 text NOT NULL DEFAULT 'manual',    -- manual | extracted | imported
  source_conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,
  source_message_id      text REFERENCES messages(id) ON DELETE SET NULL,
  project_id             text REFERENCES projects(id) ON DELETE CASCADE,  -- null = global
  status                 text NOT NULL DEFAULT 'active',    -- candidate | active | disabled
  confidence             real,
  embedding              vector(1536),
  use_count              integer NOT NULL DEFAULT 0,
  last_used_at           timestamptz,
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now(),
  tsv                    tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED
);
CREATE INDEX IF NOT EXISTS memories_status_idx ON memories (status);
CREATE INDEX IF NOT EXISTS memories_emb_idx ON memories USING hnsw (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS memories_tsv_idx ON memories USING gin (tsv);

-- ---------------------------------------------------------------- artifacts
CREATE TABLE IF NOT EXISTS artifacts (
  id              text PRIMARY KEY,
  conversation_id text REFERENCES conversations(id) ON DELETE CASCADE,
  message_id      text REFERENCES messages(id) ON DELETE SET NULL,
  project_id      text REFERENCES projects(id) ON DELETE SET NULL,
  root_id         text,                                  -- first version id
  version         integer NOT NULL DEFAULT 1,
  type            text NOT NULL,                         -- html | react | typescript | python | markdown | table | chart | svg | mermaid | math | code
  language        text,
  title           text NOT NULL,
  content         text NOT NULL,
  saved           boolean NOT NULL DEFAULT false,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS artifacts_conv_idx ON artifacts (conversation_id);
CREATE INDEX IF NOT EXISTS artifacts_root_idx ON artifacts (root_id, version);

-- ---------------------------------------------------------------- usage & spending
CREATE TABLE IF NOT EXISTS usage_events (
  id              bigserial PRIMARY KEY,
  at              timestamptz NOT NULL DEFAULT now(),
  provider        text NOT NULL,
  model_id        text NOT NULL,
  kind            text NOT NULL DEFAULT 'chat',          -- chat | compare | research | title | memory | embedding | image | tts | stt | judge | tool
  conversation_id text,
  project_id      text,
  message_id      text,
  input_tokens    integer NOT NULL DEFAULT 0,
  output_tokens   integer NOT NULL DEFAULT 0,
  cached_tokens   integer NOT NULL DEFAULT 0,
  reasoning_tokens integer NOT NULL DEFAULT 0,
  cost_estimated  numeric(12,6),
  cost_confirmed  numeric(12,6),                        -- filled when a provider billing export is imported
  latency_ms      integer,
  ttft_ms         integer,
  status          text NOT NULL DEFAULT 'ok',            -- ok | error | cancelled
  error           text,
  meta            jsonb
);
CREATE INDEX IF NOT EXISTS usage_at_idx ON usage_events (at DESC);
CREATE INDEX IF NOT EXISTS usage_model_idx ON usage_events (model_id, at DESC);
CREATE INDEX IF NOT EXISTS usage_project_idx ON usage_events (project_id, at DESC);

-- ---------------------------------------------------------------- comparisons & research
CREATE TABLE IF NOT EXISTS comparisons (
  id               text PRIMARY KEY,
  conversation_id  text REFERENCES conversations(id) ON DELETE SET NULL,
  project_id       text REFERENCES projects(id) ON DELETE SET NULL,
  prompt           text NOT NULL,
  model_ids        text[] NOT NULL,
  results          jsonb NOT NULL DEFAULT '[]'::jsonb,   -- [{modelId, text, usage, cost, latency, ttft, error}]
  judge            jsonb,                                 -- {modelId, verdict, text}
  preferred_model  text,
  created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS research_runs (
  id              text PRIMARY KEY,
  conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,
  project_id      text REFERENCES projects(id) ON DELETE SET NULL,
  question        text NOT NULL,
  model_id        text NOT NULL,
  status          text NOT NULL DEFAULT 'planning',      -- planning | searching | reading | synthesizing | complete | error | cancelled
  plan            jsonb,
  steps           jsonb NOT NULL DEFAULT '[]'::jsonb,
  sources         jsonb NOT NULL DEFAULT '[]'::jsonb,
  report_md       text,
  cost_usd        numeric(12,6) NOT NULL DEFAULT 0,
  error           text,
  created_at      timestamptz NOT NULL DEFAULT now(),
  completed_at    timestamptz
);

-- ---------------------------------------------------------------- tool executions (audit log)
CREATE TABLE IF NOT EXISTS tool_executions (
  id              text PRIMARY KEY,
  conversation_id text,
  message_id      text,
  tool            text NOT NULL,
  input           jsonb,
  output          jsonb,
  status          text NOT NULL,                         -- pending_approval | approved | denied | ok | error
  duration_ms     integer,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS tool_exec_conv_idx ON tool_executions (conversation_id, created_at DESC);

-- ---------------------------------------------------------------- triggers
CREATE OR REPLACE FUNCTION conversations_tsv_update() RETURNS trigger AS $$
BEGIN NEW.tsv := to_tsvector('simple', coalesce(NEW.title,'')); RETURN NEW; END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS conversations_tsv_trg ON conversations;
CREATE TRIGGER conversations_tsv_trg BEFORE INSERT OR UPDATE OF title ON conversations FOR EACH ROW EXECUTE FUNCTION conversations_tsv_update();

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at := now(); RETURN NEW; END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS conversations_touch ON conversations;
CREATE TRIGGER conversations_touch BEFORE UPDATE ON conversations FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
DROP TRIGGER IF EXISTS projects_touch ON projects;
CREATE TRIGGER projects_touch BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
DROP TRIGGER IF EXISTS memories_touch ON memories;
CREATE TRIGGER memories_touch BEFORE UPDATE ON memories FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- default settings
INSERT INTO settings (key, value) VALUES
  ('memory', '{"enabled": true, "autoExtract": true, "requireApproval": false}'),
  ('spending', '{"dailyLimitUsd": 10, "monthlyLimitUsd": 150, "alertAtPercent": 80}'),
  ('routing', '{"defaultMode": "manual"}'),
  ('appearance', '{"theme": "dark"}')
ON CONFLICT (key) DO NOTHING;