-- Organizations Table
CREATE TABLE IF NOT EXISTS organizations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Users Table
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  is_active INTEGER DEFAULT 1,
  current_org_id TEXT,
  profile_image TEXT,
  last_login DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (current_org_id) REFERENCES organizations(org_id)
);

-- Organization Members (Many-to-Many relationship)
CREATE TABLE IF NOT EXISTS organization_members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member',
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(org_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  UNIQUE(org_id, user_id)
);

-- Upload Jobs
CREATE TABLE IF NOT EXISTS upload_jobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  job_id TEXT UNIQUE NOT NULL,
  org_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  job_name TEXT,
  account_id TEXT NOT NULL,
  account_name TEXT,
  campaign_id TEXT,
  campaign_name TEXT,
  adset_id TEXT,
  adset_name TEXT,
  page_id TEXT,
  page_name TEXT,
  new_adset_id TEXT,
  new_adset_name TEXT,
  brand TEXT,
  target_campaign_id TEXT,
  target_campaign_name TEXT,
  template_ad_id TEXT,
  template_ad_name TEXT,
  current_step INTEGER DEFAULT 1,
  status TEXT NOT NULL,
  total_files INTEGER DEFAULT 0,
  successful_uploads INTEGER DEFAULT 0,
  failed_uploads INTEGER DEFAULT 0,
  error_message TEXT,
  started_at DATETIME,
  completed_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(org_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Upload Details
CREATE TABLE IF NOT EXISTS upload_details (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  job_id TEXT NOT NULL,
  file_name TEXT NOT NULL,
  file_type TEXT NOT NULL,
  file_size INTEGER,
  ad_id TEXT,
  ad_name TEXT,
  creative_id TEXT,
  status TEXT NOT NULL,
  error_message TEXT,
  thumbnail_generated INTEGER DEFAULT 0,
  upload_started_at DATETIME,
  upload_completed_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES upload_jobs(job_id)
);

-- AM Dashboards Saved Reports
CREATE TABLE IF NOT EXISTS am_saved_reports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  org_id TEXT NOT NULL,
  name TEXT NOT NULL,
  account_ids TEXT NOT NULL,
  account_configurations TEXT NOT NULL,
  auto_refresh_enabled INTEGER DEFAULT 1,
  last_refresh_attempt DATETIME,
  last_successful_refresh DATETIME,
  refresh_status TEXT DEFAULT 'pending',
  refresh_retry_count INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (org_id) REFERENCES organizations(org_id)
);

-- AM Dashboards Cache
CREATE TABLE IF NOT EXISTS am_dashboard_cache (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  account_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  org_id TEXT NOT NULL,
  cache_key TEXT NOT NULL,
  data TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (org_id) REFERENCES organizations(org_id)
);

-- Creative Reports Saved Reports
CREATE TABLE IF NOT EXISTS creative_saved_reports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  org_id TEXT NOT NULL,
  name TEXT NOT NULL,
  account_id TEXT NOT NULL,
  account_name TEXT NOT NULL,
  primary_kpi TEXT NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_refresh_attempt DATETIME,
  last_successful_refresh DATETIME,
  refresh_status TEXT DEFAULT 'pending',
  refresh_retry_count INTEGER DEFAULT 0,
  auto_refresh_enabled INTEGER DEFAULT 1,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (org_id) REFERENCES organizations(org_id)
);

-- Creative Reports Cache
CREATE TABLE IF NOT EXISTS creative_reports_cache (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  account_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  org_id TEXT NOT NULL,
  cache_key TEXT NOT NULL,
  data TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (org_id) REFERENCES organizations(org_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_orgs_slug ON organizations(slug);
CREATE INDEX IF NOT EXISTS idx_org_members_org ON organization_members(org_id);
CREATE INDEX IF NOT EXISTS idx_org_members_user ON organization_members(user_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_current_org ON users(current_org_id);
CREATE INDEX IF NOT EXISTS idx_jobs_org ON upload_jobs(org_id);
CREATE INDEX IF NOT EXISTS idx_jobs_user ON upload_jobs(user_id);
CREATE INDEX IF NOT EXISTS idx_jobs_status ON upload_jobs(status);
CREATE INDEX IF NOT EXISTS idx_jobs_date ON upload_jobs(created_at);
CREATE INDEX IF NOT EXISTS idx_details_job ON upload_details(job_id);
CREATE INDEX IF NOT EXISTS idx_am_reports_user ON am_saved_reports(user_id);
CREATE INDEX IF NOT EXISTS idx_am_reports_org ON am_saved_reports(org_id);
CREATE INDEX IF NOT EXISTS idx_am_reports_status ON am_saved_reports(refresh_status);
CREATE INDEX IF NOT EXISTS idx_am_cache_user ON am_dashboard_cache(user_id);
CREATE INDEX IF NOT EXISTS idx_am_cache_account ON am_dashboard_cache(account_id);
CREATE INDEX IF NOT EXISTS idx_am_cache_key ON am_dashboard_cache(cache_key);
CREATE INDEX IF NOT EXISTS idx_creative_reports_user ON creative_saved_reports(user_id);
CREATE INDEX IF NOT EXISTS idx_creative_reports_org ON creative_saved_reports(org_id);
CREATE INDEX IF NOT EXISTS idx_creative_reports_account ON creative_saved_reports(account_id);
CREATE INDEX IF NOT EXISTS idx_creative_reports_refresh ON creative_saved_reports(auto_refresh_enabled, last_successful_refresh);
CREATE INDEX IF NOT EXISTS idx_creative_cache_user ON creative_reports_cache(user_id);
CREATE INDEX IF NOT EXISTS idx_creative_cache_account ON creative_reports_cache(account_id);
CREATE INDEX IF NOT EXISTS idx_creative_cache_key ON creative_reports_cache(cache_key);
