-- HRM Loans: SP-write / View-read stack for fact_loans.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/loans_sp_views.sql

USE db_abos_v0.1;

CREATE OR REPLACE VIEW vw_loans AS
SELECT
    l.id                 AS id,
    l.id                 AS loanId,
    l.tenant_id          AS tenant_id,
    l.employee_id        AS employeeId,
    l.employee_name      AS employeeName,
    l.loan_amount        AS loanAmount,
    l.currency           AS currency,
    l.no_of_installments AS installments,
    l.monthly_amount     AS monthlyAmount,
    l.start_date         AS startDate,
    l.status             AS status,
    l.purpose            AS purpose,
    l.notes              AS notes,
    l.created_at         AS createdAt,
    l.updated_at         AS updatedAt
FROM fact_loans l
WHERE l.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_loan;
DROP PROCEDURE IF EXISTS sp_update_loan;
DROP PROCEDURE IF EXISTS sp_soft_delete_loan;

DELIMITER $$

CREATE PROCEDURE sp_create_loan(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_loan_id       BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    DECLARE v_installments INT;
    DECLARE v_monthly DECIMAL(15,2);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_loan_amount IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan amount 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');

    SET v_installments = COALESCE(NULLIF(p_installments, 0), 1);
    SET v_monthly = COALESCE(p_monthly, ROUND(p_loan_amount / v_installments, 2));

    INSERT INTO fact_loans (
        tenant_id, employee_id, employee_name, loan_amount, currency, no_of_installments,
        monthly_amount, start_date, status, purpose, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_loan_amount, COALESCE(p_currency, 'USD'), v_installments,
        v_monthly, p_start_date, COALESCE(p_status, 'Pending'), p_purpose, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_loan_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_loan(
    IN  p_tenant_id     INT,
    IN  p_loan_id       BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_loans
        WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan not found';
    END IF;

    UPDATE fact_loans SET
        employee_id        = COALESCE(p_employee_id, employee_id),
        employee_name      = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        loan_amount        = COALESCE(p_loan_amount, loan_amount),
        currency           = COALESCE(p_currency, currency),
        no_of_installments = COALESCE(NULLIF(p_installments, 0), no_of_installments),
        monthly_amount     = COALESCE(p_monthly, monthly_amount),
        start_date         = COALESCE(p_start_date, start_date),
        status             = COALESCE(p_status, status),
        purpose            = COALESCE(p_purpose, purpose),
        notes              = COALESCE(p_notes, notes),
        updated_at         = NOW(),
        updated_by         = p_updated_by
    WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

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

DELIMITER ;