-- Restless Dreamers - Weekend Sessions App
-- SQLite 3 Database Schema & Seeds

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member',
  notification_preferences TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS profiles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL UNIQUE,
  interests TEXT NOT NULL,
  whatsapp TEXT,
  linkedin_url TEXT,
  weekly_minutes INT NOT NULL DEFAULT 60,
  reading_depth TEXT NOT NULL DEFAULT 'standard',
  headline_preferences TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  url TEXT NOT NULL,
  canonical_url TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  description TEXT,
  thumbnail_path TEXT,
  source TEXT,
  favicon TEXT,
  type TEXT NOT NULL DEFAULT 'article',
  tier TEXT NOT NULL DEFAULT 'easy',
  discipline TEXT NOT NULL DEFAULT 'culture',
  tags TEXT,
  time_estimate INT NOT NULL DEFAULT 5,
  why_note TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  health_status TEXT NOT NULL DEFAULT 'ok',
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS drops (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  week_slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  phenomenon TEXT NOT NULL,
  primer TEXT NOT NULL,
  session_time DATETIME NOT NULL,
  session_link TEXT,
  replay_link TEXT,
  recap_notes TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  is_paid INT NOT NULL DEFAULT 0,
  price_inr NUMERIC NOT NULL DEFAULT 0.00,
  capacity INT NOT NULL DEFAULT 30,
  sales_open_at DATETIME,
  sales_close_at DATETIME,
  published_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS drop_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  drop_id INT NOT NULL,
  item_id INT NOT NULL,
  lens TEXT NOT NULL,
  display_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (drop_id) REFERENCES drops(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS plans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL,
  drop_id INT NOT NULL,
  status TEXT NOT NULL DEFAULT 'generated',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, drop_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (drop_id) REFERENCES drops(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS plan_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  plan_id INT NOT NULL,
  item_id INT NOT NULL,
  tier TEXT NOT NULL,
  state TEXT NOT NULL DEFAULT 'not_started',
  opened_at DATETIME,
  completed_at DATETIME,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL,
  item_id INT NOT NULL,
  quote TEXT,
  text TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS reflections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL,
  drop_id INT NOT NULL,
  prompt TEXT NOT NULL DEFAULT 'What changed your mind this week?',
  text TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, drop_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (drop_id) REFERENCES drops(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rsvps (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL,
  drop_id INT NOT NULL,
  status TEXT NOT NULL DEFAULT 'attending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, drop_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (drop_id) REFERENCES drops(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS whatsapp_groups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT 'general',
  tags TEXT,
  is_active INT NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS nudge_templates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  channel TEXT NOT NULL DEFAULT 'push',
  subject TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS nudge_campaigns (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  template_id INT NOT NULL,
  segment TEXT NOT NULL DEFAULT 'all',
  status TEXT NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME,
  sent_at DATETIME,
  FOREIGN KEY (template_id) REFERENCES nudge_templates(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS nudge_sends (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  campaign_id INT,
  user_id INT NOT NULL,
  channel TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'sent',
  sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS promo_cards (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  cta_text TEXT NOT NULL DEFAULT 'Explore',
  cta_link TEXT NOT NULL,
  frequency_cap_days INT NOT NULL DEFAULT 14,
  is_active INT NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  event_type TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id TEXT NOT NULL UNIQUE,
  user_id INT NOT NULL,
  drop_id INT NOT NULL,
  order_amount NUMERIC NOT NULL,
  order_currency TEXT NOT NULL DEFAULT 'INR',
  order_status TEXT NOT NULL DEFAULT 'PENDING',
  payment_session_id TEXT,
  cf_payment_id TEXT,
  customer_phone TEXT NOT NULL,
  customer_email TEXT NOT NULL,
  customer_name TEXT NOT NULL,
  seat_number TEXT,
  refund_id TEXT,
  refund_amount NUMERIC,
  refund_status TEXT,
  payment_method TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (drop_id) REFERENCES drops(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS audit_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  action TEXT NOT NULL,
  actor_type TEXT NOT NULL DEFAULT 'user',
  actor_id INT,
  entity_type TEXT,
  entity_id TEXT,
  payload TEXT,
  ip_address TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS webhook_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id TEXT NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  payload TEXT NOT NULL,
  processed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);