CREATE TABLE IF NOT EXISTS echo_profiles (
  id TINYINT UNSIGNED PRIMARY KEY,
  follower_goal INT UNSIGNED NOT NULL DEFAULT 30000,
  current_followers INT UNSIGNED NOT NULL DEFAULT 0,
  default_tone ENUM('frisson','courant_air','poltergeist') NOT NULL DEFAULT 'courant_air',
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS echo_months (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL UNIQUE,
  monthly_goal SMALLINT UNSIGNED NOT NULL DEFAULT 16,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS echo_ideas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  echo_month_id BIGINT UNSIGNED NOT NULL,
  source_kind ENUM('series','vera','manual') NOT NULL,
  source_key VARCHAR(160) NULL,
  title VARCHAR(255) NOT NULL,
  premise TEXT NOT NULL,
  series_name VARCHAR(120) NOT NULL DEFAULT '',
  tone ENUM('frisson','courant_air','poltergeist') NOT NULL DEFAULT 'courant_air',
  format_slug VARCHAR(40) NOT NULL DEFAULT 'post',
  channel_slug VARCHAR(40) NOT NULL DEFAULT 'instagram',
  status ENUM('idea','selected','planned','drafted','reviewed','published','discarded') NOT NULL DEFAULT 'idea',
  due_date DATE NULL,
  vera_warning TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY echo_month_source(echo_month_id,source_kind,source_key),
  INDEX echo_calendar(echo_month_id,status,due_date),
  FOREIGN KEY(echo_month_id) REFERENCES echo_months(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
