-- SQLite schema for the Email Campaign Manager
-- Database file defaults to api/data/app.sqlite (override with SQLITE_PATH)

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS campaigns (
  campaign_id TEXT PRIMARY KEY,
  name TEXT,
  opened_count INTEGER NOT NULL DEFAULT 0,
  clicked_count INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campaigns_data (
  id TEXT PRIMARY KEY,
  campaign_id TEXT,
  user_id TEXT,
  name TEXT NOT NULL,
  subject TEXT NOT NULL,
  html_body TEXT NOT NULL,
  text_body TEXT,
  from_email TEXT NOT NULL,
  from_name TEXT,
  reply_to TEXT,
  list_id TEXT NOT NULL,
  smtp_server_ids TEXT NOT NULL,
  tracking_enabled INTEGER NOT NULL DEFAULT 1,
  subject_rotation_enabled INTEGER NOT NULL DEFAULT 0,
  subject_rotation_interval INTEGER NOT NULL DEFAULT 100,
  subject_rotation_subjects_json TEXT,
  bulletproof_enabled INTEGER NOT NULL DEFAULT 0,
  schedule_enabled INTEGER NOT NULL DEFAULT 0,
  schedule_start_time TEXT,
  schedule_stop_time TEXT,
  schedule_days_json TEXT,
  schedule_batch_quantity INTEGER NOT NULL DEFAULT 100,
  schedule_timezone TEXT,
  schedule_last_run_at TEXT,
  schedule_next_run_at TEXT,
  stealth_enabled INTEGER NOT NULL DEFAULT 0,
  stealth_links_json TEXT,
  stealth_api_url TEXT,
  stealth_api_key TEXT,
  status TEXT DEFAULT 'draft',
  sent_count INTEGER NOT NULL DEFAULT 0,
  bounced_count INTEGER NOT NULL DEFAULT 0,
  opened_count INTEGER NOT NULL DEFAULT 0,
  clicked_count INTEGER NOT NULL DEFAULT 0,
  total_recipients INTEGER NOT NULL DEFAULT 0,
  pdf_attachments TEXT,
  custom_headers TEXT,
  embedded_images TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_campaigns_data_status ON campaigns_data(status);
CREATE INDEX IF NOT EXISTS idx_campaigns_data_user ON campaigns_data(user_id);
CREATE INDEX IF NOT EXISTS idx_campaigns_data_campaign_id ON campaigns_data(campaign_id);

CREATE TABLE IF NOT EXISTS email_tracking (
  tracking_id TEXT PRIMARY KEY,
  campaign_id TEXT,
  contact_id TEXT,
  email TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  bounce_reason TEXT,
  sent_at TEXT DEFAULT NULL,
  opened INTEGER NOT NULL DEFAULT 0,
  clicked INTEGER NOT NULL DEFAULT 0,
  opened_at TEXT DEFAULT NULL,
  clicked_at TEXT DEFAULT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_email_tracking_campaign ON email_tracking(campaign_id);
CREATE INDEX IF NOT EXISTS idx_email_tracking_email ON email_tracking(email);

CREATE TABLE IF NOT EXISTS click_tracking (
  click_id TEXT PRIMARY KEY,
  tracking_id TEXT NOT NULL,
  url TEXT NOT NULL,
  user_agent TEXT,
  ip_address TEXT,
  clicked_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tracking_id) REFERENCES email_tracking(tracking_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_click_tracking_tracking ON click_tracking(tracking_id);

CREATE TABLE IF NOT EXISTS smtp_servers (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  host TEXT NOT NULL,
  port INTEGER NOT NULL DEFAULT 587,
  username TEXT NOT NULL,
  password TEXT NOT NULL,
  secure INTEGER NOT NULL DEFAULT 1,
  from_email TEXT NOT NULL,
  from_name TEXT,
  hourly_limit INTEGER NOT NULL DEFAULT 5000,
  sent_this_hour INTEGER NOT NULL DEFAULT 0,
  last_reset_time INTEGER,
  is_active INTEGER NOT NULL DEFAULT 1,
  warmup_enabled INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS servers (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  host TEXT NOT NULL,
  port INTEGER NOT NULL DEFAULT 587,
  username TEXT NOT NULL,
  password TEXT NOT NULL,
  security TEXT NOT NULL DEFAULT 'tls',
  from_email TEXT NOT NULL,
  from_name TEXT,
  hourly_limit INTEGER NOT NULL DEFAULT 5000,
  sent_this_hour INTEGER NOT NULL DEFAULT 0,
  last_reset_time INTEGER,
  is_active INTEGER NOT NULL DEFAULT 1,
  warmup_enabled INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS contact_lists (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  total_contacts INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS contacts (
  id TEXT PRIMARY KEY,
  list_id TEXT NOT NULL,
  email TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,
  custom_fields TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TEXT DEFAULT NULL,
  FOREIGN KEY (list_id) REFERENCES contact_lists(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_contacts_list ON contacts(list_id);
CREATE INDEX IF NOT EXISTS idx_contacts_list_email ON contacts(list_id, email);
CREATE INDEX IF NOT EXISTS idx_contacts_list_created ON contacts(list_id, created_at);

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT,
  subscription_plan TEXT,
  status TEXT DEFAULT 'active',
  credits INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE TABLE IF NOT EXISTS subscription_plans (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  credits INTEGER NOT NULL DEFAULT 0,
  features TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS smtp_rate_state (
  server_id TEXT PRIMARY KEY,
  window_start INTEGER NOT NULL DEFAULT 0,
  sent_this_window INTEGER NOT NULL DEFAULT 0,
  last_sent_ts INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campaign_settings (
  campaign_id TEXT PRIMARY KEY,
  tracking_enabled INTEGER NOT NULL DEFAULT 1,
  subject_rotation_enabled INTEGER NOT NULL DEFAULT 0,
  subject_rotation_interval INTEGER NOT NULL DEFAULT 100,
  subject_rotation_subjects_json TEXT,
  bulletproof_enabled INTEGER NOT NULL DEFAULT 0,
  schedule_enabled INTEGER NOT NULL DEFAULT 0,
  schedule_start_time TEXT,
  schedule_stop_time TEXT,
  schedule_days_json TEXT,
  schedule_batch_quantity INTEGER NOT NULL DEFAULT 100,
  schedule_timezone TEXT,
  schedule_last_run_at TEXT,
  schedule_next_run_at TEXT,
  stealth_enabled INTEGER NOT NULL DEFAULT 0,
  stealth_links_json TEXT DEFAULT NULL,
  stealth_api_url TEXT DEFAULT NULL,
  stealth_api_key TEXT DEFAULT NULL,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE IF NOT EXISTS live_chat_sessions (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  last_message_at INTEGER DEFAULT NULL
);
CREATE INDEX IF NOT EXISTS idx_live_chat_sessions_status ON live_chat_sessions(status);
CREATE INDEX IF NOT EXISTS idx_live_chat_sessions_created ON live_chat_sessions(created_at);

CREATE TABLE IF NOT EXISTS live_chat_messages (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  message TEXT NOT NULL,
  sender_type TEXT NOT NULL CHECK(sender_type IN ('user', 'agent')),
  sender_name TEXT DEFAULT NULL,
  file_id TEXT DEFAULT NULL,
  file_url TEXT DEFAULT NULL,
  file_name TEXT DEFAULT NULL,
  telegram_message_id TEXT DEFAULT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES live_chat_sessions(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_live_chat_messages_session ON live_chat_messages(session_id);
CREATE INDEX IF NOT EXISTS idx_live_chat_messages_created ON live_chat_messages(created_at);

CREATE TABLE IF NOT EXISTS live_chat_files (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  original_name TEXT NOT NULL,
  stored_name TEXT NOT NULL,
  mime_type TEXT DEFAULT 'application/octet-stream',
  size_bytes INTEGER NOT NULL DEFAULT 0,
  storage_path TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES live_chat_sessions(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_live_chat_files_session ON live_chat_files(session_id);

CREATE TABLE IF NOT EXISTS live_chat_settings (
  id TEXT PRIMARY KEY DEFAULT 'main',
  telegram_bot_token TEXT DEFAULT NULL,
  telegram_chat_id TEXT DEFAULT NULL,
  updated_at INTEGER DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS email_templates (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  difficulty TEXT NOT NULL DEFAULT '',
  icon_key TEXT NOT NULL DEFAULT 'bank',
  description TEXT DEFAULT NULL,
  techniques TEXT DEFAULT NULL,
  from_address TEXT DEFAULT NULL,
  subject TEXT DEFAULT NULL,
  html_body TEXT DEFAULT NULL,
  custom_headers TEXT DEFAULT NULL,
  detection_score REAL DEFAULT NULL,
  detection_risk TEXT DEFAULT NULL,
  detection_findings TEXT DEFAULT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);