-- Payroll Management tables for HRM module (Bahrain / Gulf)
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS

CREATE TABLE IF NOT EXISTS `payroll_settings` (
    `id`                            TINYINT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `sio_cap_amount`                DECIMAL(12,3)       NOT NULL DEFAULT 4000.000,
    `sio_bahraini_employer_rate`    DECIMAL(5,2)        NOT NULL DEFAULT 18.00,
    `sio_bahraini_employee_rate`    DECIMAL(5,2)        NOT NULL DEFAULT 8.00,
    `sio_expat_employer_rate`       DECIMAL(5,2)        NOT NULL DEFAULT 3.00,
    `sio_expat_employee_rate`       DECIMAL(5,2)        NOT NULL DEFAULT 1.00,
    `eosb_rate_first_3_years`       DECIMAL(5,2)        NOT NULL DEFAULT 4.20,
    `eosb_rate_after_3_years`       DECIMAL(5,2)        NOT NULL DEFAULT 8.40,
    `eosb_effective_date`           DATE                NOT NULL DEFAULT '2024-03-01',
    `working_days_per_month`        TINYINT UNSIGNED    NOT NULL DEFAULT 26,
    `daily_work_hours`              DECIMAL(4,2)        NOT NULL DEFAULT 8.00,
    `overtime_day_multiplier`       DECIMAL(4,2)        NOT NULL DEFAULT 1.25,
    `overtime_night_multiplier`     DECIMAL(4,2)        NOT NULL DEFAULT 1.50,
    `payment_day`                   TINYINT UNSIGNED    NOT NULL DEFAULT 25,
    `wps_employer_id`               VARCHAR(50)         NULL,
    `created_at`                    DATETIME            NULL,
    `updated_at`                    DATETIME            NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payroll_settings` (
    `sio_cap_amount`, `sio_bahraini_employer_rate`, `sio_bahraini_employee_rate`,
    `sio_expat_employer_rate`, `sio_expat_employee_rate`,
    `eosb_rate_first_3_years`, `eosb_rate_after_3_years`, `eosb_effective_date`,
    `working_days_per_month`, `daily_work_hours`,
    `overtime_day_multiplier`, `overtime_night_multiplier`, `payment_day`,
    `created_at`, `updated_at`
)
SELECT 4000.000, 18.00, 8.00, 3.00, 1.00, 4.20, 8.40, '2024-03-01',
       26, 8.00, 1.25, 1.50, 25, NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_settings` LIMIT 1);

CREATE TABLE IF NOT EXISTS `payroll_components` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`                  VARCHAR(20)         NOT NULL,
    `name`                  VARCHAR(100)        NOT NULL,
    `type`                  ENUM('earning','deduction','employer_cost') NOT NULL,
    `is_sio_applicable`     TINYINT(1)          NOT NULL DEFAULT 0,
    `is_eosb_applicable`    TINYINT(1)          NOT NULL DEFAULT 0,
    `is_system`             TINYINT(1)          NOT NULL DEFAULT 0,
    `sort_order`            SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'BASIC', 'Basic Salary', 'earning', 1, 1, 1, 10, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'BASIC');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'SOCIAL', 'Social Allowance', 'earning', 1, 1, 1, 20, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'SOCIAL');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'HOUSING', 'Housing Allowance', 'earning', 0, 0, 1, 30, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'HOUSING');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'TRANSPORT', 'Transport Allowance', 'earning', 0, 0, 1, 40, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'TRANSPORT');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'OTHER', 'Other Allowances', 'earning', 0, 0, 1, 50, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'OTHER');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'OT_DAY', 'Overtime (Day)', 'earning', 0, 0, 1, 60, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'OT_DAY');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'OT_NIGHT', 'Overtime (Night/Holiday)', 'earning', 0, 0, 1, 70, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'OT_NIGHT');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'SIO_EE', 'SIO (Employee)', 'deduction', 0, 0, 1, 100, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'SIO_EE');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'ABSENCE', 'Absence Deduction', 'deduction', 0, 0, 1, 110, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'ABSENCE');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'UNPAID_LV', 'Unpaid Leave Deduction', 'deduction', 0, 0, 1, 120, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'UNPAID_LV');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'SIO_ER', 'SIO (Employer)', 'employer_cost', 0, 0, 1, 200, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'SIO_ER');

INSERT INTO `payroll_components` (`code`, `name`, `type`, `is_sio_applicable`, `is_eosb_applicable`, `is_system`, `sort_order`, `status`, `created_at`)
SELECT 'EOSB', 'End-of-Service (Employer)', 'employer_cost', 0, 0, 1, 210, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payroll_components` WHERE `code` = 'EOSB');

CREATE TABLE IF NOT EXISTS `employee_salary_structures` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `effective_from`        DATE                NOT NULL,
    `effective_to`          DATE                NULL,
    `basic_salary`          DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `social_allowance`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `housing_allowance`     DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `transport_allowance`   DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `other_allowances`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `payment_method`        ENUM('bank','cash') NOT NULL DEFAULT 'bank',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `employee_effective` (`employee_id`, `effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_runs` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `period_year`           SMALLINT UNSIGNED   NOT NULL,
    `period_month`          TINYINT UNSIGNED    NOT NULL,
    `title`                 VARCHAR(150)        NULL,
    `status`                ENUM('draft','calculated','approved','paid','locked') NOT NULL DEFAULT 'draft',
    `run_date`              DATE                NULL,
    `notes`                 TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `paid_at`               DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `period` (`period_year`, `period_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_items` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `payroll_run_id`        BIGINT UNSIGNED     NOT NULL,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `days_worked`           DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `days_absent`           DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `unpaid_leave_days`     DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `overtime_minutes_day`  INT UNSIGNED        NOT NULL DEFAULT 0,
    `overtime_minutes_night` INT UNSIGNED       NOT NULL DEFAULT 0,
    `gross_earnings`        DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `total_deductions`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `net_pay`               DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `employer_sio`          DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `employer_eosb`         DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `employer_total_cost`   DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `status`                ENUM('draft','approved','paid') NOT NULL DEFAULT 'draft',
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `run_employee` (`payroll_run_id`, `employee_id`),
    KEY `payroll_run_id` (`payroll_run_id`),
    KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_item_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `payroll_item_id`       BIGINT UNSIGNED     NOT NULL,
    `component_id`          BIGINT UNSIGNED     NOT NULL,
    `amount`                DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `notes`                 VARCHAR(255)        NULL,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `payroll_item_id` (`payroll_item_id`),
    KEY `component_id` (`component_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payroll_settlements` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `termination_date`      DATE                NOT NULL,
    `last_working_day`      DATE                NOT NULL,
    `years_of_service`      DECIMAL(6,2)        NOT NULL DEFAULT 0,
    `unused_leave_days`     DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `leave_encashment`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `legacy_eosb`           DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `notice_pay`            DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `last_salary`           DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `other_entitlements`    DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `total_deductions`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `total_settlement`      DECIMAL(12,3)       NOT NULL DEFAULT 0.000,
    `status`                ENUM('draft','approved','paid') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `paid_at`               DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `employee_id` (`employee_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_settings', 'payroll_settings.view', 'View Payroll Settings', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_settings.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_settings', 'payroll_settings.edit', 'Edit Payroll Settings', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_settings.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_salary', 'payroll_salary.view', 'View Salary Structures', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_salary.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_salary', 'payroll_salary.create', 'Create Salary Structures', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_salary.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_salary', 'payroll_salary.edit', 'Edit Salary Structures', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_salary.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_runs', 'payroll_runs.view', 'View Payroll Runs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_runs.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_runs', 'payroll_runs.create', 'Create Payroll Runs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_runs.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_runs', 'payroll_runs.edit', 'Edit Payroll Runs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_runs.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_runs', 'payroll_runs.approve', 'Approve Payroll Runs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_runs.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_runs', 'payroll_runs.delete', 'Delete Payroll Runs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_runs.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_payslips', 'payroll_payslips.view', 'View Payslips', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_payslips.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_payslips', 'payroll_payslips.export', 'Export Payslips / WPS', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_payslips.export');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_payslips', 'payroll_payslips.print', 'Print Payslips', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_payslips.print');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_reports', 'payroll_reports.view', 'View Payroll Reports', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_reports.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_settlements', 'payroll_settlements.view', 'View Final Settlements', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_settlements.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_settlements', 'payroll_settlements.create', 'Create Final Settlements', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_settlements.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payroll_settlements', 'payroll_settlements.approve', 'Approve Final Settlements', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payroll_settlements.approve');

-- Grant all new permissions to Administrator role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND (p.slug LIKE 'payroll_settings.%' OR p.slug LIKE 'payroll_salary.%'
       OR p.slug LIKE 'payroll_runs.%' OR p.slug LIKE 'payroll_payslips.%'
       OR p.slug LIKE 'payroll_reports.%' OR p.slug LIKE 'payroll_settlements.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
