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

CREATE TABLE IF NOT EXISTS `leave_types` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(10)         NOT NULL,
    `days_per_year`         DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `is_paid`               TINYINT(1)          NOT NULL DEFAULT 1,
    `requires_approval`     TINYINT(1)          NOT NULL DEFAULT 1,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leave_balances` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `leave_type_id`         BIGINT UNSIGNED     NOT NULL,
    `year`                  SMALLINT UNSIGNED   NOT NULL,
    `entitled`              DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `used`                  DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employee_type_year` (`employee_id`, `leave_type_id`, `year`),
    KEY `employee_id` (`employee_id`),
    KEY `leave_type_id` (`leave_type_id`),
    KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leave_requests` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `leave_type_id`         BIGINT UNSIGNED     NOT NULL,
    `start_date`            DATE                NOT NULL,
    `end_date`              DATE                NOT NULL,
    `days`                  DECIMAL(5,1)        NOT NULL DEFAULT 0,
    `reason`                TEXT                NULL,
    `status`                ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `rejection_reason`      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_id` (`employee_id`),
    KEY `leave_type_id` (`leave_type_id`),
    KEY `status` (`status`),
    KEY `start_date` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `holidays` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `date`                  DATE                NOT NULL,
    `is_recurring`          TINYINT(1)          NOT NULL DEFAULT 0,
    `branch_id`             BIGINT UNSIGNED     NULL,
    `description`           TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `date` (`date`),
    KEY `branch_id` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default leave types
INSERT INTO `leave_types` (`name`, `code`, `days_per_year`, `is_paid`, `requires_approval`, `description`, `status`, `created_at`)
SELECT 'Annual Leave', 'AL', 30, 1, 1, 'Standard annual vacation entitlement', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `leave_types` WHERE `code` = 'AL');

INSERT INTO `leave_types` (`name`, `code`, `days_per_year`, `is_paid`, `requires_approval`, `description`, `status`, `created_at`)
SELECT 'Sick Leave', 'SL', 15, 1, 1, 'Medical or health-related absence', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `leave_types` WHERE `code` = 'SL');

INSERT INTO `leave_types` (`name`, `code`, `days_per_year`, `is_paid`, `requires_approval`, `description`, `status`, `created_at`)
SELECT 'Casual Leave', 'CL', 7, 1, 1, 'Short-notice personal leave', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `leave_types` WHERE `code` = 'CL');

INSERT INTO `leave_types` (`name`, `code`, `days_per_year`, `is_paid`, `requires_approval`, `description`, `status`, `created_at`)
SELECT 'Unpaid Leave', 'UL', 0, 0, 1, 'Leave without pay', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `leave_types` WHERE `code` = 'UL');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 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 'leave_types.%' OR p.slug LIKE 'leave_balances.%'
       OR p.slug LIKE 'leave_requests.%' OR p.slug LIKE 'leave_calendar.%'
       OR p.slug LIKE 'holidays.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
