CREATE DATABASE IF NOT EXISTS rescape_press_db;
USE rescape_press_db;

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS profiles (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  bio TEXT,
  phone TEXT,
  twitter_url TEXT,
  facebook_url TEXT,
  linkedin_url TEXT,
  instagram_url TEXT,
  website_url TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_roles (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  role ENUM('super_admin', 'editor_in_chief', 'editor', 'journalist', 'donation_manager', 'moderator') NOT NULL,
  UNIQUE KEY uq_user_role (user_id, role),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS categories (
  id CHAR(36) PRIMARY KEY,
  name TEXT NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  name_fr TEXT,
  description TEXT,
  parent_id CHAR(36),
  sort_order INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  article_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS regions (
  id CHAR(36) PRIMARY KEY,
  name TEXT NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  name_fr TEXT,
  sort_order INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  article_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS articles (
  id CHAR(36) PRIMARY KEY,
  title TEXT NOT NULL,
  subtitle TEXT,
  slug VARCHAR(255) NOT NULL UNIQUE,
  language VARCHAR(10) NOT NULL DEFAULT 'en',
  content LONGTEXT,
  excerpt TEXT,
  featured_image TEXT,
  featured_image_caption TEXT,
  featured_image_caption_fr TEXT,
  gallery JSON,
  gallery_captions JSON NOT NULL,
  video_embed TEXT,
  category_id CHAR(36),
  region_id CHAR(36),
  author_id CHAR(36),
  status ENUM('draft', 'pending', 'published', 'rejected', 'scheduled') NOT NULL DEFAULT 'draft',
  is_breaking BOOLEAN NOT NULL DEFAULT false,
  is_featured BOOLEAN NOT NULL DEFAULT false,
  is_editor_pick BOOLEAN NOT NULL DEFAULT false,
  is_trending BOOLEAN NOT NULL DEFAULT false,
  homepage_placement TEXT,
  seo_title TEXT,
  meta_description TEXT,
  social_image TEXT,
  tags JSON,
  focus_keyword TEXT,
  views_count INT NOT NULL DEFAULT 0,
  public_at DATETIME NULL,
  updated_at_manual DATETIME NULL,
  publish_date DATETIME NULL,
  scheduled_date DATETIME NULL,
  deleted_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_articles_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_articles_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL,
  CONSTRAINT fk_articles_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL,
  CHECK (JSON_VALID(gallery_captions))
);

CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_articles_category ON articles(category_id);
CREATE INDEX idx_articles_region ON articles(region_id);
CREATE INDEX idx_articles_author ON articles(author_id);
CREATE INDEX idx_articles_slug ON articles(slug);
CREATE INDEX idx_articles_publish_date ON articles(publish_date);
CREATE INDEX idx_articles_public_at ON articles(public_at);
CREATE INDEX idx_articles_deleted_at ON articles(deleted_at);

ALTER TABLE articles ADD COLUMN IF NOT EXISTS public_at DATETIME NULL;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS updated_at_manual DATETIME NULL;

CREATE TABLE IF NOT EXISTS breaking_news (
  id CHAR(36) PRIMARY KEY,
  headline TEXT NOT NULL,
  article_id CHAR(36),
  priority INT NOT NULL DEFAULT 1,
  start_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_time DATETIME NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_breaking_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS donations (
  id CHAR(36) PRIMARY KEY,
  donor_name TEXT NOT NULL,
  email VARCHAR(255),
  country VARCHAR(255),
  phone TEXT,
  address TEXT,
  message TEXT,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  payment_method TEXT,
  campaign TEXT,
  donation_type ENUM('one_time', 'recurring') NOT NULL DEFAULT 'one_time',
  status VARCHAR(255) NOT NULL DEFAULT 'completed',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_donation_currency_usd CHECK (currency = 'USD')
);

CREATE TABLE IF NOT EXISTS donation_settings (
  id CHAR(36) PRIMARY KEY,
  suggested_amounts JSON NOT NULL,
  one_time_suggested_amounts JSON,
  monthly_suggested_amounts JSON,
  annual_suggested_amounts JSON,
  min_custom_amount DECIMAL(12,2) NOT NULL DEFAULT 5,
  max_custom_amount DECIMAL(12,2) NOT NULL DEFAULT 100000,
  default_campaign TEXT,
  page_heading TEXT NOT NULL,
  page_heading_fr TEXT,
  page_description TEXT NOT NULL,
  page_description_fr TEXT,
  allow_recurring BOOLEAN NOT NULL DEFAULT true,
  one_time_enabled BOOLEAN NOT NULL DEFAULT true,
  monthly_enabled BOOLEAN NOT NULL DEFAULT true,
  annual_enabled BOOLEAN NOT NULL DEFAULT true,
  is_active BOOLEAN NOT NULL DEFAULT true,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by CHAR(36),
  CHECK (JSON_VALID(suggested_amounts)),
  CHECK (one_time_suggested_amounts IS NULL OR JSON_VALID(one_time_suggested_amounts)),
  CHECK (monthly_suggested_amounts IS NULL OR JSON_VALID(monthly_suggested_amounts)),
  CHECK (annual_suggested_amounts IS NULL OR JSON_VALID(annual_suggested_amounts))
);

ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS page_heading_fr TEXT;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS page_description_fr TEXT;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS one_time_suggested_amounts JSON;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS monthly_suggested_amounts JSON;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS annual_suggested_amounts JSON;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS one_time_enabled BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS monthly_enabled BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS annual_enabled BOOLEAN NOT NULL DEFAULT true;
UPDATE donation_settings
SET one_time_suggested_amounts = COALESCE(one_time_suggested_amounts, suggested_amounts),
    monthly_suggested_amounts = COALESCE(monthly_suggested_amounts, suggested_amounts),
    annual_suggested_amounts = COALESCE(annual_suggested_amounts, suggested_amounts);

CREATE TABLE IF NOT EXISTS subscribers (
  id CHAR(36) PRIMARY KEY,
  name TEXT NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone TEXT,
  country TEXT,
  plan ENUM('free_reader', 'supporter', 'impact_member') NOT NULL DEFAULT 'free_reader',
  start_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  renewal_date DATETIME NULL,
  status VARCHAR(100) NOT NULL DEFAULT 'active',
  payment_method TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comments (
  id CHAR(36) PRIMARY KEY,
  article_id CHAR(36) NOT NULL,
  commenter_name TEXT NOT NULL,
  email VARCHAR(255),
  content TEXT NOT NULL,
  status VARCHAR(100) NOT NULL DEFAULT 'pending',
  report_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comments_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS media (
  id CHAR(36) PRIMARY KEY,
  filename TEXT NOT NULL,
  url TEXT NOT NULL,
  file_type TEXT NOT NULL,
  size_bytes BIGINT,
  uploaded_by CHAR(36),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_media_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS recommended_articles (
  id CHAR(36) PRIMARY KEY,
  article_id CHAR(36) NOT NULL,
  recommended_article_id CHAR(36) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_recommended_pair (article_id, recommended_article_id),
  CONSTRAINT fk_recommended_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  CONSTRAINT fk_recommended_target FOREIGN KEY (recommended_article_id) REFERENCES articles(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ads (
  id CHAR(36) PRIMARY KEY,
  title TEXT NOT NULL,
  placement ENUM('header', 'sidebar_home', 'sidebar_post', 'bottom', 'in_article') NOT NULL,
  format ENUM('image', 'gif', 'youtube', 'vimeo') NOT NULL,
  media_url TEXT NOT NULL,
  redirect_url TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  sort_order INT NOT NULL DEFAULT 0,
  start_date DATETIME NULL,
  end_date DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ad_events (
  id CHAR(36) PRIMARY KEY,
  ad_id CHAR(36) NOT NULL,
  event_type ENUM('impression', 'click') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ad_events_ad FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
);

CREATE INDEX idx_ad_events_ad_id ON ad_events(ad_id);
CREATE INDEX idx_ad_events_type ON ad_events(event_type);
CREATE INDEX idx_ad_events_created ON ad_events(created_at);

CREATE TABLE IF NOT EXISTS site_pages (
  id CHAR(36) PRIMARY KEY,
  slug VARCHAR(255) NOT NULL UNIQUE,
  title TEXT NOT NULL,
  subtitle TEXT,
  last_updated TEXT,
  content LONGTEXT NOT NULL,
  title_fr TEXT,
  subtitle_fr TEXT,
  last_updated_fr TEXT,
  content_fr LONGTEXT,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by CHAR(36),
  is_active BOOLEAN NOT NULL DEFAULT true
);

ALTER TABLE site_pages ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT true;

CREATE TABLE IF NOT EXISTS site_page_versions (
  id CHAR(36) PRIMARY KEY,
  page_slug VARCHAR(255) NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT,
  last_updated TEXT,
  content LONGTEXT NOT NULL,
  title_fr TEXT,
  subtitle_fr TEXT,
  last_updated_fr TEXT,
  content_fr LONGTEXT,
  edited_by CHAR(36),
  editor_name TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_site_page_versions_slug_created ON site_page_versions(page_slug, created_at);

CREATE TABLE IF NOT EXISTS site_settings (
  id CHAR(36) PRIMARY KEY,
  site_name TEXT NOT NULL,
  tagline TEXT,
  logo_url TEXT,
  logo_white_url TEXT,
  favicon_url TEXT,
  contact_email TEXT,
  contact_phone TEXT,
  contact_address TEXT,
  facebook_url TEXT,
  twitter_url TEXT,
  linkedin_url TEXT,
  youtube_url TEXT,
  instagram_url TEXT,
  footer_text TEXT,
  default_language VARCHAR(10) NOT NULL DEFAULT 'en',
  updated_by CHAR(36),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO donation_settings (
  id, suggested_amounts, one_time_suggested_amounts, monthly_suggested_amounts, annual_suggested_amounts, min_custom_amount, max_custom_amount, page_heading, page_heading_fr, page_description, page_description_fr, allow_recurring, one_time_enabled, monthly_enabled, annual_enabled, is_active
)
SELECT
  UUID(), JSON_ARRAY(25, 50, 100, 250, 500), JSON_ARRAY(5, 10, 25, 50), JSON_ARRAY(2, 5, 10, 20), JSON_ARRAY(25, 50, 100, 250), 5, 100000,
  'Support independent journalism', 'Soutenir un journalisme independant',
  'Your contribution funds reporters on the ground in the Eastern DRC. Every dollar keeps Kivu Post independent.',
  'Votre contribution finance des journalistes sur le terrain dans l''est de la RDC. Chaque dollar maintient Kivu Post independant.',
  true, true, true, true, true
WHERE NOT EXISTS (SELECT 1 FROM donation_settings);

INSERT INTO site_settings (id, site_name, tagline)
SELECT UUID(), 'KivuPost', 'Truth. People. Stories that Matter.'
WHERE NOT EXISTS (SELECT 1 FROM site_settings);