-- Attendance 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 `attendance_shifts` (
    `id`                        BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                      VARCHAR(100)        NOT NULL,
    `code`                      VARCHAR(10)         NOT NULL,
    `start_time`                TIME                NOT NULL,
    `end_time`                  TIME                NOT NULL,
    `grace_period_minutes`      SMALLINT UNSIGNED   NOT NULL DEFAULT 15,
    `early_exit_grace_minutes`  SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `overtime_after_minutes`    SMALLINT UNSIGNED   NOT NULL DEFAULT 30,
    `break_minutes`             SMALLINT UNSIGNED   NOT NULL DEFAULT 60,
    `auto_punch`                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 `employee_shift_assignments` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`       BIGINT UNSIGNED     NOT NULL,
    `shift_id`          BIGINT UNSIGNED     NOT NULL,
    `effective_from`    DATE                NOT NULL,
    `effective_to`      DATE                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`),
    KEY `shift_id` (`shift_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance_records` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `shift_id`              BIGINT UNSIGNED     NULL,
    `attendance_date`       DATE                NOT NULL,
    `clock_in`              DATETIME            NULL,
    `clock_out`             DATETIME            NULL,
    `status`                ENUM('present','absent','late','half_day','on_leave','holiday','auto_present') NOT NULL DEFAULT 'absent',
    `late_minutes`          SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `early_exit_minutes`    SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `overtime_minutes`      SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `work_minutes`          SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    `source`                ENUM('clock','manual','auto','correction') NOT NULL DEFAULT 'clock',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employee_date` (`employee_id`, `attendance_date`),
    KEY `attendance_date` (`attendance_date`),
    KEY `status` (`status`),
    KEY `shift_id` (`shift_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance_corrections` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `attendance_record_id`  BIGINT UNSIGNED     NOT NULL,
    `original_clock_in`     DATETIME            NULL,
    `original_clock_out`    DATETIME            NULL,
    `original_status`       VARCHAR(20)         NULL,
    `corrected_clock_in`    DATETIME            NULL,
    `corrected_clock_out`   DATETIME            NULL,
    `corrected_status`      VARCHAR(20)         NULL,
    `reason`                TEXT                NOT NULL,
    `status`                ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `requested_by`          BIGINT UNSIGNED     NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `rejection_reason`      TEXT                NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `attendance_record_id` (`attendance_record_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default shifts
INSERT INTO `attendance_shifts` (`name`, `code`, `start_time`, `end_time`, `grace_period_minutes`, `break_minutes`, `auto_punch`, `description`, `status`, `created_at`)
SELECT 'General Day Shift', 'DAY', '08:00:00', '17:00:00', 15, 60, 1, 'Standard 8 AM – 5 PM office shift', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `attendance_shifts` WHERE `code` = 'DAY');

INSERT INTO `attendance_shifts` (`name`, `code`, `start_time`, `end_time`, `grace_period_minutes`, `break_minutes`, `auto_punch`, `description`, `status`, `created_at`)
SELECT 'Morning Shift', 'MORN', '06:00:00', '14:00:00', 10, 30, 1, 'Early morning operations shift', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `attendance_shifts` WHERE `code` = 'MORN');

INSERT INTO `attendance_shifts` (`name`, `code`, `start_time`, `end_time`, `grace_period_minutes`, `break_minutes`, `auto_punch`, `description`, `status`, `created_at`)
SELECT 'Evening Shift', 'EVE', '14:00:00', '22:00:00', 10, 30, 1, 'Afternoon/evening operations shift', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `attendance_shifts` WHERE `code` = 'EVE');

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'attendance', 'attendance.clock', 'Clock In/Out Employees', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'attendance.clock');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'attendance', 'attendance.manual', 'Manual Attendance Entry', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'attendance.manual');

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'attendance', 'attendance.auto_punch', 'Run Auto Punch', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'attendance.auto_punch');

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'attendance_corrections', 'attendance_corrections.create', 'Request Attendance Corrections', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'attendance_corrections.create');

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'attendance_shifts', 'attendance_shifts.delete', 'Delete Attendance Shifts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'attendance_shifts.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 'attendance.%' OR p.slug LIKE 'attendance_corrections.%' OR p.slug LIKE 'attendance_shifts.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
