-- HRM Attendance: SP-write / View-read stack for fact_attendance.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/attendance_sp_views.sql

USE db_abos_v0.1;

CREATE OR REPLACE VIEW vw_attendance AS
SELECT
    a.id            AS id,
    a.id            AS attendanceId,
    a.tenant_id     AS tenant_id,
    a.employee_id   AS employeeId,
    a.employee_name AS employeeName,
    a.date          AS date,
    a.status        AS status,
    a.hours_worked  AS hours,
    a.check_in      AS checkIn,
    a.check_out     AS checkOut,
    a.notes         AS notes,
    a.created_at    AS createdAt,
    a.updated_at    AS updatedAt
FROM fact_attendance a
WHERE a.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_attendance;
DROP PROCEDURE IF EXISTS sp_update_attendance;
DROP PROCEDURE IF EXISTS sp_soft_delete_attendance;

DELIMITER $$

CREATE PROCEDURE sp_create_attendance(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_attendance_id BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_date IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance date is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name
          FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id
         LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    INSERT INTO fact_attendance (
        tenant_id, employee_id, employee_name, date, status, hours_worked,
        check_in, check_out, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_date,
        COALESCE(p_status, 'Present'), p_hours,
        NULLIF(p_check_in, ''), NULLIF(p_check_out, ''), p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_attendance_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_attendance(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_attendance
        WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance record not found';
    END IF;

    UPDATE fact_attendance SET
        employee_id   = COALESCE(p_employee_id, employee_id),
        employee_name = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        date          = COALESCE(p_date, date),
        status        = COALESCE(p_status, status),
        hours_worked  = COALESCE(p_hours, hours_worked),
        check_in      = COALESCE(NULLIF(p_check_in, ''), check_in),
        check_out     = COALESCE(NULLIF(p_check_out, ''), check_out),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_attendance(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_deleted_by    INT
)
proc: BEGIN
    UPDATE fact_attendance SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;