-- Opsional: aplikasi akan auto-migrate saat startup.
-- Kalau ingin manual, import SQL ini lewat phpMyAdmin lalu start Node.js app.

CREATE TABLE IF NOT EXISTS users (
  telegram_id BIGINT PRIMARY KEY,
  username VARCHAR(190) NULL,
  first_name VARCHAR(190) NULL,
  last_name VARCHAR(190) NULL,
  balance BIGINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  last_active DATETIME NOT NULL,
  INDEX idx_users_last_active (last_active),
  INDEX idx_users_balance (balance)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bot_sessions (
  telegram_id BIGINT PRIMARY KEY,
  step VARCHAR(80) NOT NULL,
  data MEDIUMTEXT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id VARCHAR(64) PRIMARY KEY,
  user_id BIGINT NOT NULL,
  kind ENUM('topup','h2h','otp') NOT NULL,
  amount BIGINT NOT NULL,
  cost BIGINT NOT NULL DEFAULT 0,
  profit BIGINT NOT NULL DEFAULT 0,
  status ENUM('pending','paid','processing','success','failed','expired','canceled','refunded') NOT NULL DEFAULT 'pending',
  payment_provider VARCHAR(32) NOT NULL DEFAULT 'pakasir',
  payment_method VARCHAR(32) NOT NULL DEFAULT 'qris',
  payment_reference VARCHAR(128) NULL,
  payment_url TEXT NULL,
  qris_payload TEXT NULL,
  payload MEDIUMTEXT NULL,
  note TEXT NULL,
  paid_at DATETIME NULL,
  expired_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_invoice_user (user_id),
  INDEX idx_invoice_status (status),
  INDEX idx_invoice_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(64) PRIMARY KEY,
  invoice_id VARCHAR(64) NOT NULL,
  user_id BIGINT NOT NULL,
  type ENUM('h2h','otp') NOT NULL,
  api_order_id VARCHAR(128) NULL,
  product_code VARCHAR(100) NULL,
  product_name VARCHAR(255) NULL,
  target VARCHAR(255) NULL,
  status ENUM('queued','processing','success','failed','canceled','refunded') NOT NULL DEFAULT 'queued',
  cost BIGINT NOT NULL DEFAULT 0,
  sell_price BIGINT NOT NULL DEFAULT 0,
  profit BIGINT NOT NULL DEFAULT 0,
  response MEDIUMTEXT NULL,
  message TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  INDEX idx_order_invoice (invoice_id),
  INDEX idx_order_user (user_id),
  INDEX idx_order_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS markups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  scope VARCHAR(40) NOT NULL,
  rule_key VARCHAR(190) NOT NULL,
  percent DECIMAL(8,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_markup (scope, rule_key),
  INDEX idx_markup_scope (scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ledger (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  type VARCHAR(40) NOT NULL,
  amount BIGINT NOT NULL,
  balance_before BIGINT NOT NULL,
  balance_after BIGINT NOT NULL,
  ref_id VARCHAR(80) NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_ledger_user (user_id),
  INDEX idx_ledger_ref (ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_cache (
  cache_key VARCHAR(80) PRIMARY KEY,
  data MEDIUMTEXT NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  level VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,
  meta MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_logs_created (created_at),
  INDEX idx_logs_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;