-- ============================================================
-- TRADER REBAHAN - MySQL Schema
-- ============================================================
-- charset: utf8mb4
-- engine : InnoDB
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `order_items`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `password_resets`;
DROP TABLE IF EXISTS `email_verifications`;
DROP TABLE IF EXISTS `ib_activation_requests`;
DROP TABLE IF EXISTS `ea_sets`;
DROP TABLE IF EXISTS `recommended_brokers`;
DROP TABLE IF EXISTS `announcements`;
DROP TABLE IF EXISTS `eas`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `monitor_accounts`;
DROP TABLE IF EXISTS `payment_methods`;

SET FOREIGN_KEY_CHECKS = 1;

-- =========================
-- USERS
-- =========================
CREATE TABLE `users` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(120) NOT NULL,
  `email`         VARCHAR(190) NOT NULL,
  `password`      VARCHAR(255) NOT NULL,
  `phone`         VARCHAR(40)  DEFAULT NULL,
  `telegram`      VARCHAR(60)  DEFAULT NULL,
  `broker_account`VARCHAR(120) DEFAULT NULL,
  `role`          ENUM('member','admin') NOT NULL DEFAULT 'member',
  `is_ib`         TINYINT(1)   NOT NULL DEFAULT 0,
  `status`        ENUM('active','suspended','pending_verification') NOT NULL DEFAULT 'active',
  `email_verified_at` DATETIME DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- EMAIL VERIFICATION CODES
-- =========================
CREATE TABLE `email_verifications` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`      VARCHAR(190) NOT NULL,
  `code`       VARCHAR(8)   NOT NULL,
  `attempts`   INT          NOT NULL DEFAULT 0,
  `expires_at` DATETIME     NOT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ev_email_idx` (`email`),
  KEY `ev_code_idx` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- PASSWORD RESETS
-- =========================
CREATE TABLE `password_resets` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`      VARCHAR(190) NOT NULL,
  `token`      VARCHAR(120) NOT NULL,
  `expires_at` DATETIME     NOT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `password_resets_email_idx` (`email`),
  KEY `password_resets_token_idx` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- EAs (produk)
-- =========================
CREATE TABLE `eas` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`        VARCHAR(20)  NOT NULL,
  `name`        VARCHAR(160) NOT NULL,
  `slug`        VARCHAR(160) NOT NULL,
  `tagline`     TEXT         DEFAULT NULL,
  `description` LONGTEXT     DEFAULT NULL,
  `pair`        VARCHAR(120) DEFAULT NULL,
  `type`        VARCHAR(40)  DEFAULT 'Premium',
  `rating`      VARCHAR(40)  DEFAULT NULL,
  `price`       INT UNSIGNED NOT NULL DEFAULT 0,
  `price_label` VARCHAR(60)  DEFAULT NULL,
  `watch_profit`VARCHAR(40)  DEFAULT NULL,
  `dd_max`      VARCHAR(40)  DEFAULT NULL,
  `features`    TEXT         DEFAULT NULL COMMENT 'JSON array of feature strings',
  `file_path`   VARCHAR(255) DEFAULT NULL COMMENT 'Relative path within ea-files dir (general/universal file)',
  `file_original` VARCHAR(255) DEFAULT NULL COMMENT 'original filename saat upload',
  `file_mt4_path` VARCHAR(255) DEFAULT NULL COMMENT 'file khusus MT4',
  `file_mt4_original` VARCHAR(255) DEFAULT NULL,
  `file_mt5_path` VARCHAR(255) DEFAULT NULL COMMENT 'file khusus MT5',
  `file_mt5_original` VARCHAR(255) DEFAULT NULL,
  `image_path`  VARCHAR(255) DEFAULT NULL,
  `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `eas_code_unique` (`code`),
  UNIQUE KEY `eas_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- ORDERS
-- =========================
CREATE TABLE `orders` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`            VARCHAR(40)  NOT NULL,
  `user_id`         INT UNSIGNED NOT NULL,
  `ea_id`           INT UNSIGNED NOT NULL,
  `ea_name`         VARCHAR(160) NOT NULL,
  `amount`          INT UNSIGNED NOT NULL DEFAULT 0,
  `payment_method`  VARCHAR(40)  DEFAULT NULL,
  `payment_proof`   VARCHAR(255) DEFAULT NULL COMMENT 'Relative path under /uploads/payments',
  `broker_account`  VARCHAR(120) DEFAULT NULL,
  `account_name`    VARCHAR(120) DEFAULT NULL COMMENT 'Nama pemilik akun broker',
  `broker_server`   VARCHAR(120) DEFAULT NULL COMMENT 'Nama server broker, mis. Exness-MT5Real37',
  `platform`        ENUM('MT4','MT5','BOTH') NOT NULL DEFAULT 'BOTH',
  `license_lock`    ENUM('account_number','account_name','account_full','server','none') NOT NULL DEFAULT 'account_number' COMMENT 'Tipe lock lisensi EA yang diminta member',
  `under_ib`        TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'member menyatakan dirinya under IB',
  `notes`           TEXT         DEFAULT NULL,
  `status`          ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `admin_note`      TEXT         DEFAULT NULL,
  `delivered_file`  VARCHAR(255) DEFAULT NULL COMMENT 'File EA yang dikirim ke pembeli (override file default EA)',
  `delivered_file_label` VARCHAR(160) DEFAULT NULL,
  `approved_at`     DATETIME     DEFAULT NULL,
  `approved_by`     INT UNSIGNED DEFAULT NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orders_code_unique` (`code`),
  KEY `orders_user_idx` (`user_id`),
  KEY `orders_ea_idx` (`ea_id`),
  KEY `orders_status_idx` (`status`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_orders_ea`   FOREIGN KEY (`ea_id`)   REFERENCES `eas`   (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- MONITOR ACCOUNTS (akun pantau yg ditampilkan publik & member)
-- =========================
CREATE TABLE `monitor_accounts` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`      VARCHAR(160) NOT NULL,
  `ea_code`    VARCHAR(20)  DEFAULT NULL,
  `server`     VARCHAR(120) DEFAULT NULL,
  `account_number` VARCHAR(60) DEFAULT NULL,
  `account_password` VARCHAR(120) DEFAULT NULL COMMENT 'investor password',
  `profit`     VARCHAR(40)  DEFAULT NULL,
  `dd`         VARCHAR(40)  DEFAULT NULL,
  `link`       VARCHAR(255) DEFAULT NULL,
  `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'tampil di hero homepage',
  `members_only` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- PAYMENT METHODS (rekening, e-wallet, QRIS — dikelola admin)
-- =========================
CREATE TABLE `payment_methods` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type`           ENUM('bank','ewallet','qris','crypto','other') NOT NULL DEFAULT 'bank',
  `name`           VARCHAR(120) NOT NULL COMMENT 'BCA, Mandiri, DANA, QRIS Statis, dll',
  `account_number` VARCHAR(120) DEFAULT NULL COMMENT 'nomor rekening / nomor HP / dll',
  `account_holder` VARCHAR(120) DEFAULT NULL,
  `image_path`     VARCHAR(255) DEFAULT NULL COMMENT 'untuk QRIS image / logo',
  `instructions`   TEXT         DEFAULT NULL,
  `is_active`      TINYINT(1)   NOT NULL DEFAULT 1,
  `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,
  PRIMARY KEY (`id`),
  KEY `payment_methods_type_active_idx` (`type`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- SETTINGS (key-value sederhana)
-- =========================
CREATE TABLE `settings` (
  `key_name`  VARCHAR(80)  NOT NULL,
  `value`     TEXT         DEFAULT NULL,
  `updated_at`DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- IB ACTIVATION REQUESTS
-- =========================
CREATE TABLE `ib_activation_requests` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         INT UNSIGNED NOT NULL,
  `contact_type`    ENUM('whatsapp','telegram') NOT NULL DEFAULT 'whatsapp',
  `contact_value`   VARCHAR(120) NOT NULL,
  `broker_name`     VARCHAR(120) NOT NULL,
  `account_number`  VARCHAR(60)  NOT NULL,
  `status`          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `activation_code` VARCHAR(60)  DEFAULT NULL COMMENT 'auto-generate dgn formula nomor_akun*17+58975 saat approve',
  `admin_note`      TEXT         DEFAULT NULL,
  `processed_by`    INT UNSIGNED DEFAULT NULL,
  `processed_at`    DATETIME     DEFAULT NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ib_user_idx` (`user_id`),
  KEY `ib_status_idx` (`status`),
  KEY `ib_acc_idx` (`account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- EA SET FILES (rekomendasi set per EA)
-- =========================
CREATE TABLE `ea_sets` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ea_id`        INT UNSIGNED NOT NULL,
  `name`         VARCHAR(160) NOT NULL,
  `description`  TEXT,
  `pair`         VARCHAR(40) DEFAULT NULL,
  `timeframe`    VARCHAR(20) DEFAULT NULL,
  `file_path`    VARCHAR(255) NOT NULL,
  `file_original` VARCHAR(255) DEFAULT NULL,
  `is_active`    TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order`   INT NOT NULL DEFAULT 0,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ea_idx` (`ea_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- RECOMMENDED BROKERS
-- =========================
CREATE TABLE `recommended_brokers` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                  VARCHAR(120) NOT NULL,
  `tagline`               VARCHAR(255) DEFAULT NULL,
  `logo_path`             VARCHAR(255) DEFAULT NULL,
  `description`           TEXT,
  `register_url`          VARCHAR(500) DEFAULT NULL,
  `register_instructions` TEXT,
  `change_ib_instructions`TEXT,
  `is_active`             TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order`            INT NOT NULL DEFAULT 0,
  `created_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- ANNOUNCEMENTS / PROMOTIONS
-- =========================
CREATE TABLE `announcements` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`         VARCHAR(180) NOT NULL,
  `description`   TEXT,
  `banner_path`   VARCHAR(255) DEFAULT NULL,
  `youtube_url`   VARCHAR(500) DEFAULT NULL,
  `tiktok_url`    VARCHAR(500) DEFAULT NULL,
  `external_url`  VARCHAR(500) DEFAULT NULL,
  `badge`         VARCHAR(40)  DEFAULT NULL,
  `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
  `starts_at`     DATETIME DEFAULT NULL,
  `ends_at`       DATETIME DEFAULT NULL,
  `sort_order`    INT NOT NULL DEFAULT 0,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;