JvArchive schéma v3

-- ============================================================
-- JvArchive - PostgreSQL 16+
-- Schéma recommandé - version psql/\i compatible
-- ============================================================

\set ON_ERROR_STOP on

-- ------------------------------------------------------------
-- Réglages de session pour bulk load
-- ------------------------------------------------------------
SET client_encoding = 'UTF8';
SET DateStyle = 'ISO, YMD';
SET TimeZone = 'UTC';
SET synchronous_commit = OFF;
SET maintenance_work_mem = '4GB';
SET work_mem = '64MB';
SET temp_buffers = '256MB';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET max_parallel_maintenance_workers = 4;

-- ------------------------------------------------------------
-- Extensions
-- ------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- ------------------------------------------------------------
-- Reset
-- ------------------------------------------------------------
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS topics CASCADE;
DROP TABLE IF EXISTS auteurs CASCADE;

-- ------------------------------------------------------------
-- Tables métier
-- ------------------------------------------------------------
CREATE TABLE auteurs (
    id      bigint NOT NULL,
    pseudo  text   NOT NULL,
    avatar  text   NULL
);

CREATE TABLE topics (
    id                               bigint      NOT NULL,
    titre                            text        NOT NULL,
    nb_messages                      integer     NOT NULL,
    date_creation                    timestamptz NOT NULL,
    date_suppression                 timestamptz NULL,
    restaure                         boolean     NOT NULL DEFAULT false,
    delete_by_modo                   boolean     NOT NULL DEFAULT false,
    auteur_id                        bigint      NULL,
    date_dernier_message             timestamptz NOT NULL,
    date_dernier_message_enregistre  timestamptz NULL,
    nb_messages_enregistre           integer     NULL,
    CONSTRAINT topics_nb_messages_chk
        CHECK (nb_messages >= 0),
    CONSTRAINT topics_nb_messages_enregistre_chk
        CHECK (
            nb_messages_enregistre IS NULL
            OR nb_messages_enregistre >= 0
        )
);

CREATE TABLE messages (
    topic_id  bigint      NOT NULL,
    id        bigint      NOT NULL,
    auteur_id bigint      NULL,
    date_post timestamptz NOT NULL,
    texte     text        NOT NULL
) PARTITION BY HASH (topic_id);

-- ------------------------------------------------------------
-- Partitions HASH de messages
-- ------------------------------------------------------------
DO $$
DECLARE
    v_partitions integer := 32;
    i integer;
BEGIN
    FOR i IN 0..v_partitions - 1 LOOP
        EXECUTE format(
            'CREATE TABLE messages_p%s PARTITION OF messages
             FOR VALUES WITH (MODULUS %s, REMAINDER %s);',
            lpad(i::text, 2, '0'),
            v_partitions,
            i
        );
    END LOOP;
END
$$;

-- ------------------------------------------------------------
-- Import AUTEURS
-- ------------------------------------------------------------
\echo Import auteurs depuis auteurs.csv
\copy auteurs (id, pseudo, avatar) FROM 'auteurs.csv' WITH (FORMAT csv, HEADER true)

-- ------------------------------------------------------------
-- Import TOPICS via staging 100% texte
-- ------------------------------------------------------------
DROP TABLE IF EXISTS topics_stage;

CREATE TEMP TABLE topics_stage (
    id                               text,
    titre                            text,
    nb_messages                      text,
    date_creation                    text,
    date_suppression                 text,
    restaure                         text,
    delete_by_modo                   text,
    auteur_id                        text,
    date_dernier_message             text,
    date_dernier_message_enregistre  text,
    nb_messages_enregistre           text
);

\echo Import topics_stage depuis topics.csv
\copy topics_stage (id, titre, nb_messages, date_creation, date_suppression, restaure, delete_by_modo, auteur_id, date_dernier_message, date_dernier_message_enregistre, nb_messages_enregistre) FROM 'topics.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO topics (
    id,
    titre,
    nb_messages,
    date_creation,
    date_suppression,
    restaure,
    delete_by_modo,
    auteur_id,
    date_dernier_message,
    date_dernier_message_enregistre,
    nb_messages_enregistre
)
WITH normalized AS (
    SELECT
        NULLIF(btrim(id), '')::bigint AS id,
        titre,
        COALESCE(NULLIF(btrim(nb_messages), '')::integer, 0) AS nb_messages,

        NULLIF(btrim(date_creation), '')::timestamptz AS date_creation_ts,
        NULLIF(btrim(date_suppression), '')::timestamptz AS date_suppression_ts,
        NULLIF(btrim(date_dernier_message), '')::timestamptz AS date_dernier_message_ts,
        NULLIF(btrim(date_dernier_message_enregistre), '')::timestamptz AS date_dernier_message_enregistre_ts,

        CASE
            WHEN restaure IS NULL OR btrim(restaure) = '' THEN false
            WHEN lower(btrim(restaure)) IN ('true', 't', 'yes', 'y', 'on', '1') THEN true
            WHEN lower(btrim(restaure)) IN ('false', 'f', 'no', 'n', 'off', '0') THEN false
            WHEN btrim(restaure) ~ '^[0-9]+$' THEN (btrim(restaure)::integer <> 0)
            ELSE false
        END AS restaure_bool,

        CASE
            WHEN delete_by_modo IS NULL OR btrim(delete_by_modo) = '' THEN false
            WHEN lower(btrim(delete_by_modo)) IN ('true', 't', 'yes', 'y', 'on', '1') THEN true
            WHEN lower(btrim(delete_by_modo)) IN ('false', 'f', 'no', 'n', 'off', '0') THEN false
            WHEN btrim(delete_by_modo) ~ '^[0-9]+$' THEN (btrim(delete_by_modo)::integer <> 0)
            ELSE false
        END AS delete_by_modo_bool,

        NULLIF(btrim(auteur_id), '')::bigint AS auteur_id,
        NULLIF(btrim(nb_messages_enregistre), '')::integer AS nb_messages_enregistre
    FROM topics_stage
)
SELECT
    id,
    titre,
    nb_messages,
    COALESCE(
        date_creation_ts,
        date_dernier_message_ts,
        date_dernier_message_enregistre_ts,
        date_suppression_ts
    ) AS date_creation,
    date_suppression_ts,
    restaure_bool,
    delete_by_modo_bool,
    auteur_id,
    COALESCE(
        date_dernier_message_ts,
        date_dernier_message_enregistre_ts,
        date_creation_ts,
        date_suppression_ts
    ) AS date_dernier_message,
    date_dernier_message_enregistre_ts,
    nb_messages_enregistre
FROM normalized
WHERE id IS NOT NULL
  AND titre IS NOT NULL
  AND COALESCE(
        date_creation_ts,
        date_dernier_message_ts,
        date_dernier_message_enregistre_ts,
        date_suppression_ts
      ) IS NOT NULL
  AND COALESCE(
        date_dernier_message_ts,
        date_dernier_message_enregistre_ts,
        date_creation_ts,
        date_suppression_ts
      ) IS NOT NULL;

DROP TABLE topics_stage;

-- ------------------------------------------------------------
-- Import MESSAGES directement dans le parent partitionné
-- texte vide => '' au lieu de NULL
-- ------------------------------------------------------------
\echo Import messages depuis messages.csv
\copy messages (id, date_post, texte, topic_id, auteur_id) FROM 'messages.csv' WITH (FORMAT csv, HEADER true, FORCE_NOT_NULL (texte))

-- ------------------------------------------------------------
-- PK / contraintes d’unicité après chargement
-- ------------------------------------------------------------
ALTER TABLE auteurs
    ADD CONSTRAINT auteurs_pk PRIMARY KEY (id);

ALTER TABLE topics
    ADD CONSTRAINT topics_pk PRIMARY KEY (id);

ALTER TABLE messages
    ADD CONSTRAINT messages_pk PRIMARY KEY (topic_id, id);

-- ------------------------------------------------------------
-- Index secondaires
-- ------------------------------------------------------------
CREATE INDEX idx_auteurs_pseudo_trgm
    ON auteurs
    USING GIN ((lower(pseudo)) gin_trgm_ops);

CREATE INDEX idx_topics_last_active
    ON topics (date_dernier_message DESC, id DESC)
    WHERE date_suppression IS NULL;

CREATE INDEX idx_topics_auteur_last_active
    ON topics (auteur_id, date_dernier_message DESC, id DESC)
    WHERE auteur_id IS NOT NULL
      AND date_suppression IS NULL;

CREATE INDEX idx_topics_date_creation
    ON topics (date_creation DESC, id DESC);

CREATE INDEX idx_topics_titre_trgm
    ON topics
    USING GIN ((lower(titre)) gin_trgm_ops)
    WHERE date_suppression IS NULL;

CREATE INDEX idx_messages_topic_date
    ON messages (topic_id, date_post, id);

CREATE INDEX idx_messages_auteur_date
    ON messages (auteur_id, date_post DESC, id DESC)
    WHERE auteur_id IS NOT NULL;

-- ------------------------------------------------------------
-- Stats
-- ------------------------------------------------------------
ANALYZE auteurs;
ANALYZE topics;
ANALYZE messages;