Schéma JvArchive

-- =====================================================
-- IMPORT JVA FINAL SAFE V2
-- =====================================================

-- CONFIG IMPORT
SET client_encoding = 'UTF8';
SET synchronous_commit = OFF;
SET maintenance_work_mem = '4GB';
SET work_mem = '64MB';

-- =====================================================
-- DROP TABLES EXISTANTES (optionnel si réimport)
-- =====================================================
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS topics CASCADE;
DROP TABLE IF EXISTS auteurs CASCADE;

-- =====================================================
-- TABLE AUTEURS
-- =====================================================
CREATE TABLE auteurs (
  id BIGINT PRIMARY KEY,
  pseudo VARCHAR(50),
  avatar VARCHAR(255)
);

-- =====================================================
-- TABLE TOPICS (restaure / delete_by_modo = TEXT pour import)
-- =====================================================
CREATE TABLE topics (
  id BIGINT PRIMARY KEY,
  titre VARCHAR(255),

  nb_messages INTEGER,
  date_creation TIMESTAMPTZ,
  date_suppression TIMESTAMPTZ,

  restaure TEXT,
  delete_by_modo TEXT,

  auteur_id BIGINT,

  date_dernier_message TIMESTAMPTZ,
  date_dernier_message_enregistre TIMESTAMPTZ,
  nb_messages_enregistre INTEGER
);

-- =====================================================
-- TABLE MESSAGES (partitionnée)
-- =====================================================
CREATE TABLE messages (
  id BIGINT,
  date_post TIMESTAMPTZ,
  texte TEXT,
  topic_id BIGINT,
  auteur_id BIGINT
) PARTITION BY RANGE (date_post);

-- =====================================================
-- CREATION DES PARTITIONS MESSAGES (2004 → 2026)
-- =====================================================
DO $$
DECLARE
  y INTEGER;
BEGIN
  FOR y IN 2004..2026 LOOP
    EXECUTE format(
      'CREATE TABLE messages_%s PARTITION OF messages
       FOR VALUES FROM (%L) TO (%L);',
      y,
      y || '-01-01',
      (y + 1) || '-01-01'
    );
  END LOOP;
END$$;

-- Partition de secours (messages hors plage)
CREATE TABLE messages_default PARTITION OF messages DEFAULT;

-- =====================================================
-- IMPORT CSV
-- =====================================================

-- AUTEURS
COPY auteurs (id, pseudo, avatar)
FROM 'auteurs.csv'
WITH (FORMAT csv, HEADER true);

-- TOPICS
COPY 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
)
FROM 'topics.csv'
WITH (FORMAT csv, HEADER true);

-- MESSAGES
COPY messages (id, date_post, texte, topic_id, auteur_id)
FROM 'messages.csv'
WITH (FORMAT csv, HEADER true);

-- =====================================================
-- POST-TRAITEMENT : conversion TEXT → BOOLEAN
-- =====================================================
UPDATE topics SET
  restaure = CASE WHEN restaure IN ('1','t','true') THEN 'true'
                  ELSE 'false' END,
  delete_by_modo = CASE WHEN delete_by_modo IN ('1','t','true') THEN 'true'
                        ELSE 'false' END;

ALTER TABLE topics
  ALTER COLUMN restaure TYPE BOOLEAN USING restaure::BOOLEAN;

ALTER TABLE topics
  ALTER COLUMN delete_by_modo TYPE BOOLEAN USING delete_by_modo::BOOLEAN;

-- =====================================================
-- INDEX (APRÈS IMPORT)
-- =====================================================

-- auteurs
CREATE INDEX idx_auteurs_pseudo ON auteurs(pseudo);

-- topics
CREATE INDEX idx_topics_last_msg
ON topics (date_dernier_message DESC);

CREATE INDEX idx_topics_auteur
ON topics (auteur_id);

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

CREATE INDEX idx_messages_auteur_date
ON messages (auteur_id, date_post);

-- =====================================================
-- ANALYZE
-- =====================================================
ANALYZE auteurs;
ANALYZE topics;
ANALYZE messages;

-- Activation de pg_trgm (recherches par message)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Créer un index GIN sur le texte
CREATE INDEX idx_messages_texte_trgm
ON messages USING GIN (texte gin_trgm_ops);


-- =====================================================
-- FIN
-- =====================================================