-- Broker Management Schema Addition
-- Run this after schema.sql to add broker support

-- ----------------------------
-- Table: brokers
-- Stores broker/data source information
-- ----------------------------
CREATE TABLE IF NOT EXISTS `brokers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(20) NOT NULL COMMENT 'Short code for identification',
    `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 IF NOT EXISTS `broker_field_mappings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `broker_id` INT UNSIGNED NOT NULL,
    `system_field` VARCHAR(50) NOT NULL COMMENT 'Our internal field name',
    `csv_column` VARCHAR(100) NOT NULL COMMENT 'Column name/header in broker CSV',
    `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 COMMENT 'Format string for transformation',
    `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 IF NOT EXISTS `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;

-- ----------------------------
-- Modify csv_uploads to link to broker
-- MariaDB compatible syntax (separate statements)
-- ----------------------------
ALTER TABLE `csv_uploads` ADD COLUMN `broker_id` INT UNSIGNED NULL;
ALTER TABLE `csv_uploads` ADD COLUMN `data_type` ENUM('subscription', 'trade_profit') NOT NULL DEFAULT 'trade_profit';
ALTER TABLE `csv_uploads` ADD KEY `idx_broker` (`broker_id`);
ALTER TABLE `csv_uploads` ADD CONSTRAINT `fk_upload_broker` FOREIGN KEY (`broker_id`) REFERENCES `brokers` (`id`) ON DELETE SET NULL;

-- ----------------------------
-- Modify csv_rows for trade profit data
-- MariaDB compatible syntax (separate statements)
-- ----------------------------
ALTER TABLE `csv_rows` ADD COLUMN `account_identifier` VARCHAR(100) NULL;
ALTER TABLE `csv_rows` ADD COLUMN `profit_amount_usd` DECIMAL(15,2) NULL;
ALTER TABLE `csv_rows` ADD COLUMN `trade_date` DATE NULL;
ALTER TABLE `csv_rows` ADD COLUMN `broker_id` INT UNSIGNED NULL;
ALTER TABLE `csv_rows` ADD COLUMN `raw_data_json` LONGTEXT NULL;

-- ----------------------------
-- Table: affiliate_broker_accounts
-- Links affiliates to their broker account IDs
-- ----------------------------
CREATE TABLE IF NOT EXISTS `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 COMMENT 'Account ID at the broker',
    `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;

-- ----------------------------
-- Insert 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');
