-- HRM Payroll: SP-write / View-read stack for fact_payroll.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/payroll_sp_views.sql

USE db_abos_v0.1;

CREATE OR REPLACE VIEW vw_payroll AS
SELECT
    p.id            AS id,
    p.id            AS payrollId,
    p.tenant_id     AS tenant_id,
    p.employee_id   AS employeeId,
    p.employee_name AS employeeName,
    p.month         AS month,
    p.basic_salary  AS basicSalary,
    p.allowances    AS allowances,
    p.deductions    AS deductions,
    p.tax           AS tax,
    p.net_pay       AS netPay,
    p.currency      AS currency,
    p.status        AS status,
    p.pay_date      AS payDate,
    p.created_at    AS createdAt,
    p.updated_at    AS updatedAt
FROM fact_payroll p
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_payroll;
DROP PROCEDURE IF EXISTS sp_update_payroll;
DROP PROCEDURE IF EXISTS sp_soft_delete_payroll;

DELIMITER $$

CREATE PROCEDURE sp_create_payroll(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_month         VARCHAR(20),
    IN  p_basic_salary  DECIMAL(15,2),
    IN  p_allowances    DECIMAL(15,2),
    IN  p_deductions    DECIMAL(15,2),
    IN  p_tax           DECIMAL(15,2),
    IN  p_net_pay       DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_status        VARCHAR(20),
    IN  p_pay_date      DATE,
    IN  p_created_by    INT,
    OUT p_payroll_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_basic_salary IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Basic salary is required';
    END IF;
    IF p_net_pay IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Net pay 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_payroll (
        tenant_id, employee_id, employee_name, month, basic_salary, allowances,
        deductions, tax, net_pay, currency, status, pay_date,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_month, p_basic_salary, COALESCE(p_allowances, 0),
        COALESCE(p_deductions, 0), COALESCE(p_tax, 0), p_net_pay, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Draft'), p_pay_date,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_payroll_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_payroll(
    IN  p_tenant_id     INT,
    IN  p_payroll_id    BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_month         VARCHAR(20),
    IN  p_basic_salary  DECIMAL(15,2),
    IN  p_allowances    DECIMAL(15,2),
    IN  p_deductions    DECIMAL(15,2),
    IN  p_tax           DECIMAL(15,2),
    IN  p_net_pay       DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_status        VARCHAR(20),
    IN  p_pay_date      DATE,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_payroll
        WHERE id = p_payroll_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payroll record not found';
    END IF;

    UPDATE fact_payroll SET
        employee_id   = COALESCE(p_employee_id, employee_id),
        employee_name = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        month         = COALESCE(p_month, month),
        basic_salary  = COALESCE(p_basic_salary, basic_salary),
        allowances    = COALESCE(p_allowances, allowances),
        deductions    = COALESCE(p_deductions, deductions),
        tax           = COALESCE(p_tax, tax),
        net_pay       = COALESCE(p_net_pay, net_pay),
        currency      = COALESCE(p_currency, currency),
        status        = COALESCE(p_status, status),
        pay_date      = COALESCE(p_pay_date, pay_date),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_payroll_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

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

DELIMITER ;