-- Migration: Broker System with Self-Registration
-- This migration updates the system so clients register themselves
-- and can optionally link to a broker using their unique broker code

-- Add broker_code to users table (unique identifier for brokers)
ALTER TABLE users ADD COLUMN broker_code VARCHAR(10) UNIQUE NULL AFTER phone;

-- Add referred_by_broker to users table (for clients who registered via a broker)
ALTER TABLE users ADD COLUMN referred_by_broker INT NULL AFTER broker_code;
ALTER TABLE users ADD CONSTRAINT fk_referred_broker FOREIGN KEY (referred_by_broker) REFERENCES users(id) ON DELETE SET NULL;

-- Add subscription_start_date (defaults to created_at for payment calculation)
ALTER TABLE users ADD COLUMN subscription_start_date DATE NULL AFTER referred_by_broker;

-- Create user_payments table (payments by registered users, based on registration date)
CREATE TABLE IF NOT EXISTS user_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_year INT NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'mobile_money', 'other') DEFAULT 'cash',
    reference_number VARCHAR(100),
    receipt_number VARCHAR(100),
    notes TEXT,
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending',
    recorded_by INT NULL,
    confirmed_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_year (user_id, payment_year, status),
    INDEX idx_user_id (user_id),
    INDEX idx_payment_year (payment_year),
    INDEX idx_status (status)
);

-- Generate broker codes for existing brokers
-- Format: 6-digit numeric code (e.g., 123456)
UPDATE users SET broker_code = LPAD(FLOOR(100000 + RAND() * 899999), 6, '0')
WHERE role = 'broker' AND broker_code IS NULL;

-- Set subscription_start_date for existing users from their created_at
UPDATE users SET subscription_start_date = DATE(created_at) WHERE subscription_start_date IS NULL;

-- Index for faster broker lookups
CREATE INDEX idx_broker_code ON users(broker_code);
CREATE INDEX idx_referred_by_broker ON users(referred_by_broker);
