CREATE TABLE IF NOT EXISTS reddit_communities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subreddit VARCHAR(100) NOT NULL UNIQUE,
  label VARCHAR(180) NOT NULL,
  language ENUM('en','fr','mixed') NOT NULL DEFAULT 'en',
  status ENUM('candidate','watch','excluded') NOT NULL DEFAULT 'candidate',
  priority_score TINYINT UNSIGNED NOT NULL DEFAULT 50,
  projects_json JSON NOT NULL,
  rationale TEXT NULL,
  last_collected_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX(status), INDEX(priority_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reddit_observations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reddit_community_id BIGINT UNSIGNED NOT NULL,
  reddit_id VARCHAR(30) NOT NULL,
  post_url TEXT NOT NULL,
  title TEXT NOT NULL,
  title_fr TEXT NULL,
  excerpt MEDIUMTEXT NULL,
  score INT NOT NULL DEFAULT 0,
  comments_count INT UNSIGNED NOT NULL DEFAULT 0,
  engagement_score DECIMAL(8,2) NOT NULL DEFAULT 0,
  opportunity_type VARCHAR(60) NULL,
  projects_json JSON NOT NULL,
  vera_summary TEXT NULL,
  values_status ENUM('unknown','aligned','caution','reject') NOT NULL DEFAULT 'unknown',
  published_at DATETIME NULL,
  collected_at DATETIME NOT NULL,
  UNIQUE KEY community_post(reddit_community_id,reddit_id),
  INDEX(engagement_score), INDEX(published_at),
  FOREIGN KEY(reddit_community_id) REFERENCES reddit_communities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO reddit_communities (subreddit,label,language,status,priority_score,projects_json,rationale,created_at,updated_at) VALUES
('spirituality','Spirituality','en','candidate',72,'["Maison des Esprits","Grande Bibliothèque"]','Tendances internationales, questions et débats généraux sur la spiritualité.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('Mediums','Mediums','en','candidate',82,'["Maison des Esprits","Grande Bibliothèque"]','Témoignages et questions directement liés à la médiumnité.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('Paranormal','Paranormal','en','candidate',76,'["Maison des Esprits","Grande Bibliothèque"]','Expériences vécues, récits et débats autour du paranormal.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('NDE','Near Death Experiences','en','candidate',78,'["Maison des Esprits","Grande Bibliothèque"]','Témoignages d’expériences de mort imminente et discussions associées.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('AstralProjection','Astral Projection','en','candidate',70,'["Maison des Esprits","Grande Bibliothèque"]','Expériences, pratiques et questions sur les sorties hors du corps.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('Meditation','Meditation','en','candidate',58,'["Maison des Esprits","Événements"]','Tendances de pratique, difficultés concrètes et formats de transmission.',UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('occult','Occult','en','candidate',52,'["Grande Bibliothèque"]','Sources historiques et culturelles à examiner avec un fort niveau de discernement.',UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE label=VALUES(label),priority_score=VALUES(priority_score),projects_json=VALUES(projects_json),rationale=VALUES(rationale),updated_at=UTC_TIMESTAMP();
