-- Create Staff Salaries Table
CREATE TABLE IF NOT EXISTS `staff_salaries` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `staff_id` INT NOT NULL,
    `salary_month` VARCHAR(7) NOT NULL, -- Format: YYYY-MM
    `gross_amount` DECIMAL(15, 2) NOT NULL,
    `basic_salary` DECIMAL(15, 2) NOT NULL,
    `allowances` DECIMAL(15, 2) DEFAULT 0,
    `deductions` JSON, -- Store as JSON: {"description": amount}
    `total_deductions` DECIMAL(15, 2) DEFAULT 0,
    `net_amount` DECIMAL(15, 2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'XAF',
    `status` ENUM('Draft', 'Approved', 'Paid', 'Cancelled') DEFAULT 'Draft',
    `payment_method` ENUM('Cash', 'Bank Transfer', 'Check', 'Mobile Money') DEFAULT 'Bank Transfer',
    `payment_date` DATE,
    `notes` TEXT,
    `created_by` INT,
    `approved_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`staff_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `staff_id` (`staff_id`),
    INDEX `salary_month` (`salary_month`),
    INDEX `status` (`status`),
    UNIQUE KEY `unique_staff_month` (`staff_id`, `salary_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
