-- Payment Verification Platform: core schema (MySQL 8 / MariaDB 10.5+)
-- All timestamps are UTC. Money columns are DECIMAL(14,2).
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS roles (
  id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permissions (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  description VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id TINYINT UNSIGNED NOT NULL,
  permission_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id TINYINT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  email_verify_token CHAR(64) NULL,
  password_reset_token CHAR(64) NULL,
  password_reset_expires DATETIME NULL,
  totp_secret TEXT NULL,
  totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  theme ENUM('dark','light') NOT NULL DEFAULT 'dark',
  ui_prefs JSON NULL,
  referral_code VARCHAR(16) NULL UNIQUE,
  referred_by BIGINT UNSIGNED NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  INDEX idx_users_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_profiles (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL DEFAULT '',
  phone VARCHAR(30) NULL,
  company VARCHAR(120) NULL,
  avatar_path VARCHAR(255) NULL,
  timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Dhaka',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admins (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS login_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  email VARCHAR(190) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  user_agent VARCHAR(255) NULL,
  success TINYINT(1) NOT NULL,
  reason VARCHAR(60) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_lh_user (user_id, created_at),
  INDEX idx_lh_throttle (email, success, created_at),
  INDEX idx_lh_ip (ip, success, created_at)
) ENGINE=InnoDB;

-- Organization = team. Every user gets one team they own; all tenant data hangs off teams.id.
CREATE TABLE IF NOT EXISTS teams (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS team_members (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  invite_email VARCHAR(190) NULL,
  invite_token CHAR(64) NULL,
  role ENUM('owner','manager','developer','viewer') NOT NULL DEFAULT 'viewer',
  permissions JSON NULL,
  status ENUM('invited','active','removed') NOT NULL DEFAULT 'invited',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_team_user (team_id, user_id),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS brands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  logo_path VARCHAR(255) NULL,
  website VARCHAR(255) NULL,
  description TEXT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Plans (fully dynamic; nothing hardcoded)
CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  description TEXT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  device_limit INT UNSIGNED NOT NULL DEFAULT 1,
  account_limit INT UNSIGNED NOT NULL DEFAULT 1,
  monthly_tx_limit INT UNSIGNED NOT NULL DEFAULT 100,
  api_request_limit INT UNSIGNED NOT NULL DEFAULT 1000,
  webhook_limit INT UNSIGNED NOT NULL DEFAULT 1,
  history_retention_days INT UNSIGNED NOT NULL DEFAULT 30,
  support_level VARCHAR(40) NOT NULL DEFAULT 'standard',
  is_trial TINYINT(1) NOT NULL DEFAULT 0,
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS plan_features (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id INT UNSIGNED NOT NULL,
  feature_key VARCHAR(60) NOT NULL,
  feature_label VARCHAR(120) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_plan_feature (plan_id, feature_key),
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS coupons (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  discount_type ENUM('percent','fixed') NOT NULL,
  discount_value DECIMAL(14,2) NOT NULL,
  max_uses INT UNSIGNED NULL,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  valid_from DATETIME NULL,
  valid_until DATETIME NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  status ENUM('trial','active','expired','cancelled') NOT NULL DEFAULT 'active',
  starts_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  -- per-subscription overrides for custom user plans (NULL = use plan value)
  override_limits JSON NULL,
  coupon_id INT UNSIGNED NULL,
  expiry_notified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sub_team (team_id, status),
  INDEX idx_sub_expiry (status, expires_at),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  FOREIGN KEY (coupon_id) REFERENCES coupons(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscription_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_id BIGINT UNSIGNED NOT NULL,
  team_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(40) NOT NULL,
  from_plan_id INT UNSIGNED NULL,
  to_plan_id INT UNSIGNED NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sh_team (team_id),
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS providers (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  account_type VARCHAR(30) NOT NULL DEFAULT 'personal',
  parser_class VARCHAR(120) NOT NULL,
  number_pattern VARCHAR(120) NOT NULL DEFAULT '^01[3-9][0-9]{8}$',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS provider_parser_configs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id SMALLINT UNSIGNED NOT NULL,
  config_key VARCHAR(60) NOT NULL,
  config_value TEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_cfg (provider_id, config_key),
  FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  device_uid VARCHAR(100) NULL,
  status ENUM('waiting','connected','offline','suspended','revoked') NOT NULL DEFAULT 'waiting',
  platform VARCHAR(40) NULL,
  app_version VARCHAR(30) NULL,
  last_active_at DATETIME NULL,
  last_sync_at DATETIME NULL,
  last_error VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  UNIQUE KEY uq_device_uid (team_id, device_uid),
  INDEX idx_dev_team (team_id, status),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS device_pairing_codes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  code_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS device_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id BIGINT UNSIGNED NOT NULL,
  token_prefix CHAR(8) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  -- per-device HMAC key (encrypted) so the app can sign sync payloads
  signing_secret_enc TEXT NOT NULL,
  revoked_at DATETIME NULL,
  last_used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_dt_hash (token_hash),
  INDEX idx_dt_prefix (token_prefix),
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  provider_id SMALLINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NULL,
  label VARCHAR(100) NOT NULL,
  account_number VARCHAR(30) NOT NULL,
  account_masked VARCHAR(30) NOT NULL,
  description VARCHAR(255) NULL,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'enabled',
  last_sync_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_pa_team (team_id, status),
  INDEX idx_pa_lookup (provider_id, account_number),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (provider_id) REFERENCES providers(id),
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(20) NOT NULL UNIQUE,
  team_id BIGINT UNSIGNED NOT NULL,
  brand_id BIGINT UNSIGNED NULL,
  customer_reference VARCHAR(100) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  provider_id SMALLINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','processing','paid','expired','cancelled','manual_review') NOT NULL DEFAULT 'pending',
  expires_at DATETIME NOT NULL,
  transaction_id BIGINT UNSIGNED NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- a transaction can complete at most one order
  UNIQUE KEY uq_order_tx (transaction_id),
  INDEX idx_po_match (team_id, status, account_id, amount),
  INDEX idx_po_expiry (status, expires_at),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (provider_id) REFERENCES providers(id),
  FOREIGN KEY (account_id) REFERENCES payment_accounts(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sms_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(20) NOT NULL UNIQUE,
  team_id BIGINT UNSIGNED NOT NULL,
  provider_id SMALLINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NULL,
  account_id BIGINT UNSIGNED NULL,
  external_txn_id VARCHAR(60) NOT NULL,
  sender_masked VARCHAR(30) NULL,
  receiver_account VARCHAR(30) NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  txn_type VARCHAR(30) NOT NULL DEFAULT 'received',
  reference VARCHAR(100) NULL,
  sms_timestamp DATETIME NULL,
  -- sanitized evidence only (no full SMS body): parser id, matched fields, sha256 of body
  evidence JSON NULL,
  processing_status ENUM('received','processing','processed','failed') NOT NULL DEFAULT 'received',
  verification_status ENUM('received','processing','verified','unmatched','duplicate','rejected','manual_review') NOT NULL DEFAULT 'received',
  is_sandbox TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- idempotency: the same provider TXN id is stored once per team
  UNIQUE KEY uq_txn_provider (team_id, provider_id, external_txn_id),
  INDEX idx_tx_list (team_id, created_at),
  INDEX idx_tx_status (team_id, verification_status),
  INDEX idx_tx_amount (team_id, amount),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (provider_id) REFERENCES providers(id),
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
  FOREIGN KEY (account_id) REFERENCES payment_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB;

ALTER TABLE payment_orders
  ADD CONSTRAINT fk_po_tx FOREIGN KEY (transaction_id) REFERENCES sms_transactions(id);

CREATE TABLE IF NOT EXISTS transaction_verification_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  transaction_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  step VARCHAR(40) NOT NULL,
  decision VARCHAR(30) NOT NULL,
  detail VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_vl_tx (transaction_id),
  INDEX idx_vl_team (team_id, created_at),
  FOREIGN KEY (transaction_id) REFERENCES sms_transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reference_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  provider_id SMALLINT UNSIGNED NULL,
  account_id BIGINT UNSIGNED NULL,
  require_reference TINYINT(1) NOT NULL DEFAULT 1,
  match_mode ENUM('exact') NOT NULL DEFAULT 'exact',
  expected_amount DECIMAL(14,2) NULL,
  min_amount DECIMAL(14,2) NULL,
  max_amount DECIMAL(14,2) NULL,
  expiry_minutes INT UNSIGNED NOT NULL DEFAULT 30,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id CHAR(32) NOT NULL UNIQUE,
  team_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(40) NOT NULL,
  transaction_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  payload JSON NOT NULL,
  processing_status ENUM('pending','processed','failed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pe_team (team_id, created_at),
  INDEX idx_pe_status (processing_status, created_at),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_keys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  brand_id BIGINT UNSIGNED NULL,
  label VARCHAR(80) NOT NULL,
  key_prefix CHAR(12) NOT NULL,
  key_hash CHAR(64) NOT NULL UNIQUE,
  scope ENUM('user','admin') NOT NULL DEFAULT 'user',
  rate_limit_per_min INT UNSIGNED NOT NULL DEFAULT 60,
  ip_allowlist VARCHAR(500) NULL,
  last_used_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ak_prefix (key_prefix),
  INDEX idx_ak_team (team_id),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_usage_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NULL,
  api_key_id BIGINT UNSIGNED NULL,
  device_id BIGINT UNSIGNED NULL,
  scope VARCHAR(10) NOT NULL,
  method VARCHAR(8) NOT NULL,
  path VARCHAR(190) NOT NULL,
  status_code SMALLINT UNSIGNED NOT NULL,
  ip VARCHAR(45) NOT NULL,
  duration_ms INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_aul_team (team_id, created_at),
  INDEX idx_aul_key_min (api_key_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhooks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  brand_id BIGINT UNSIGNED NULL,
  url VARCHAR(500) NOT NULL,
  secret_enc TEXT NOT NULL,
  events JSON NOT NULL,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'enabled',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_wh_team (team_id, status),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  webhook_id BIGINT UNSIGNED NOT NULL,
  payment_event_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','delivered','failed','dead') NOT NULL DEFAULT 'pending',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- one delivery job per (webhook, event): idempotent fan-out
  UNIQUE KEY uq_we (webhook_id, payment_event_id),
  INDEX idx_we_due (status, next_attempt_at),
  FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE,
  FOREIGN KEY (payment_event_id) REFERENCES payment_events(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webhook_delivery_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  webhook_event_id BIGINT UNSIGNED NOT NULL,
  attempt TINYINT UNSIGNED NOT NULL,
  response_code SMALLINT NULL,
  response_excerpt VARCHAR(500) NULL,
  error VARCHAR(255) NULL,
  duration_ms INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wdl_we (webhook_event_id),
  FOREIGN KEY (webhook_event_id) REFERENCES webhook_events(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Wallet: balance is a cache; wallet_ledger is the immutable source of truth.
CREATE TABLE IF NOT EXISTS wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL UNIQUE,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id BIGINT UNSIGNED NOT NULL,
  entry_type ENUM('credit','debit') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  balance_after DECIMAL(14,2) NOT NULL,
  reason ENUM('add_funds','manual_adjustment','subscription_charge','usage_charge','refund') NOT NULL,
  reference VARCHAR(80) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ledger_ref (wallet_id, reason, reference),
  INDEX idx_wl_wallet (wallet_id, created_at),
  FOREIGN KEY (wallet_id) REFERENCES wallets(id),
  CHECK (amount > 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS credit_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  kind ENUM('add_funds_request') NOT NULL DEFAULT 'add_funds_request',
  amount DECIMAL(14,2) NOT NULL,
  method VARCHAR(40) NULL,
  payer_reference VARCHAR(100) NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  ledger_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ct_status (status, created_at),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  type VARCHAR(40) NOT NULL,
  title VARCHAR(160) NOT NULL,
  body VARCHAR(500) NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_n_user (user_id, read_at, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(160) NOT NULL,
  category VARCHAR(40) NOT NULL DEFAULT 'general',
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  status ENUM('open','in_progress','waiting_user','resolved','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_st_team (team_id, status),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  is_staff TINYINT(1) NOT NULL DEFAULT 0,
  body TEXT NOT NULL,
  attachment_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sm_ticket (ticket_id),
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  team_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(40) NULL,
  target_id VARCHAR(40) NULL,
  detail VARCHAR(500) NULL,
  ip VARCHAR(45) NULL,
  is_security TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_al_actor (actor_user_id, created_at),
  INDEX idx_al_sec (is_security, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS system_settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cms_pages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  title VARCHAR(160) NOT NULL,
  content_json JSON NULL,
  body_html MEDIUMTEXT NULL,
  seo_title VARCHAR(160) NULL,
  seo_description VARCHAR(300) NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  to_email VARCHAR(190) NOT NULL,
  subject VARCHAR(200) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  INDEX idx_el_status (status, created_at)
) ENGINE=InnoDB;

-- Infra tables (not in the required list but needed by spec: cron locks, rate limits, daily rollups)
CREATE TABLE IF NOT EXISTS cron_runs (
  job VARCHAR(60) PRIMARY KEY,
  locked_until DATETIME NULL,
  last_started_at DATETIME NULL,
  last_finished_at DATETIME NULL,
  last_status VARCHAR(20) NULL,
  last_message VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS daily_stats (
  team_id BIGINT UNSIGNED NOT NULL,
  stat_date DATE NOT NULL,
  provider_id SMALLINT UNSIGNED NOT NULL,
  tx_count INT UNSIGNED NOT NULL DEFAULT 0,
  verified_count INT UNSIGNED NOT NULL DEFAULT 0,
  unmatched_count INT UNSIGNED NOT NULL DEFAULT 0,
  amount_total DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (team_id, stat_date, provider_id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
