-- wp.spboucher.ai — initial schema (PostgreSQL 17, pgvector, pg_trgm, unaccent, pgcrypto)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Immutable helpers so that generated tsvector columns can use them.
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  AS $$ SELECT public.unaccent('public.unaccent', $1) $$;
CREATE OR REPLACE FUNCTION jsonb_strings(j jsonb) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  WITH RECURSIVE walk(v) AS (
    SELECT j
    UNION ALL
    SELECT CASE WHEN jsonb_typeof(v) = 'array' THEN e.value WHEN jsonb_typeof(v) = 'object' THEN o.value END
    FROM walk LEFT JOIN LATERAL jsonb_array_elements(CASE WHEN jsonb_typeof(v) = 'array' THEN v ELSE '[]'::jsonb END) e ON true
              LEFT JOIN LATERAL jsonb_each(CASE WHEN jsonb_typeof(v) = 'object' THEN v ELSE '{}'::jsonb END) o ON true
    WHERE jsonb_typeof(v) IN ('array', 'object')
  )
  SELECT coalesce(string_agg(v #>> '{}', ' '), '') FROM walk WHERE jsonb_typeof(v) = 'string'
$$;
CREATE OR REPLACE FUNCTION arr_text(a text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT coalesce(array_to_string(a, ' '), '') $$;

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

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  password_hash text NOT NULL,
  role text NOT NULL DEFAULT 'owner' CHECK (role IN ('owner', 'editor')),
  totp_secret text,
  totp_enabled boolean NOT NULL DEFAULT false,
  must_change_password boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  last_login_at timestamptz
);
CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  csrf_secret text NOT NULL,
  user_agent text, ip text,
  created_at timestamptz NOT NULL DEFAULT now(),
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  absolute_expires_at timestamptz NOT NULL,
  rotated_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz,
  mfa_passed boolean NOT NULL DEFAULT false
);
CREATE INDEX sessions_user_idx ON sessions(user_id);
CREATE TABLE login_attempts (id bigserial PRIMARY KEY, ip text NOT NULL, email text, success boolean NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX login_attempts_ip_idx ON login_attempts(ip, created_at);
CREATE INDEX login_attempts_email_idx ON login_attempts(email, created_at);

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  actor_id uuid, actor_label text NOT NULL,
  action text NOT NULL, entity_type text NOT NULL, entity_id text,
  before jsonb, after jsonb, ip text,
  at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_log_at_idx ON audit_log(at DESC);
CREATE INDEX audit_log_entity_idx ON audit_log(entity_type, entity_id);

CREATE TABLE series (
  id serial PRIMARY KEY, slug text NOT NULL UNIQUE, code text NOT NULL UNIQUE,
  name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,
  issn text, sort int NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true
);
CREATE TABLE authors (
  id serial PRIMARY KEY, slug text NOT NULL UNIQUE,
  given_name text NOT NULL, family_name text NOT NULL, display_name text NOT NULL,
  orcid text, email text, website text, github text, google_scholar text, ssrn_url text, repec text,
  affiliation_en text, affiliation_fr text, department_en text, department_fr text,
  bio_en text, bio_fr text, is_primary boolean NOT NULL DEFAULT false, sort int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE topics (
  id serial PRIMARY KEY, slug text NOT NULL UNIQUE,
  name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,
  kind text NOT NULL DEFAULT 'topic' CHECK (kind IN ('domain', 'topic')),
  parent_id int REFERENCES topics(id) ON DELETE SET NULL,
  accent text, sort int NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true
);
CREATE TABLE methods (
  id serial PRIMARY KEY, slug text NOT NULL UNIQUE,
  name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,
  category text NOT NULL DEFAULT 'general', sort int NOT NULL DEFAULT 0
);
CREATE TABLE datasets (
  id serial PRIMARY KEY, slug text NOT NULL UNIQUE,
  name_en text NOT NULL, name_fr text NOT NULL, provider text, coverage text, frequency text,
  access_conditions text, license text, url text, description_en text, description_fr text,
  is_public boolean NOT NULL DEFAULT false, sort int NOT NULL DEFAULT 0
);

CREATE TABLE files (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kind text NOT NULL CHECK (kind IN ('manuscript', 'supplement', 'preview', 'other')),
  storage_key text NOT NULL UNIQUE,
  original_name text, mime text NOT NULL, bytes bigint NOT NULL, sha256 text NOT NULL,
  pages int, pdf_meta jsonb, integrity_ok boolean, text_status text NOT NULL DEFAULT 'pending' CHECK (text_status IN ('pending', 'done', 'failed', 'skipped')),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX files_sha_idx ON files(sha256);

CREATE TABLE papers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  series_id int NOT NULL REFERENCES series(id),
  number int NOT NULL,
  identifier text NOT NULL UNIQUE,          -- permanent public identifier, e.g. WP38, FIRC0002
  slug text NOT NULL UNIQUE,                -- URL slug, e.g. wp38
  internal_code text,                       -- internal project number / repository name (not proof of release)
  title text NOT NULL, subtitle text, title_fr text, subtitle_fr text,
  abstract jsonb NOT NULL DEFAULT '[]'::jsonb,         -- array of paragraphs
  abstract_fr jsonb, structured_abstract jsonb,
  language text NOT NULL DEFAULT 'en',
  research_question text, research_question_fr text,
  hero_headline text, hero_subheadline text,
  contributions jsonb NOT NULL DEFAULT '[]'::jsonb,    -- [{kind?, title, description}]
  methodology jsonb NOT NULL DEFAULT '[]'::jsonb,      -- [{title, description}] (verified fields only)
  data_sections jsonb NOT NULL DEFAULT '[]'::jsonb,    -- [{title, description}]
  findings jsonb NOT NULL DEFAULT '[]'::jsonb,         -- [{value, label}]
  reproducibility jsonb NOT NULL DEFAULT '[]'::jsonb,  -- [string]
  artifacts jsonb NOT NULL DEFAULT '[]'::jsonb,        -- [{kind, label, url}]
  notice text, notice_fr text,
  keywords text[] NOT NULL DEFAULT '{}',
  jel text[] NOT NULL DEFAULT '{}',
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'reserved', 'published', 'withdrawn', 'superseded')),
  publication_status text NOT NULL DEFAULT 'working_paper' CHECK (publication_status IN ('working_paper', 'under_review', 'forthcoming', 'published')),
  journal_ref text, doi text, ssrn_id text, external_links jsonb NOT NULL DEFAULT '[]'::jsonb,
  year int, first_released_on date, first_released_precision text NOT NULL DEFAULT 'month' CHECK (first_released_precision IN ('day', 'month', 'year')),
  last_revised_on date, last_revised_precision text NOT NULL DEFAULT 'month' CHECK (last_revised_precision IN ('day', 'month', 'year')),
  current_version_id uuid,
  featured boolean NOT NULL DEFAULT false, featured_rank int,
  primary_domain_id int REFERENCES topics(id) ON DELETE SET NULL,
  view_count bigint NOT NULL DEFAULT 0, download_count bigint NOT NULL DEFAULT 0,
  embedding vector(1536), lexvec vector(512), embedding_model text,
  fts tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', f_unaccent(coalesce(title, ''))), 'A') ||
    setweight(to_tsvector('english', f_unaccent(arr_text(keywords))), 'A') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(subtitle, ''))), 'B') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(research_question, ''))), 'B') ||
    setweight(to_tsvector('english', f_unaccent(jsonb_strings(abstract))), 'B') ||
    setweight(to_tsvector('english', f_unaccent(jsonb_strings(findings))), 'C') ||
    setweight(to_tsvector('english', f_unaccent(jsonb_strings(contributions))), 'C') ||
    setweight(to_tsvector('english', f_unaccent(jsonb_strings(methodology))), 'C') ||
    setweight(to_tsvector('english', f_unaccent(jsonb_strings(data_sections))), 'C') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(identifier, '') || ' ' || arr_text(jel))), 'D')
  ) STORED,
  search_blob text GENERATED ALWAYS AS (f_unaccent(lower(coalesce(title, '') || ' ' || coalesce(subtitle, '') || ' ' || arr_text(keywords)))) STORED,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), published_at timestamptz,
  UNIQUE (series_id, number)
);
CREATE INDEX papers_fts_idx ON papers USING gin(fts);
CREATE INDEX papers_blob_trgm_idx ON papers USING gin(search_blob gin_trgm_ops);
CREATE INDEX papers_status_idx ON papers(status, year DESC, number DESC);
CREATE INDEX papers_keywords_idx ON papers USING gin(keywords);

CREATE TABLE paper_authors (
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  author_id int NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
  position int NOT NULL DEFAULT 1, affiliation_note text,
  PRIMARY KEY (paper_id, author_id)
);
CREATE INDEX paper_authors_author_idx ON paper_authors(author_id);
CREATE TABLE paper_topics (
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  topic_id int NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
  assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),
  PRIMARY KEY (paper_id, topic_id)
);
CREATE INDEX paper_topics_topic_idx ON paper_topics(topic_id);
CREATE TABLE paper_methods (
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  method_id int NOT NULL REFERENCES methods(id) ON DELETE CASCADE,
  note text, assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),
  PRIMARY KEY (paper_id, method_id)
);
CREATE INDEX paper_methods_method_idx ON paper_methods(method_id);
CREATE TABLE paper_datasets (
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  dataset_id int NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,
  role text, note text, assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),
  PRIMARY KEY (paper_id, dataset_id)
);
CREATE INDEX paper_datasets_dataset_idx ON paper_datasets(dataset_id);

CREATE TABLE paper_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  version_no int NOT NULL, label text NOT NULL,
  released_on date, released_precision text NOT NULL DEFAULT 'month' CHECK (released_precision IN ('day', 'month', 'year')),
  description text, description_fr text,
  file_id uuid REFERENCES files(id) ON DELETE SET NULL,
  pages int, sha256 text, bytes bigint,
  is_current boolean NOT NULL DEFAULT false,
  withdrawn boolean NOT NULL DEFAULT false, withdrawn_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (paper_id, version_no)
);
CREATE INDEX paper_versions_paper_idx ON paper_versions(paper_id, version_no DESC);
ALTER TABLE papers ADD CONSTRAINT papers_current_version_fk FOREIGN KEY (current_version_id) REFERENCES paper_versions(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE paper_chunks (
  id bigserial PRIMARY KEY,
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  version_id uuid REFERENCES paper_versions(id) ON DELETE CASCADE,
  chunk_index int NOT NULL, page_from int, page_to int,
  text text NOT NULL,
  fts tsvector GENERATED ALWAYS AS (to_tsvector('english', f_unaccent(text))) STORED,
  embedding vector(1536)
);
CREATE INDEX paper_chunks_paper_idx ON paper_chunks(paper_id, chunk_index);
CREATE INDEX paper_chunks_fts_idx ON paper_chunks USING gin(fts);

CREATE TABLE paper_relations (
  id bigserial PRIMARY KEY,
  from_paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  to_paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  kind text NOT NULL CHECK (kind IN ('topic', 'method', 'dataset', 'author', 'semantic', 'lexical', 'curated')),
  weight real NOT NULL DEFAULT 1, explanation text, explanation_fr text,
  inferred boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (from_paper_id, to_paper_id, kind), CHECK (from_paper_id <> to_paper_id)
);
CREATE INDEX paper_relations_from_idx ON paper_relations(from_paper_id, weight DESC);

CREATE TABLE redirects (from_path text PRIMARY KEY, to_path text NOT NULL, status int NOT NULL DEFAULT 301, note text, created_at timestamptz NOT NULL DEFAULT now());

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
  version_id uuid, kind text NOT NULL CHECK (kind IN ('view', 'download')),
  occurred_at timestamptz NOT NULL DEFAULT now(), day date NOT NULL DEFAULT current_date,
  visitor_hash text NOT NULL, referrer_host text, locale text, is_bot boolean NOT NULL DEFAULT false
);
CREATE INDEX events_day_idx ON events(day, paper_id);
CREATE UNIQUE INDEX events_dedupe_idx ON events(paper_id, kind, day, visitor_hash);
CREATE TABLE paper_stats_daily (paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE, day date NOT NULL, views int NOT NULL DEFAULT 0, downloads int NOT NULL DEFAULT 0, PRIMARY KEY (paper_id, day));

CREATE TABLE search_log (id bigserial PRIMARY KEY, query text NOT NULL, mode text NOT NULL, results int NOT NULL, locale text, took_ms int, at timestamptz NOT NULL DEFAULT now());
CREATE INDEX search_log_at_idx ON search_log(at DESC);

CREATE TABLE ingest_candidates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_path text NOT NULL, file_id uuid REFERENCES files(id) ON DELETE SET NULL, sha256 text NOT NULL,
  proposal jsonb NOT NULL DEFAULT '{}'::jsonb, flags jsonb NOT NULL DEFAULT '[]'::jsonb, duplicates jsonb NOT NULL DEFAULT '[]'::jsonb,
  status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'processing', 'ready', 'approved', 'rejected', 'merged')),
  paper_id uuid REFERENCES papers(id) ON DELETE SET NULL,
  reviewed_by uuid, reviewed_at timestamptz, review_note text,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ingest_candidates_sha_idx ON ingest_candidates(sha256) WHERE status IN ('queued', 'processing', 'ready');

CREATE TABLE jobs (
  id bigserial PRIMARY KEY, kind text NOT NULL, payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'done', 'failed')),
  attempts int NOT NULL DEFAULT 0, max_attempts int NOT NULL DEFAULT 3,
  run_after timestamptz NOT NULL DEFAULT now(), locked_at timestamptz, last_error text,
  created_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz
);
CREATE INDEX jobs_queue_idx ON jobs(status, run_after) WHERE status IN ('queued', 'running');

CREATE TABLE backups (id bigserial PRIMARY KEY, kind text NOT NULL, path text NOT NULL, bytes bigint, sha256 text, ok boolean NOT NULL, log text, remote_ok boolean, created_at timestamptz NOT NULL DEFAULT now());

-- Public views: everything the read-only role may see.
CREATE VIEW public_papers AS SELECT * FROM papers WHERE status IN ('published', 'withdrawn', 'superseded');
CREATE VIEW public_paper_versions AS SELECT v.* FROM paper_versions v JOIN papers p ON p.id = v.paper_id WHERE p.status IN ('published', 'withdrawn', 'superseded');
CREATE VIEW public_paper_chunks AS SELECT c.id, c.paper_id, c.version_id, c.chunk_index, c.page_from, c.page_to, c.text, c.fts, c.embedding FROM paper_chunks c JOIN papers p ON p.id = c.paper_id WHERE p.status = 'published';
CREATE VIEW public_paper_relations AS SELECT r.* FROM paper_relations r JOIN papers a ON a.id = r.from_paper_id JOIN papers b ON b.id = r.to_paper_id WHERE a.status = 'published' AND b.status = 'published';
CREATE VIEW public_settings AS SELECT key, value FROM settings WHERE key NOT LIKE 'private.%';
CREATE VIEW public_files AS SELECT f.id, f.kind, f.storage_key, f.original_name, f.mime, f.bytes, f.sha256, f.pages, f.integrity_ok FROM files f WHERE EXISTS (SELECT 1 FROM paper_versions v JOIN papers p ON p.id = v.paper_id WHERE v.file_id = f.id AND p.status IN ('published', 'withdrawn', 'superseded') AND NOT v.withdrawn) OR f.kind = 'preview';

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END $$;
CREATE TRIGGER papers_touch BEFORE UPDATE ON papers FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
CREATE TRIGGER authors_touch BEFORE UPDATE ON authors FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
CREATE TRIGGER ingest_touch BEFORE UPDATE ON ingest_candidates FOR EACH ROW EXECUTE FUNCTION touch_updated_at();