-- Create Staff Table
-- Staff members with detailed information separate from users

CREATE TABLE IF NOT EXISTS staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE COMMENT 'Reference to users table',
    staff_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique staff identifier',
    position ENUM('Guard', 'Controller', 'Manager', 'Accountant', 'CEO', 'VCEO', 'Marketer', 'Other Staff') DEFAULT 'Guard' COMMENT 'Staff position/role',
    zone ENUM('Buea', 'Limbe', 'Douala', 'Tiko', 'Yaounde', 'Bamenda', 'Kumba') COMMENT 'Assigned zone/location',
    remunerations LONGTEXT COMMENT 'JSON array of remuneration items with amounts',
    total_monthly_pay DECIMAL(12, 2) DEFAULT 0 COMMENT 'Total monthly salary',
    contract_date DATE COMMENT 'Employment contract start date',
    contract_end DATE COMMENT 'Employment contract end date',
    status ENUM('Probation', 'Volunteer', 'Permanent', 'Inactive') DEFAULT 'Probation' COMMENT 'Employment status',
    notes LONGTEXT COMMENT 'Additional notes',
    photo_url VARCHAR(255) COMMENT 'Path to staff photo',
    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,
    INDEX idx_staff_id (staff_id),
    INDEX idx_position (position),
    INDEX idx_zone (zone),
    INDEX idx_status (status),
    INDEX idx_contract_dates (contract_date, contract_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Staff management with detailed employment information';
