-- Affiliate Program Database Schema
-- MySQL 8.0+
-- Run this file to create the database structure

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table: users
-- Core user accounts for both admins and affiliates
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'affiliate') NOT NULL DEFAULT 'affiliate',
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) 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_users_email` (`email`),
    KEY `idx_users_role` (`role`),
    KEY `idx_users_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: affiliate_profiles
-- Extended profile for affiliates with sponsor relationships
-- ----------------------------
DROP TABLE IF EXISTS `affiliate_profiles`;
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
-- ----------------------------
DROP TABLE IF EXISTS `program_config`;
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
-- ----------------------------
DROP TABLE IF EXISTS `commission_splits`;
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: csv_uploads
-- Tracks uploaded CSV files for subscription data
-- ----------------------------
DROP TABLE IF EXISTS `csv_uploads`;
CREATE TABLE `csv_uploads` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `period` VARCHAR(7) NOT NULL COMMENT 'Format: YYYY-MM',
    `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`),
    CONSTRAINT `fk_upload_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: csv_rows
-- Individual rows from uploaded CSV files
-- ----------------------------
DROP TABLE IF EXISTS `csv_rows`;
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,
    `affiliate_user_id` INT UNSIGNED NULL,
    `subscription_amount_usd` DECIMAL(15,2) NOT NULL,
    `notes` TEXT NULL,
    `is_valid` TINYINT(1) NOT NULL DEFAULT 1,
    `validation_error` VARCHAR(255) 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
-- ----------------------------
DROP TABLE IF EXISTS `commission_ledger`;
CREATE TABLE `commission_ledger` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `period` VARCHAR(7) NOT NULL,
    `earner_user_id` INT UNSIGNED NOT NULL COMMENT 'Affiliate receiving commission',
    `source_user_id` INT UNSIGNED NOT NULL COMMENT 'Affiliate who generated the volume',
    `csv_row_id` INT UNSIGNED NULL,
    `level` TINYINT UNSIGNED NOT NULL,
    `base_amount_usd` DECIMAL(15,2) NOT NULL COMMENT 'Original subscription amount',
    `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)
-- ----------------------------
DROP TABLE IF EXISTS `company_ledger`;
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 COMMENT 'Level where upline was missing, if applicable',
    `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: password_resets
-- Password reset tokens
-- ----------------------------
DROP TABLE IF EXISTS `password_resets`;
CREATE TABLE `password_resets` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `token` VARCHAR(100) NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `used_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_token` (`token`),
    CONSTRAINT `fk_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: activity_log
-- Audit trail for important actions
-- ----------------------------
DROP TABLE IF EXISTS `activity_log`;
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;

SET FOREIGN_KEY_CHECKS = 1;
