-- ═══════════════════════════════════════════════════════════════════════
-- Integrated Payroll  (add-on: payroll)
-- Ghana SSNIT (employee 5.5%, employer 13%) + progressive PAYE, payslips.
-- Historic runs are immutable once finalized. Idempotent; app self-heals via
-- includes/payroll_lib.php.
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `payroll_staff_pay` (
    `staff_id`            INT PRIMARY KEY,
    `basic_salary`        DECIMAL(12,2) NOT NULL DEFAULT 0,
    `transport_allowance` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `housing_allowance`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `other_allowance`     DECIMAL(12,2) NOT NULL DEFAULT 0,
    `other_deductions`    DECIMAL(12,2) NOT NULL DEFAULT 0,
    `tax_relief`          DECIMAL(12,2) NOT NULL DEFAULT 0,
    `ssnit_exempt`        TINYINT(1)   NOT NULL DEFAULT 0,
    `active`              TINYINT(1)   NOT NULL DEFAULT 1,
    `updated_at`          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `payroll_runs` (
    `id`           INT AUTO_INCREMENT PRIMARY KEY,
    `period`       VARCHAR(7)  NOT NULL,
    `status`       ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
    `note`         VARCHAR(200) NULL,
    `created_by`   INT          NULL,
    `created_at`   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `finalized_at` DATETIME     NULL,
    UNIQUE KEY `uniq_period` (`period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `payroll_items` (
    `id`               INT AUTO_INCREMENT PRIMARY KEY,
    `run_id`           INT NOT NULL,
    `staff_id`         INT NOT NULL,
    `staff_name`       VARCHAR(120) NULL,
    `basic`            DECIMAL(12,2) NOT NULL DEFAULT 0,
    `allowances`       DECIMAL(12,2) NOT NULL DEFAULT 0,
    `gross`            DECIMAL(12,2) NOT NULL DEFAULT 0,
    `ssnit_employee`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `ssnit_employer`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `taxable`          DECIMAL(12,2) NOT NULL DEFAULT 0,
    `paye`             DECIMAL(12,2) NOT NULL DEFAULT 0,
    `other_deductions` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `net`              DECIMAL(12,2) NOT NULL DEFAULT 0,
    `bank_name`        VARCHAR(100) NULL,
    `bank_account`     VARCHAR(50)  NULL,
    `ssnit_number`     VARCHAR(50)  NULL,
    `created_at`       DATETIME     DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_run` (`run_id`), KEY `idx_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
