-- Run this SQL on your local and live database

-- 1. Modify users.role ENUM
ALTER TABLE users MODIFY COLUMN role ENUM('admin', 'manager', 'hr', 'employee', 'staff') DEFAULT 'employee';
UPDATE users SET role = 'employee' WHERE role = 'staff';

-- 2. Create employee_compensation table
CREATE TABLE IF NOT EXISTS employee_compensation (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    business_id CHAR(36) NOT NULL,
    employment_type ENUM('permanent', 'contract', 'casual') NOT NULL DEFAULT 'permanent',
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    salary_currency VARCHAR(5) NOT NULL DEFAULT 'GHS',
    pay_frequency ENUM('monthly', 'weekly', 'daily') NOT NULL DEFAULT 'monthly',
    contract_start_date DATE NULL,
    contract_end_date DATE NULL,
    contract_amount DECIMAL(12,2) NULL,
    contract_pay_type ENUM('one_time', 'milestone', 'retainer') NOT NULL DEFAULT 'one_time',
    contract_milestone_label VARCHAR(150) NULL,
    preferred_payment_mode ENUM('mobile_money', 'bank', 'cash', 'cheque') NOT NULL DEFAULT 'mobile_money',
    bank_name VARCHAR(100) NULL,
    bank_account_number VARCHAR(50) NULL,
    momo_number VARCHAR(20) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Existing installations: run these if employee_compensation was created before contract/payment fields existed.
ALTER TABLE employee_compensation ADD COLUMN IF NOT EXISTS contract_pay_type ENUM('one_time', 'milestone', 'retainer') NOT NULL DEFAULT 'one_time' AFTER contract_amount;
ALTER TABLE employee_compensation ADD COLUMN IF NOT EXISTS contract_milestone_label VARCHAR(150) NULL AFTER contract_pay_type;
ALTER TABLE employee_compensation ADD COLUMN IF NOT EXISTS preferred_payment_mode ENUM('mobile_money', 'bank', 'cash', 'cheque') NOT NULL DEFAULT 'mobile_money' AFTER contract_milestone_label;

-- 3. Create employee_allowances table
CREATE TABLE IF NOT EXISTS employee_allowances (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    business_id CHAR(36) NOT NULL,
    allowance_type VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    frequency ENUM('monthly', 'weekly', 'one_off') NOT NULL DEFAULT 'monthly',
    start_date DATE NOT NULL,
    end_date DATE NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Create payroll_runs table
CREATE TABLE IF NOT EXISTS payroll_runs (
    id CHAR(36) PRIMARY KEY,
    business_id CHAR(36) NOT NULL,
    period_label VARCHAR(50) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_salaries DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'paid') NOT NULL DEFAULT 'draft',
    run_by CHAR(36) NOT NULL,
    paid_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (run_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Create payroll_run_items table
CREATE TABLE IF NOT EXISTS payroll_run_items (
    id CHAR(36) PRIMARY KEY,
    run_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    FOREIGN KEY (run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;