CREATE TABLE IF NOT EXISTS projects (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  domain_url TEXT NOT NULL,
  hostname TEXT NOT NULL,
  slug TEXT NOT NULL,
  template_key TEXT NOT NULL DEFAULT 'clean-saas',
  amp_template_key TEXT NOT NULL DEFAULT '__random_amp__',
  seo_title TEXT NOT NULL,
  meta_description TEXT NOT NULL DEFAULT '',
  keywords TEXT NOT NULL DEFAULT '',
  hero_heading TEXT NOT NULL DEFAULT '',
  hero_subheading TEXT NOT NULL DEFAULT '',
  article_body TEXT NOT NULL DEFAULT '',
  cta_label TEXT NOT NULL DEFAULT 'Mulai Sekarang',
  cta_url TEXT NOT NULL DEFAULT '',
  use_ai_content INTEGER NOT NULL DEFAULT 0,
  ai_content_mode TEXT NOT NULL DEFAULT 'rewrite',
  ai_instructions TEXT NOT NULL DEFAULT '',
  ai_content_json TEXT NOT NULL DEFAULT '{}',
  ai_generated_at TEXT,
  amp_url TEXT NOT NULL DEFAULT '',
  alternate_links TEXT NOT NULL DEFAULT '',
  generate_faq INTEGER NOT NULL DEFAULT 1,
  generate_reviews INTEGER NOT NULL DEFAULT 1,
  audit_before_save INTEGER NOT NULL DEFAULT 1,
  logo_media_id INTEGER,
  icon_media_id INTEGER,
  main_media_id INTEGER,
  title_included INTEGER NOT NULL DEFAULT 1,
  generated_path TEXT,
  generated_urls TEXT NOT NULL DEFAULT '[]',
  last_template_key TEXT NOT NULL DEFAULT '',
  last_amp_template_key TEXT NOT NULL DEFAULT '',
  publication_status TEXT NOT NULL DEFAULT 'draft',
  finishing_report TEXT NOT NULL DEFAULT '{}',
  finishing_checked_at TEXT,
  approved_at TEXT,
  last_generated_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, domain_url),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS generation_logs (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  action TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_connections (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  provider TEXT NOT NULL DEFAULT 'openai',
  api_base_url TEXT NOT NULL DEFAULT 'https://api.openai.com/v1',
  model TEXT NOT NULL DEFAULT 'gpt-5.4-mini',
  api_key_encrypted TEXT NOT NULL,
  key_last_four TEXT NOT NULL DEFAULT '',
  connection_status TEXT NOT NULL DEFAULT 'saved',
  last_tested_at TEXT,
  last_error TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS seo_flow_schemas (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  source_text TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS media_assets (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  original_name TEXT NOT NULL,
  stored_name TEXT NOT NULL UNIQUE,
  file_path TEXT NOT NULL,
  public_url TEXT NOT NULL,
  content_type TEXT NOT NULL,
  file_size INTEGER NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS media_groups (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  logo_media_id INTEGER NOT NULL,
  icon_media_id INTEGER NOT NULL,
  main_media_id INTEGER NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, name),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (logo_media_id) REFERENCES media_assets(id),
  FOREIGN KEY (icon_media_id) REFERENCES media_assets(id),
  FOREIGN KEY (main_media_id) REFERENCES media_assets(id)
);

CREATE INDEX IF NOT EXISTS idx_projects_updated_at
ON projects(updated_at);

CREATE INDEX IF NOT EXISTS idx_generation_logs_project_id
ON generation_logs(project_id);

CREATE INDEX IF NOT EXISTS idx_sessions_token_hash
ON sessions(token_hash);

CREATE INDEX IF NOT EXISTS idx_sessions_expires_at
ON sessions(expires_at);

CREATE INDEX IF NOT EXISTS idx_ai_connections_user_id
ON ai_connections(user_id);

CREATE INDEX IF NOT EXISTS idx_seo_flow_schemas_user_id
ON seo_flow_schemas(user_id);

CREATE INDEX IF NOT EXISTS idx_media_assets_created_at
ON media_assets(created_at);

CREATE INDEX IF NOT EXISTS idx_media_groups_updated_at
ON media_groups(updated_at);
