-- ============================================
-- COMPLETE DATABASE SCHEMA
-- Affiliate Program with Broker Management
-- Compatible with MySQL 8.0+ and MariaDB 10.3+
-- ============================================


-- ----------------------------
-- Table: users
-- Core user accounts for both admins and affiliates
-- ----------------------------

-- ----------------------------
-- Table: affiliate_profiles
-- Extended profile for affiliates with sponsor relationships
-- ----------------------------
CREATE TABLE `affiliate_profiles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `sponsor_user_id` INT UNSIGNED NULL,
    `referral_code` VARCHAR(20) NOT NULL,
    `wallet_address` VARCHAR(255) NULL,
    `phone` VARCHAR(30) NULL,
    `country` VARCHAR(100) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_affiliate_user` (`user_id`),
    UNIQUE KEY `idx_referral_code` (`referral_code`),
    KEY `idx_sponsor` (`sponsor_user_id`),
    CONSTRAINT `fk_affiliate_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_affiliate_sponsor` FOREIGN KEY (`sponsor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: program_config
-- Key-value configuration storage
-- ----------------------------
CREATE TABLE `program_config` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `config_key` VARCHAR(100) NOT NULL,
    `config_value` TEXT NOT NULL,
    `description` VARCHAR(255) NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: commission_splits
-- Defines the percentage split for each level in the unilevel plan
-- ----------------------------
CREATE TABLE `commission_splits` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `level` TINYINT UNSIGNED NOT NULL,
    `percent` DECIMAL(5,2) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `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 `idx_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: brokers
-- Stores broker/data source information
-- ----------------------------
CREATE TABLE `brokers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `description` TEXT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `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 `idx_broker_code` (`code`),
    KEY `idx_broker_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: broker_field_mappings
-- Maps CSV columns to system fields for each broker
-- ----------------------------
CREATE TABLE `broker_field_mappings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broker_id` INT UNSIGNED NOT NULL,
    `system_field` VARCHAR(50) NOT NULL,
    `csv_column` VARCHAR(100) NOT NULL,
    `is_required` TINYINT(1) NOT NULL DEFAULT 0,
    `transform_type` ENUM('none', 'date', 'currency', 'email_lookup', 'account_lookup') DEFAULT 'none',
    `transform_format` VARCHAR(100) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_broker_field` (`broker_id`, `system_field`),
    KEY `idx_broker` (`broker_id`),
    CONSTRAINT `fk_mapping_broker` FOREIGN KEY (`broker_id`) REFERENCES `brokers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: broker_sample_headers
-- Stores sample CSV headers for reference
-- ----------------------------
CREATE TABLE `broker_sample_headers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broker_id` INT UNSIGNED NOT NULL,
    `headers_json` LONGTEXT NOT NULL,
    `sample_row_json` LONGTEXT NULL,
    `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_broker` (`broker_id`),
    CONSTRAINT `fk_sample_broker` FOREIGN KEY (`broker_id`) REFERENCES `brokers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: affiliate_broker_accounts
-- Links affiliates to their broker account IDs
-- ----------------------------
CREATE TABLE `affiliate_broker_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `broker_id` INT UNSIGNED NOT NULL,
    `broker_account_id` VARCHAR(100) NOT NULL,
    `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_broker_account` (`broker_id`, `broker_account_id`),
    KEY `idx_user` (`user_id`),
    CONSTRAINT `fk_broker_account_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_broker_account_broker` FOREIGN KEY (`broker_id`) REFERENCES `brokers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: csv_uploads
-- Tracks uploaded CSV files (now with broker support)
-- ----------------------------
CREATE TABLE `csv_uploads` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broker_id` INT UNSIGNED NULL,
    `data_type` ENUM('subscription', 'trade_profit') NOT NULL DEFAULT 'trade_profit',
    `period` VARCHAR(7) NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `uploaded_by` INT UNSIGNED NOT NULL,
    `total_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `valid_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `invalid_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_volume_usd` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('pending', 'validated', 'processed', 'failed') NOT NULL DEFAULT 'pending',
    `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `processed_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    KEY `idx_period` (`period`),
    KEY `idx_status` (`status`),
    KEY `idx_uploaded_by` (`uploaded_by`),
    KEY `idx_broker` (`broker_id`),
    CONSTRAINT `fk_upload_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_upload_broker` FOREIGN KEY (`broker_id`) REFERENCES `brokers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: csv_rows
-- Individual rows from uploaded CSV files (with trade profit support)
-- ----------------------------
CREATE TABLE `csv_rows` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `upload_id` INT UNSIGNED NOT NULL,
    `row_number` INT UNSIGNED NOT NULL,
    `period` VARCHAR(7) NOT NULL,
    `affiliate_email` VARCHAR(255) NOT NULL,
    `account_identifier` VARCHAR(100) NULL,
    `affiliate_user_id` INT UNSIGNED NULL,
    `subscription_amount_usd` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `profit_amount_usd` DECIMAL(15,2) NULL,
    `trade_date` DATE NULL,
    `broker_id` INT UNSIGNED NULL,
    `notes` TEXT NULL,
    `is_valid` TINYINT(1) NOT NULL DEFAULT 1,
    `validation_error` VARCHAR(255) NULL,
    `raw_data_json` LONGTEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_upload` (`upload_id`),
    KEY `idx_period` (`period`),
    KEY `idx_affiliate` (`affiliate_user_id`),
    KEY `idx_valid` (`is_valid`),
    CONSTRAINT `fk_row_upload` FOREIGN KEY (`upload_id`) REFERENCES `csv_uploads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_row_affiliate` FOREIGN KEY (`affiliate_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: commission_ledger
-- Records all commission payments to affiliates
-- ----------------------------
CREATE TABLE `commission_ledger` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `period` VARCHAR(7) NOT NULL,
    `earner_user_id` INT UNSIGNED NOT NULL,
    `source_user_id` INT UNSIGNED NOT NULL,
    `csv_row_id` INT UNSIGNED NULL,
    `level` TINYINT UNSIGNED NOT NULL,
    `base_amount_usd` DECIMAL(15,2) NOT NULL,
    `commission_percent` DECIMAL(5,2) NOT NULL,
    `commission_amount_usd` DECIMAL(15,2) NOT NULL,
    `status` ENUM('pending', 'approved', 'paid') NOT NULL DEFAULT 'pending',
    `paid_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_period` (`period`),
    KEY `idx_earner` (`earner_user_id`),
    KEY `idx_source` (`source_user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_level` (`level`),
    KEY `idx_csv_row` (`csv_row_id`),
    CONSTRAINT `fk_ledger_earner` FOREIGN KEY (`earner_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ledger_source` FOREIGN KEY (`source_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ledger_row` FOREIGN KEY (`csv_row_id`) REFERENCES `csv_rows` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: company_ledger
-- Tracks company remainder (unpaid commission portions)
-- ----------------------------
CREATE TABLE `company_ledger` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `period` VARCHAR(7) NOT NULL,
    `csv_row_id` INT UNSIGNED NULL,
    `reason` ENUM('base_retention', 'missing_upline', 'processing_fee') NOT NULL,
    `level` TINYINT UNSIGNED NULL,
    `amount_usd` DECIMAL(15,2) NOT NULL,
    `notes` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_period` (`period`),
    KEY `idx_reason` (`reason`),
    KEY `idx_csv_row` (`csv_row_id`),
    CONSTRAINT `fk_company_row` FOREIGN KEY (`csv_row_id`) REFERENCES `csv_rows` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: activity_log
-- Audit trail for important actions
-- ----------------------------
CREATE TABLE `activity_log` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_action` (`action`),
    KEY `idx_created` (`created_at`),
    CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: subscription_packages
-- Subscription tiers that affiliates can purchase
-- ----------------------------
CREATE TABLE `subscription_packages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    `price_usd` DECIMAL(10,2) NOT NULL,
    `duration_days` INT UNSIGNED NOT NULL DEFAULT 30,
    `features` TEXT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT UNSIGNED 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 `idx_slug` (`slug`),
    KEY `idx_active` (`is_active`),
    KEY `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: user_subscriptions
-- Tracks affiliate subscription purchases and status
-- ----------------------------
CREATE TABLE `user_subscriptions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `package_id` INT UNSIGNED NOT NULL,
    `payment_id` INT UNSIGNED NULL,
    `status` ENUM('pending', 'active', 'expired', 'cancelled') NOT NULL DEFAULT 'pending',
    `starts_at` DATETIME NULL,
    `expires_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_package` (`package_id`),
    KEY `idx_status` (`status`),
    KEY `idx_expires` (`expires_at`),
    CONSTRAINT `fk_subscription_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_subscription_package` FOREIGN KEY (`package_id`) REFERENCES `subscription_packages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: crypto_payments
-- Tracks crypto payment transactions via Coinbase Commerce or similar
-- ----------------------------
CREATE TABLE `crypto_payments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `subscription_id` INT UNSIGNED NULL,
    `external_id` VARCHAR(100) NULL,
    `external_code` VARCHAR(20) NULL,
    `amount_usd` DECIMAL(10,2) NOT NULL,
    `crypto_amount` DECIMAL(20,8) NULL,
    `crypto_currency` VARCHAR(10) NULL,
    `wallet_address` VARCHAR(255) NULL,
    `status` ENUM('pending', 'confirming', 'completed', 'failed', 'expired', 'refunded') NOT NULL DEFAULT 'pending',
    `payment_url` VARCHAR(500) NULL,
    `webhook_data` LONGTEXT NULL,
    `paid_at` DATETIME NULL,
    `expires_at` DATETIME 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 `idx_external_id` (`external_id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_subscription` (`subscription_id`),
    KEY `idx_status` (`status`),
    KEY `idx_external_code` (`external_code`),
    CONSTRAINT `fk_payment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_payment_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `user_subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: subscription_commissions
-- Commission payouts from subscription purchases
-- ----------------------------
CREATE TABLE `subscription_commissions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `payment_id` INT UNSIGNED NOT NULL,
    `earner_user_id` INT UNSIGNED NOT NULL,
    `source_user_id` INT UNSIGNED NOT NULL,
    `level` TINYINT UNSIGNED NOT NULL,
    `base_amount_usd` DECIMAL(10,2) NOT NULL,
    `commission_percent` DECIMAL(5,2) NOT NULL,
    `commission_amount_usd` DECIMAL(10,2) NOT NULL,
    `status` ENUM('pending', 'approved', 'paid') NOT NULL DEFAULT 'pending',
    `paid_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_payment` (`payment_id`),
    KEY `idx_earner` (`earner_user_id`),
    KEY `idx_source` (`source_user_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_subcomm_payment` FOREIGN KEY (`payment_id`) REFERENCES `crypto_payments` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_subcomm_earner` FOREIGN KEY (`earner_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_subcomm_source` FOREIGN KEY (`source_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Affiliate profiles with sponsor chain: Alice -> Bob -> Charlie -> Diana -> Eve
INSERT INTO `affiliate_profiles` (`user_id`, `sponsor_user_id`, `referral_code`, `wallet_address`, `country`) VALUES
(2, NULL, 'ALICE001', '0xAlice123...', 'USA'),
(3, 2, 'BOB002', '0xBob456...', 'Canada'),
(4, 3, 'CHARLIE003', '0xCharlie789...', 'UK'),
(5, 4, 'DIANA004', '0xDiana012...', 'Australia'),
(6, 5, 'EVE005', '0xEve345...', 'Germany');

-- Commission splits (5 levels, 60% total payout)
INSERT INTO `commission_splits` (`level`, `percent`, `is_active`) VALUES
(1, 25.00, 1),
(2, 15.00, 1),
(3, 10.00, 1),
(4, 6.00, 1),
(5, 4.00, 1);

-- Sample brokers
INSERT INTO `brokers` (`name`, `code`, `description`) VALUES
('Broker Alpha', 'ALPHA', 'Primary trading broker - sends weekly profit reports'),
('Broker Beta', 'BETA', 'Secondary broker - monthly CSV exports'),
('Broker Gamma', 'GAMMA', 'Crypto trading platform');

-- Broker field mappings (maps CSV columns to system fields)
-- Broker Alpha: Account Number, Member ID, Trading Profit, Trade Date, Notes
INSERT INTO `broker_field_mappings` (`broker_id`, `system_field`, `csv_column`, `is_required`) VALUES
(1, 'broker_account', 'Account Number', 1),
(1, 'member_account_id', 'Member ID', 0),
(1, 'profit_amount', 'Trading Profit', 1),
(1, 'trade_date', 'Trade Date', 0),
(1, 'notes', 'Notes', 0);

-- Broker Beta: client_id, member_id, profit_usd, transaction_date, description
INSERT INTO `broker_field_mappings` (`broker_id`, `system_field`, `csv_column`, `is_required`) VALUES
(2, 'broker_account', 'client_id', 1),
(2, 'member_account_id', 'member_id', 0),
(2, 'profit_amount', 'profit_usd', 1),
(2, 'trade_date', 'transaction_date', 0),
(2, 'notes', 'description', 0);

-- Broker Gamma: user_account, system_member_id, earnings, date, email, memo
INSERT INTO `broker_field_mappings` (`broker_id`, `system_field`, `csv_column`, `is_required`) VALUES
(3, 'broker_account', 'user_account', 1),
(3, 'member_account_id', 'system_member_id', 0),
(3, 'profit_amount', 'earnings', 1),
(3, 'trade_date', 'date', 0),
(3, 'email', 'email', 0),
(3, 'notes', 'memo', 0);

-- Affiliate broker accounts (links broker account IDs to affiliates)
-- Alice (user_id=2)
INSERT INTO `affiliate_broker_accounts` (`user_id`, `broker_id`, `broker_account_id`, `is_verified`) VALUES
(2, 1, 'ALPHA-001', 1),
(2, 2, 'BETA-A001', 1),
(2, 3, 'GAM-001', 1);

-- Bob (user_id=3)
INSERT INTO `affiliate_broker_accounts` (`user_id`, `broker_id`, `broker_account_id`, `is_verified`) VALUES
(3, 1, 'ALPHA-002', 1),
(3, 2, 'BETA-A002', 1),
(3, 3, 'GAM-002', 1);

-- Charlie (user_id=4)
INSERT INTO `affiliate_broker_accounts` (`user_id`, `broker_id`, `broker_account_id`, `is_verified`) VALUES
(4, 1, 'ALPHA-003', 1),
(4, 2, 'BETA-A003', 1),
(4, 3, 'GAM-003', 1);

-- Diana (user_id=5)
INSERT INTO `affiliate_broker_accounts` (`user_id`, `broker_id`, `broker_account_id`, `is_verified`) VALUES
(5, 1, 'ALPHA-004', 1),
(5, 2, 'BETA-A004', 1),
(5, 3, 'GAM-004', 1);

-- Eve (user_id=6)
INSERT INTO `affiliate_broker_accounts` (`user_id`, `broker_id`, `broker_account_id`, `is_verified`) VALUES
(6, 1, 'ALPHA-005', 1),
(6, 2, 'BETA-A005', 1),
(6, 3, 'GAM-005', 1);

-- Program config defaults
INSERT INTO `program_config` (`config_key`, `config_value`, `description`) VALUES
('company_name', 'TradePro Affiliates', 'Company display name'),
('support_email', 'support@tradepro.com', 'Support contact email'),
('min_payout', '50.00', 'Minimum payout threshold in USD'),
('payout_day', '15', 'Day of month for payouts'),
('btcpay_server_url', '', 'BTCPay Server URL (e.g., https://pay.yourdomain.com)'),
('btcpay_api_key', '', 'BTCPay Server API Key'),
('btcpay_store_id', '', 'BTCPay Server Store ID'),
('btcpay_webhook_secret', '', 'BTCPay Server Webhook Secret'),
('crypto_payment_timeout_minutes', '60', 'Minutes before crypto payment expires');

-- Sample subscription packages
INSERT INTO `subscription_packages` (`name`, `slug`, `description`, `price_usd`, `duration_days`, `features`, `is_active`, `sort_order`) VALUES
('Starter', 'starter', 'Perfect for beginners getting started with trading signals', 49.00, 30, '["Basic trading signals", "Email support", "Weekly market updates", "Access to beginner tutorials"]', 1, 1),
('Professional', 'professional', 'Advanced features for serious traders', 149.00, 30, '["Premium trading signals", "Priority support", "Daily market analysis", "Advanced trading tools", "Risk management alerts"]', 1, 2),
('Elite', 'elite', 'Complete package for professional traders', 299.00, 30, '["All Professional features", "1-on-1 coaching sessions", "VIP Discord access", "Custom trade setups", "Portfolio review"]', 1, 3),
('Annual Starter', 'annual-starter', 'Starter package - save 2 months with annual billing', 490.00, 365, '["Basic trading signals", "Email support", "Weekly market updates", "Access to beginner tutorials", "2 months FREE"]', 1, 4),
('Annual Professional', 'annual-professional', 'Professional package - save 2 months with annual billing', 1490.00, 365, '["Premium trading signals", "Priority support", "Daily market analysis", "Advanced trading tools", "Risk management alerts", "2 months FREE"]', 1, 5);
