-- FITQIOS ALL PRODUK TOKOVOUCHER V3
-- PREFIX pd_
-- Folder baru. Tidak menyentuh tabel Game.

CREATE TABLE IF NOT EXISTS pd_supplier_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier VARCHAR(32) NOT NULL DEFAULT 'tokovoucher',
  member_code VARCHAR(100) NOT NULL DEFAULT '',
  signature_enc TEXT NULL,
  secret_enc TEXT NULL,
  connection_status ENUM('unknown','connected','error') NOT NULL DEFAULT 'unknown',
  supplier_balance DECIMAL(18,2) NULL,
  account_name VARCHAR(190) NULL,
  last_error TEXT NULL,
  last_checked_at DATETIME NULL,
  last_sync_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_supplier (supplier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier VARCHAR(32) NOT NULL DEFAULT 'tokovoucher',
  supplier_id INT NOT NULL,
  slug VARCHAR(100) NOT NULL,
  name VARCHAR(160) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  synced_at DATETIME NULL,
  UNIQUE KEY uq_pd_category_supplier (supplier,supplier_id),
  UNIQUE KEY uq_pd_category_slug (slug),
  KEY idx_pd_category_status (status,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_operators (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier VARCHAR(32) NOT NULL DEFAULT 'tokovoucher',
  supplier_id INT NOT NULL,
  category_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  logo TEXT NULL,
  min_chars INT NULL,
  max_chars INT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  synced_at DATETIME NULL,
  UNIQUE KEY uq_pd_operator_supplier (supplier,supplier_id),
  KEY idx_pd_operator_category (category_id,status,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier VARCHAR(32) NOT NULL DEFAULT 'tokovoucher',
  supplier_id INT NOT NULL,
  operator_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  format_form VARCHAR(32) NULL,
  status TINYINT NOT NULL DEFAULT 1,
  synced_at DATETIME NULL,
  UNIQUE KEY uq_pd_type_supplier (supplier,supplier_id),
  KEY idx_pd_type_operator (operator_id,status,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier VARCHAR(32) NOT NULL DEFAULT 'tokovoucher',
  supplier_id BIGINT NULL,
  category_id INT NOT NULL,
  operator_id INT NOT NULL DEFAULT 0,
  type_id INT NOT NULL DEFAULT 0,
  code VARCHAR(100) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  supplier_price DECIMAL(18,2) NOT NULL DEFAULT 0,

  -- FINAL: margin Fitqios PER PRODUK.
  -- Sinkron supplier TIDAK BOLEH menimpa field ini.
  fitqios_margin DECIMAL(18,2) NOT NULL DEFAULT 100,

  status TINYINT NOT NULL DEFAULT 1,
  synced_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_pd_product_code (supplier,code),
  KEY idx_pd_products_category (category_id,status),
  KEY idx_pd_products_operator (operator_id,status),
  KEY idx_pd_products_type (type_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_reseller_category_margins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  category_id INT NOT NULL,
  margin_0_50000 DECIMAL(18,2) NOT NULL DEFAULT 100,
  margin_50000_200000 DECIMAL(18,2) NOT NULL DEFAULT 100,
  margin_200000_500000 DECIMAL(18,2) NOT NULL DEFAULT 100,
  margin_500000_1000000 DECIMAL(18,2) NOT NULL DEFAULT 100,
  margin_1000000_up DECIMAL(18,2) NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_reseller_category (client_id,category_id),
  KEY idx_pd_reseller_margin_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_reseller_product_promos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  product_code VARCHAR(100) NOT NULL,
  strike_price DECIMAL(18,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_reseller_product_promo (client_id,product_code),
  KEY idx_pd_promo_client (client_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_sync_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id VARCHAR(40) NOT NULL,
  status ENUM('running','success','failed') NOT NULL DEFAULT 'running',
  categories_count INT NOT NULL DEFAULT 0,
  operators_count INT NOT NULL DEFAULT 0,
  types_count INT NOT NULL DEFAULT 0,
  products_count INT NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  UNIQUE KEY uq_pd_sync_batch (batch_id),
  KEY idx_pd_sync_status (status,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  integration_key VARCHAR(100) NOT NULL,
  user_email VARCHAR(190) NOT NULL,
  ref_id VARCHAR(80) NOT NULL,
  category_id INT NOT NULL,
  category_name VARCHAR(160) NOT NULL,
  product_code VARCHAR(100) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  target_value VARCHAR(255) NULL,
  target_masked VARCHAR(255) NULL,
  server_id VARCHAR(120) NULL,
  supplier_price_snapshot DECIMAL(18,2) NOT NULL,
  fitqios_margin_snapshot DECIMAL(18,2) NOT NULL,
  fitqios_price_snapshot DECIMAL(18,2) NOT NULL,
  reseller_margin_snapshot DECIMAL(18,2) NOT NULL,
  sell_snapshot DECIMAL(18,2) NOT NULL,
  strike_price_snapshot DECIMAL(18,2) NULL,
  status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  supplier_ref VARCHAR(120) NULL,
  serial_number TEXT NULL,
  reason_code VARCHAR(100) NULL,
  reason_detail TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_tx_ref (ref_id),
  KEY idx_pd_tx_user (integration_key,user_email,id),
  KEY idx_pd_tx_client (client_id,id),
  KEY idx_pd_tx_status (status,updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pd_supplier_settings (supplier)
VALUES ('tokovoucher')
ON DUPLICATE KEY UPDATE supplier=VALUES(supplier);

-- V6: override input hanya untuk kasus metadata supplier tidak cukup.
CREATE TABLE IF NOT EXISTS pd_input_overrides (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope ENUM('category','operator','type','product') NOT NULL,
  category_id INT NOT NULL DEFAULT 0,
  operator_id INT NOT NULL DEFAULT 0,
  type_id INT NOT NULL DEFAULT 0,
  product_code VARCHAR(100) NOT NULL DEFAULT '',
  schema_json JSON NOT NULL,
  helper_text VARCHAR(255) NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_pd_input_category (category_id,status),
  KEY idx_pd_input_operator (operator_id,status),
  KEY idx_pd_input_type (type_id,status),
  KEY idx_pd_input_product (product_code,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- V7: SECURE INTEGRATION + USER CONTEXT + TRANSACTION CORE
-- Tabel pd_ berdiri sendiri. Tidak menggunakan tabel game_*.
-- ============================================================

CREATE TABLE IF NOT EXISTS pd_clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  balance DECIMAL(18,2) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_pd_clients_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_integrations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  integration_key VARCHAR(100) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_integration_key (integration_key),
  KEY idx_pd_integration_client (client_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_user_contexts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  bukaolshop_user_id VARCHAR(120) NOT NULL DEFAULT '',
  user_email VARCHAR(190) NOT NULL,
  expires_at DATETIME NOT NULL,
  last_used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_user_context_token (token_hash),
  KEY idx_pd_user_context_client (client_id,user_email,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_reseller_integrations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'bukaolshop',
  secret_enc TEXT NOT NULL,
  status ENUM('connected','disconnected') NOT NULL DEFAULT 'connected',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_reseller_provider (client_id,provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_balance_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  type ENUM('debit','refund','topup','adjustment') NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  balance_before DECIMAL(18,2) NOT NULL,
  balance_after DECIMAL(18,2) NOT NULL,
  reference_id VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pd_balance_client (client_id,id),
  UNIQUE KEY uq_pd_balance_once (client_id,type,reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_user_wallet_operations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  ref_id VARCHAR(100) NOT NULL,
  user_email VARCHAR(190) NOT NULL,
  operation ENUM('debit','refund') NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  status ENUM('processing','success','failed') NOT NULL DEFAULT 'processing',
  provider_change_id VARCHAR(190) NULL,
  provider_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_wallet_operation (client_id,ref_id,operation),
  KEY idx_pd_wallet_status (status,updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pd_quotes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_token CHAR(64) NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  user_email VARCHAR(190) NOT NULL,
  product_code VARCHAR(100) NOT NULL,
  target_value VARCHAR(255) NOT NULL,
  server_id VARCHAR(120) NOT NULL DEFAULT '',
  input_json JSON NULL,
  supplier_price DECIMAL(18,2) NOT NULL,
  fitqios_margin DECIMAL(18,2) NOT NULL,
  fitqios_price DECIMAL(18,2) NOT NULL,
  reseller_margin DECIMAL(18,2) NOT NULL,
  sell_price DECIMAL(18,2) NOT NULL,
  strike_price DECIMAL(18,2) NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_quote_token (quote_token),
  KEY idx_pd_quote_expiry (expires_at,used_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE pd_transactions
  ADD COLUMN IF NOT EXISTS client_request_id VARCHAR(100) NULL AFTER client_id,
  ADD COLUMN IF NOT EXISTS quote_token CHAR(64) NULL AFTER integration_key,
  ADD COLUMN IF NOT EXISTS bukaolshop_user_id VARCHAR(120) NULL AFTER user_email,
  ADD COLUMN IF NOT EXISTS operator_id INT NOT NULL DEFAULT 0 AFTER category_name,
  ADD COLUMN IF NOT EXISTS operator_name VARCHAR(190) NOT NULL DEFAULT '' AFTER operator_id,
  ADD COLUMN IF NOT EXISTS type_id INT NOT NULL DEFAULT 0 AFTER operator_name,
  ADD COLUMN IF NOT EXISTS type_name VARCHAR(190) NOT NULL DEFAULT '' AFTER type_id,
  ADD COLUMN IF NOT EXISTS input_snapshot JSON NULL AFTER server_id,
  ADD COLUMN IF NOT EXISTS supplier_price_final DECIMAL(18,2) NULL AFTER strike_price_snapshot,
  ADD COLUMN IF NOT EXISTS user_wallet_status ENUM('none','debited','refund_pending','refunded') NOT NULL DEFAULT 'none' AFTER status,
  ADD COLUMN IF NOT EXISTS user_debit_amount DECIMAL(18,2) NULL AFTER user_wallet_status,
  ADD COLUMN IF NOT EXISTS user_debit_change_id VARCHAR(190) NULL AFTER user_debit_amount,
  ADD COLUMN IF NOT EXISTS user_refund_change_id VARCHAR(190) NULL AFTER user_debit_change_id,
  ADD UNIQUE KEY IF NOT EXISTS uq_pd_tx_client_request (client_id,client_request_id);


-- ============================================================
-- V8: FINAL TRANSACTION NOTIFICATION
-- Saldo tidak mengirim notifikasi. Notifikasi hanya status final.
-- ============================================================
CREATE TABLE IF NOT EXISTS pd_transaction_notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  ref_id VARCHAR(100) NOT NULL,
  final_status ENUM('success','failed') NOT NULL,
  status ENUM('processing','sent','failed') NOT NULL DEFAULT 'processing',
  provider_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_final_notification (client_id,ref_id,final_status),
  KEY idx_pd_notification_status (status,updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- V9: STOREFRONT ENTRY / RECEIPT
-- ============================================================
ALTER TABLE pd_clients
  ADD COLUMN IF NOT EXISTS receipt_title VARCHAR(190) NOT NULL DEFAULT '' AFTER name,
  ADD COLUMN IF NOT EXISTS primary_color VARCHAR(20) NOT NULL DEFAULT '#2563eb' AFTER receipt_title;

CREATE TABLE IF NOT EXISTS pd_entry_nonces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  nonce_hash CHAR(64) NOT NULL,
  user_email VARCHAR(190) NOT NULL,
  bukaolshop_user_id VARCHAR(120) NOT NULL DEFAULT '',
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pd_entry_nonce (nonce_hash),
  KEY idx_pd_entry_nonce_expiry (expires_at,used_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;