-- Trouve-KA — migration 001 : schéma initial
-- Author: Simon-Pierre Boucher
-- Contact: contact@spboucher.ai

BEGIN;

CREATE TABLE IF NOT EXISTS schema_migrations (
    version     INTEGER PRIMARY KEY,
    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Domaines connus du web québécois (et non québécois, pour mémoire)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS domains (
    id                  BIGSERIAL PRIMARY KEY,
    domain              TEXT NOT NULL UNIQUE,
    first_seen          TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_crawled_at     TIMESTAMPTZ,
    robots_fetched_at   TIMESTAMPTZ,
    robots_body         TEXT,
    robots_status       TEXT,            -- ok | not_found | error | forbidden
    crawl_delay_ms      INTEGER,         -- délai imposé par robots.txt (Crawl-delay)
    quebec_score        REAL NOT NULL DEFAULT 0,
    authority_score     REAL NOT NULL DEFAULT 0,
    page_count          INTEGER NOT NULL DEFAULT 0,
    inlink_domains      INTEGER NOT NULL DEFAULT 0,
    outlink_domains     INTEGER NOT NULL DEFAULT 0,
    language_stats      JSONB NOT NULL DEFAULT '{}'::jsonb,
    content_change_rate REAL,
    blocked             BOOLEAN NOT NULL DEFAULT FALSE,
    is_seed             BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE INDEX IF NOT EXISTS idx_domains_quebec ON domains (quebec_score DESC);

-- ---------------------------------------------------------------------------
-- URLs canoniques connues
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS urls (
    id              BIGSERIAL PRIMARY KEY,
    url             TEXT NOT NULL UNIQUE,
    canonical_url   TEXT,
    domain_id       BIGINT NOT NULL REFERENCES domains(id),
    first_seen      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_urls_domain ON urls (domain_id);

-- ---------------------------------------------------------------------------
-- Frontier : la file d'URLs à crawler, avec priorités et scheduling
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS frontier_items (
    id              BIGSERIAL PRIMARY KEY,
    url_id          BIGINT NOT NULL UNIQUE REFERENCES urls(id),
    priority        REAL NOT NULL DEFAULT 0.5,
    depth           INTEGER NOT NULL DEFAULT 0,
    source_url_id   BIGINT REFERENCES urls(id),
    discovered_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_crawled_at TIMESTAMPTZ,
    next_crawl_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    status          TEXT NOT NULL DEFAULT 'pending',  -- pending|in_progress|done|failed|blocked
    retries         INTEGER NOT NULL DEFAULT 0,
    error_code      TEXT,
    locked_by       TEXT,
    locked_at       TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_frontier_ready
    ON frontier_items (next_crawl_at, priority DESC)
    WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_frontier_status ON frontier_items (status);

-- ---------------------------------------------------------------------------
-- Historique des tentatives de crawl (l'échec est normal; il est traqué)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS crawl_attempts (
    id            BIGSERIAL PRIMARY KEY,
    url_id        BIGINT NOT NULL REFERENCES urls(id),
    fetched_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    status_code   INTEGER,
    error_code    TEXT,          -- dns|timeout|tls|http_4xx|http_5xx|robots_denied|parse_failed|
                                 -- unsupported_content|too_large|duplicate|spam|not_quebec|ssrf_blocked|ok
    outcome       TEXT NOT NULL, -- indexed|duplicate|error|not_quebec|robots_blocked|redirect|unchanged
    content_hash  TEXT,
    bytes         INTEGER,
    duration_ms   INTEGER,
    redirect_url  TEXT,
    title         TEXT,
    quebec_score  REAL
);
CREATE INDEX IF NOT EXISTS idx_attempts_time ON crawl_attempts (fetched_at DESC);
CREATE INDEX IF NOT EXISTS idx_attempts_url ON crawl_attempts (url_id);

-- ---------------------------------------------------------------------------
-- Documents indexés (métadonnées; le contenu cherchable vit dans l'index)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS documents (
    id                  BIGSERIAL PRIMARY KEY,
    url_id              BIGINT NOT NULL UNIQUE REFERENCES urls(id),
    content_hash        TEXT NOT NULL,
    etag                TEXT,
    last_modified       TEXT,
    title               TEXT,
    description         TEXT,
    language            TEXT,
    page_quebec_score   REAL NOT NULL DEFAULT 0,
    published_at        TIMESTAMPTZ,
    last_changed_at     TIMESTAMPTZ,
    first_indexed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_indexed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    enrichment_stage    INTEGER NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_documents_hash ON documents (content_hash);

-- ---------------------------------------------------------------------------
-- Graphe de liens agrégé au niveau domaine (page→page viendra plus tard)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS domain_links (
    from_domain_id  BIGINT NOT NULL REFERENCES domains(id),
    to_domain_id    BIGINT NOT NULL REFERENCES domains(id),
    link_count      INTEGER NOT NULL DEFAULT 1,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (from_domain_id, to_domain_id)
);
CREATE INDEX IF NOT EXISTS idx_domain_links_to ON domain_links (to_domain_id);

-- ---------------------------------------------------------------------------
-- Analytics de recherche agrégées et respectueuses de la vie privée
-- (les requêtes zéro-résultat sont de l'or : elles pilotent le crawl)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS search_queries (
    id            BIGSERIAL PRIMARY KEY,
    query         TEXT NOT NULL,
    language      TEXT,
    results_total INTEGER NOT NULL,
    took_ms       INTEGER NOT NULL,
    zero_result   BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_queries_zero ON search_queries (created_at) WHERE zero_result;

-- ---------------------------------------------------------------------------
-- Soumissions publiques d'URL (soumission ≠ inclusion)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS submissions (
    id           BIGSERIAL PRIMARY KEY,
    url          TEXT NOT NULL,
    submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    status       TEXT NOT NULL DEFAULT 'queued'  -- queued|accepted|rejected
);

INSERT INTO schema_migrations (version) VALUES (1) ON CONFLICT DO NOTHING;

COMMIT;