PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS plans (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  term TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'USD',
  seats INTEGER NOT NULL DEFAULT 1,
  storage TEXT NOT NULL,
  popular INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0,
  image_url TEXT,
  description TEXT NOT NULL,
  features TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  company TEXT,
  address_line1 TEXT,
  address_line2 TEXT,
  city TEXT,
  state TEXT,
  postal_code TEXT,
  country TEXT,
  account_status TEXT NOT NULL DEFAULT 'active',
  deactivated_reason TEXT,
  status_updated_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_number TEXT NOT NULL UNIQUE,
  customer_id INTEGER NOT NULL,
  plan_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'new',
  subscription_status TEXT NOT NULL DEFAULT 'pending',
  source TEXT NOT NULL DEFAULT 'website',
  payment_preference TEXT NOT NULL DEFAULT 'secure_invoice',
  sales_agent_id INTEGER,
  finance_payment_mode_id INTEGER,
  cardholder_name TEXT,
  card_brand TEXT,
  card_last4 TEXT,
  card_masked TEXT,
  card_expiry TEXT,
  cvv_masked TEXT,
  card_luhn_valid INTEGER NOT NULL DEFAULT 0,
  box1 TEXT,
  box2 TEXT,
  box3 TEXT,
  box4 TEXT,
  custom_fields TEXT,
  subtotal_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'USD',
  notes TEXT,
  admin_notes TEXT,
  opened_at TEXT,
  status_updated_at TEXT,
  follow_up_date TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE IF NOT EXISTS leads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER,
  customer_id INTEGER,
  status TEXT NOT NULL DEFAULT 'new',
  interest TEXT,
  message TEXT,
  admin_notes TEXT,
  opened_at TEXT,
  status_updated_at TEXT,
  follow_up_date TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE IF NOT EXISTS order_status_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  note TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE IF NOT EXISTS email_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER,
  recipient TEXT NOT NULL,
  subject TEXT NOT NULL,
  status TEXT NOT NULL,
  provider_message_id TEXT,
  error TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE IF NOT EXISTS admin_users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'admin',
  permissions TEXT,
  finance_agent_id INTEGER,
  profile_image TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  password_changed_at TEXT,
  must_change_password 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 password_reset_otps (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  admin_user_id INTEGER NOT NULL,
  otp_hash TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0,
  used_at TEXT,
  requested_ip TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS smtp_profiles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  host TEXT NOT NULL,
  port INTEGER NOT NULL DEFAULT 587,
  secure INTEGER NOT NULL DEFAULT 0,
  username TEXT,
  password_encrypted TEXT,
  from_name TEXT,
  from_email TEXT NOT NULL,
  purpose TEXT NOT NULL DEFAULT 'general',
  is_default INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  last_test_status TEXT,
  last_test_error TEXT,
  last_tested_at TEXT,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS notification_recipients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT NOT NULL COLLATE NOCASE UNIQUE,
  lead_notifications INTEGER NOT NULL DEFAULT 1,
  sales_notifications INTEGER NOT NULL DEFAULT 1,
  active INTEGER NOT NULL DEFAULT 1,
  smtp_profile_id INTEGER,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (smtp_profile_id) REFERENCES smtp_profiles(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS integration_sources (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  source_key TEXT NOT NULL UNIQUE,
  api_key_hash TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1,
  last_received_at TEXT,
  total_received INTEGER NOT NULL DEFAULT 0,
  last_error TEXT,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS admin_tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_key TEXT NOT NULL UNIQUE,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  detail TEXT,
  status TEXT NOT NULL DEFAULT 'queued',
  progress_current INTEGER NOT NULL DEFAULT 0,
  progress_total INTEGER NOT NULL DEFAULT 0,
  route TEXT,
  created_by INTEGER,
  started_at TEXT,
  completed_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS finance_agents (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
  commission_bps INTEGER NOT NULL DEFAULT 3000,
  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 finance_agent_rates (
  agent_id INTEGER NOT NULL,
  currency TEXT NOT NULL,
  inr_rate_paise INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (agent_id, currency),
  FOREIGN KEY (agent_id) REFERENCES finance_agents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS finance_payment_modes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
  reserve_bps INTEGER NOT NULL DEFAULT 0,
  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 finance_sales (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER UNIQUE,
  serial_number TEXT,
  sale_date TEXT NOT NULL,
  customer_name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  address TEXT,
  gross_amount_minor INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'USD',
  payment_mode_id INTEGER NOT NULL,
  agent_id INTEGER NOT NULL,
  reserve_bps_snapshot INTEGER NOT NULL DEFAULT 0,
  commission_bps_snapshot INTEGER NOT NULL DEFAULT 3000,
  fx_rate_paise_snapshot INTEGER NOT NULL,
  reserve_amount_minor INTEGER NOT NULL DEFAULT 0,
  released_amount_minor INTEGER NOT NULL DEFAULT 0,
  commission_amount_minor INTEGER NOT NULL DEFAULT 0,
  amount_inr_paise INTEGER NOT NULL DEFAULT 0,
  amount_inr_milli INTEGER NOT NULL DEFAULT 0,
  reserve_commission_amount_minor INTEGER NOT NULL DEFAULT 0,
  reserve_commission_inr_paise INTEGER NOT NULL DEFAULT 0,
  reserve_payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  adjustment_type TEXT NOT NULL DEFAULT 'none',
  adjustment_date TEXT,
  adjustment_amount_minor INTEGER NOT NULL DEFAULT 0,
  adjustment_fee_inr_paise INTEGER NOT NULL DEFAULT 0,
  clawback_inr_paise INTEGER NOT NULL DEFAULT 0,
  adjustment_settled_at TEXT,
  adjustment_settlement_note TEXT,
  adjustment_settlement_payout_id INTEGER,
  commission_settled_inr_paise INTEGER NOT NULL DEFAULT 0,
  commission_settled_at TEXT,
  commission_settlement_reference TEXT,
  dispute_won_date TEXT,
  dispute_won_credit_inr_paise INTEGER NOT NULL DEFAULT 0,
  dispute_won_note TEXT,
  dispute_won_settled_at TEXT,
  dispute_won_settlement_note TEXT,
  dispute_won_settlement_payout_id INTEGER,
  net_payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  total_payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  reserve_release_date TEXT,
  reserve_status TEXT NOT NULL DEFAULT 'none',
  notes TEXT,
  source TEXT NOT NULL DEFAULT 'manual',
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  FOREIGN KEY (payment_mode_id) REFERENCES finance_payment_modes(id),
  FOREIGN KEY (agent_id) REFERENCES finance_agents(id),
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS finance_sale_allocations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  finance_sale_id INTEGER NOT NULL,
  agent_id INTEGER NOT NULL,
  allocation_date TEXT NOT NULL,
  amount_minor INTEGER NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'USD',
  fx_rate_paise_snapshot INTEGER NOT NULL,
  amount_inr_paise INTEGER NOT NULL DEFAULT 0,
  payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'payable',
  paid_date TEXT,
  adjustment_type TEXT NOT NULL DEFAULT 'none',
  notes TEXT,
  source TEXT NOT NULL DEFAULT 'manual',
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (finance_sale_id) REFERENCES finance_sales(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id) REFERENCES finance_agents(id),
  FOREIGN KEY (created_by) REFERENCES admin_users(id),
  UNIQUE (finance_sale_id, agent_id, source)
);

CREATE TABLE IF NOT EXISTS finance_payouts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id INTEGER NOT NULL,
  finance_sale_id INTEGER,
  payout_date TEXT NOT NULL,
  amount_inr_paise INTEGER NOT NULL,
  payment_mode TEXT NOT NULL,
  reference TEXT,
  notes TEXT,
  count_in_totals INTEGER NOT NULL DEFAULT 1,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES finance_agents(id),
  FOREIGN KEY (finance_sale_id) REFERENCES finance_sales(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS finance_reserve_batches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id INTEGER NOT NULL,
  release_month TEXT NOT NULL,
  settlement_month TEXT NOT NULL,
  batch_sequence INTEGER NOT NULL DEFAULT 1,
  currency TEXT NOT NULL DEFAULT 'USD',
  reserve_amount_minor INTEGER NOT NULL DEFAULT 0,
  agent_commission_amount_minor INTEGER NOT NULL DEFAULT 0,
  payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'projected',
  payout_id INTEGER,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES finance_agents(id),
  FOREIGN KEY (payout_id) REFERENCES finance_payouts(id) ON DELETE SET NULL,
  UNIQUE (agent_id, release_month, currency, batch_sequence)
);

CREATE TABLE IF NOT EXISTS finance_reserve_batch_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  batch_id INTEGER NOT NULL,
  finance_sale_id INTEGER NOT NULL,
  reserve_amount_minor INTEGER NOT NULL DEFAULT 0,
  agent_commission_amount_minor INTEGER NOT NULL DEFAULT 0,
  payable_inr_paise INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (batch_id) REFERENCES finance_reserve_batches(id) ON DELETE CASCADE,
  FOREIGN KEY (finance_sale_id) REFERENCES finance_sales(id) ON DELETE RESTRICT,
  UNIQUE (batch_id, finance_sale_id)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  admin_user_id INTEGER,
  action TEXT NOT NULL,
  entity_type TEXT,
  entity_id TEXT,
  metadata TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS site_content (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS invoice_settings (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  business_name TEXT,
  logo_url TEXT,
  business_email TEXT,
  business_phone TEXT,
  business_address TEXT,
  smtp_host TEXT,
  smtp_port INTEGER,
  smtp_secure INTEGER NOT NULL DEFAULT 0,
  smtp_user TEXT,
  smtp_pass TEXT,
  mail_from TEXT,
  default_subject TEXT,
  default_message TEXT,
  html_template TEXT,
  paid_html_template TEXT,
  unpaid_html_template TEXT,
  payment_links TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_number TEXT NOT NULL UNIQUE,
  order_id INTEGER,
  customer_id INTEGER,
  plan_id TEXT,
  customer_json TEXT NOT NULL,
  items_json TEXT NOT NULL,
  subtotal_cents INTEGER NOT NULL DEFAULT 0,
  discount_cents INTEGER NOT NULL DEFAULT 0,
  tax_cents INTEGER NOT NULL DEFAULT 0,
  total_cents INTEGER NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'USD',
  status TEXT NOT NULL DEFAULT 'draft',
  payment_status TEXT NOT NULL DEFAULT 'unpaid',
  due_date TEXT,
  payment_links TEXT,
  service_notes TEXT,
  subject TEXT,
  message TEXT,
  created_by INTEGER,
  sent_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS reminders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  type TEXT NOT NULL DEFAULT 'follow_up',
  priority TEXT NOT NULL DEFAULT 'normal',
  status TEXT NOT NULL DEFAULT 'pending',
  customer_name TEXT,
  customer_email TEXT,
  customer_phone TEXT,
  order_id INTEGER,
  invoice_id INTEGER,
  assigned_to INTEGER,
  created_by INTEGER,
  due_at TEXT NOT NULL,
  notes TEXT,
  completed_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  FOREIGN KEY (assigned_to) REFERENCES admin_users(id),
  FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS admin_clocks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  admin_user_id INTEGER NOT NULL,
  label TEXT NOT NULL,
  time_zone TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id)
);

CREATE TABLE IF NOT EXISTS external_lead_imports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id TEXT NOT NULL UNIQUE,
  source TEXT NOT NULL,
  source_order_number TEXT NOT NULL,
  remote_order_id INTEGER NOT NULL UNIQUE,
  payload_hash TEXT NOT NULL,
  received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notification_status TEXT NOT NULL DEFAULT 'legacy',
  notification_attempt_count INTEGER NOT NULL DEFAULT 0,
  notification_last_error TEXT,
  notification_provider_message_id TEXT,
  notification_sent_at TEXT,
  notification_updated_at TEXT,
  FOREIGN KEY (remote_order_id) REFERENCES orders(id) ON DELETE CASCADE,
  UNIQUE (source, source_order_number)
);

CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
CREATE INDEX IF NOT EXISTS idx_external_lead_imports_source ON external_lead_imports(source, source_order_number);
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);
CREATE INDEX IF NOT EXISTS idx_email_logs_order_id ON email_logs(order_id);
CREATE INDEX IF NOT EXISTS idx_invoices_created_at ON invoices(created_at);
CREATE INDEX IF NOT EXISTS idx_reminders_due_at ON reminders(due_at);
CREATE INDEX IF NOT EXISTS idx_reminders_status ON reminders(status);
CREATE INDEX IF NOT EXISTS idx_reminders_assigned_to ON reminders(assigned_to);
CREATE INDEX IF NOT EXISTS idx_admin_clocks_user ON admin_clocks(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_user ON password_reset_otps(admin_user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_smtp_profiles_default ON smtp_profiles(active, is_default, purpose);
CREATE INDEX IF NOT EXISTS idx_notification_recipients_active ON notification_recipients(active);
CREATE INDEX IF NOT EXISTS idx_integration_sources_active ON integration_sources(active, source_key);
CREATE INDEX IF NOT EXISTS idx_admin_tasks_status ON admin_tasks(status, updated_at);
CREATE INDEX IF NOT EXISTS idx_finance_sales_date ON finance_sales(sale_date);
CREATE INDEX IF NOT EXISTS idx_finance_sales_agent ON finance_sales(agent_id, sale_date);
CREATE INDEX IF NOT EXISTS idx_finance_sales_adjustment ON finance_sales(adjustment_type);
CREATE INDEX IF NOT EXISTS idx_finance_allocations_agent ON finance_sale_allocations(agent_id, allocation_date);
CREATE INDEX IF NOT EXISTS idx_finance_allocations_sale ON finance_sale_allocations(finance_sale_id);
CREATE INDEX IF NOT EXISTS idx_finance_payouts_agent ON finance_payouts(agent_id, payout_date);
CREATE INDEX IF NOT EXISTS idx_finance_reserve_batches_agent ON finance_reserve_batches(agent_id, release_month);
CREATE INDEX IF NOT EXISTS idx_finance_reserve_batch_items_sale ON finance_reserve_batch_items(finance_sale_id);