-- Create Pay Groups Table
CREATE TABLE IF NOT EXISTS `pay_groups` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `salary_month` VARCHAR(7) NOT NULL,
    `total_gross` DECIMAL(15, 2) NOT NULL,
    `total_bonus` DECIMAL(15, 2) DEFAULT 0,
    `bonus_type` ENUM('Amount', 'Percentage') DEFAULT 'Amount',
    `total_deductions` DECIMAL(15, 2) DEFAULT 0,
    `deductions_type` ENUM('Amount', 'Percentage') DEFAULT 'Amount',
    `total_net` DECIMAL(15, 2) NOT NULL,
    `status` ENUM('Complete', 'Incomplete') DEFAULT 'Incomplete',
    `partial_amount` DECIMAL(15, 2),
    `partial_type` ENUM('Amount', 'Percentage'),
    `currency` VARCHAR(3) DEFAULT 'XAF',
    `notes` TEXT,
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `salary_month` (`salary_month`),
    INDEX `status` (`status`),
    UNIQUE KEY `unique_month` (`salary_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create Pay Group Members Table
CREATE TABLE IF NOT EXISTS `pay_group_members` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `pay_group_id` INT NOT NULL,
    `salary_id` INT NOT NULL,
    `staff_id` INT,
    `staff_name` VARCHAR(255),
    `gross_amount` DECIMAL(15, 2),
    `bonus_share` DECIMAL(15, 2) DEFAULT 0,
    `deductions_share` DECIMAL(15, 2) DEFAULT 0,
    `final_amount` DECIMAL(15, 2),
    `paid_amount` DECIMAL(15, 2) DEFAULT 0,
    `payment_status` ENUM('Pending', 'Paid') DEFAULT 'Pending',
    `payment_date` DATETIME,
    FOREIGN KEY (`pay_group_id`) REFERENCES `pay_groups`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`salary_id`) REFERENCES `staff_salaries`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`staff_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `pay_group_id` (`pay_group_id`),
    INDEX `payment_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
