-- ============================================================
-- Forex Broker Platform — Full Schema
-- ============================================================
-- Covers: broker accounts, ad slot purchases, ad performance,
-- award nomination applications (with custom categories),
-- and per-category vote tracking.

CREATE TABLE IF NOT EXISTS brokers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  company_name VARCHAR(200) DEFAULT NULL,
  wallet_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Ad slots (inventory brokers can buy) ----------------

CREATE TABLE IF NOT EXISTS ad_slots (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  placement VARCHAR(100) NOT NULL,      -- e.g. Homepage Banner, Sidebar, Newsletter
  description VARCHAR(500) DEFAULT NULL,
  price DECIMAL(10,2) NOT NULL,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Ads purchased & run by a broker ----------------

CREATE TABLE IF NOT EXISTS ads (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  broker_id INT UNSIGNED NOT NULL,
  ad_slot_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  target_url VARCHAR(500) DEFAULT NULL,
  impressions INT UNSIGNED NOT NULL DEFAULT 0,
  clicks INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','expired') NOT NULL DEFAULT 'active',
  purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT NULL,
  FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE,
  FOREIGN KEY (ad_slot_id) REFERENCES ad_slots(id),
  INDEX idx_broker (broker_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Award categories (brokers can create their own) ----------------

CREATE TABLE IF NOT EXISTS award_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL UNIQUE,
  is_custom TINYINT(1) NOT NULL DEFAULT 0,
  created_by_broker_id INT UNSIGNED DEFAULT NULL,
  nomination_fee DECIMAL(10,2) NOT NULL DEFAULT 25.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by_broker_id) REFERENCES brokers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Nomination applications ----------------

CREATE TABLE IF NOT EXISTS nominations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  broker_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  votes INT UNSIGNED NOT NULL DEFAULT 0,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES award_categories(id),
  INDEX idx_broker (broker_id),
  INDEX idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Individual vote events (also lets you prevent duplicate voting later
-- by checking voter_identifier, and power the AI guide's timing analysis).
CREATE TABLE IF NOT EXISTS votes_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nomination_id INT UNSIGNED NOT NULL,
  voter_identifier VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (nomination_id) REFERENCES nominations(id) ON DELETE CASCADE,
  INDEX idx_nomination (nomination_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Payment orders (Binance Pay) ----------------

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  broker_id INT UNSIGNED NOT NULL,
  order_type ENUM('ad_slot','nomination') NOT NULL,
  payment_method ENUM('binance','bank_transfer') NOT NULL DEFAULT 'binance',
  reference_data JSON NOT NULL,          -- e.g. {"ad_slot_id":2,"title":"...","target_url":"..."}
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USDT',
  merchant_trade_no VARCHAR(64) NOT NULL UNIQUE,
  binance_prepay_id VARCHAR(100) DEFAULT NULL,
  checkout_url VARCHAR(500) DEFAULT NULL,
  bank_txn_reference VARCHAR(150) DEFAULT NULL,   -- broker-submitted transfer reference/slip number
  bank_sender_name VARCHAR(150) DEFAULT NULL,     -- name the transfer was sent from
  bank_note VARCHAR(500) DEFAULT NULL,            -- any extra note from the broker
  bank_proof_submitted_at TIMESTAMP NULL DEFAULT NULL,
  status ENUM('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE,
  INDEX idx_broker (broker_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- Admin users ----------------

CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin login: username "admin", password "ChangeMe123!"
-- CHANGE THIS IMMEDIATELY after your first login (Admin Panel > Settings > Change Password).
INSERT INTO admin_users (username, password_hash) VALUES
 ('admin', '$2b$12$azhu2QjePkAZj2Y5ZjoPuOGaXRisHOJxUCqbLBagXytySqcf7wYq6');

-- ---------------- Platform settings (editable from Admin Panel > Settings) ----------------

CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key, setting_value) VALUES
 ('admin_notification_email', 'mehenix@gmail.com'),
 ('smtp_host', ''),
 ('smtp_port', '587'),
 ('smtp_username', ''),
 ('smtp_password', ''),
 ('smtp_secure', 'tls'),
 ('smtp_from_email', ''),
 ('smtp_from_name', 'Broker Platform'),
 ('binance_api_key', ''),
 ('binance_api_secret', ''),
 ('binance_merchant_id', ''),
 ('bank_name', ''),
 ('bank_account_name', ''),
 ('bank_account_number', ''),
 ('bank_routing_swift', ''),
 ('bank_branch', ''),
 ('bank_instructions', 'Please use your Order Reference as the transfer memo/note so we can match your payment.'),
 ('site_base_url', 'https://yoursite.com');

-- ---------------- Sample data (safe to delete) ----------------

INSERT INTO ad_slots (name, placement, description, price, duration_days) VALUES
 ('Homepage Banner', 'Homepage Top', 'Full-width banner on the homepage above the fold', 150.00, 30),
 ('Sidebar Spotlight', 'Sidebar', 'Sticky sidebar placement on all article pages', 80.00, 30),
 ('Newsletter Feature', 'Email Newsletter', 'Featured slot in the weekly broker newsletter', 60.00, 7),
 ('Comparison Table Highlight', 'Broker Comparison Page', 'Highlighted row on the broker comparison table', 120.00, 30);

INSERT INTO award_categories (name, is_custom) VALUES
 ('Best Forex Broker 2026', 0),
 ('Best Customer Support', 0),
 ('Most Trusted Broker', 0);