-- =============================================================================================
-- fix_crm_commission_procedure.sql — sp_create_commission_on_deal_close was called from Java
-- (CrmProcedureRepository.closeDeal -> OpportunityService.closeDeal) with a completely different
-- parameter shape than the procedure actually declared: Java sent
-- (p_opportunity_id, p_agent_id, p_outcome, p_changed_by) + expected an OUT p_commission_id,
-- while the procedure was (p_tenant_id, p_opportunity_id, p_agent_id, p_period, p_updated_by)
-- with NO out parameter. Since JDBC binds positionally, Java's opportunity_id landed in the
-- procedure's p_tenant_id slot, and the "outcome" string (e.g. "Closed Won") landed in the
-- p_agent_id slot (INT) — this almost certainly threw a type-conversion error on every call,
-- meaning the CRM "Close Deal" action was broken.
--
-- Fix direction (per product decision): rewrite the procedure to match Java's existing intent
-- (CloseDealPayload only ever sent opportunityId/outcome/agentId — never a "period" — so period
-- is now derived server-side as the current YYYY-MM instead of being a caller-supplied value).
--   - Adds p_tenant_id (first param, tenant-scopes both lookups — closes the audit finding that
--     the original procedure's opportunity/agent lookups had no tenant filter).
--   - Adds SIGNAL SQLSTATE guards: fail cleanly if the opportunity or agent isn't found, instead
--     of silently proceeding with NULL values and inserting a corrupt fact_commissions row.
--   - Renames p_period -> p_outcome: the opportunity's new status is now whatever outcome the
--     caller passed (defaulting to 'Deal_Won' only if blank), instead of being hardcoded.
--   - Adds OUT p_commission_id so the Java caller gets back the ID it already expected.
--
-- Safe to re-run: DROP + CREATE.
-- =============================================================================================

USE db_abos_v0.1;

DROP PROCEDURE IF EXISTS `sp_create_commission_on_deal_close`;

DELIMITER $$

CREATE PROCEDURE `sp_create_commission_on_deal_close`(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_agent_id INT,
    IN p_outcome VARCHAR(50),
    IN p_updated_by INT,
    OUT p_commission_id BIGINT
)
BEGIN
    DECLARE v_deal_value DECIMAL(15,2);
    DECLARE v_old_status VARCHAR(50);
    DECLARE v_agent_name VARCHAR(255);
    DECLARE v_rate DECIMAL(5,2);
    DECLARE v_earned DECIMAL(15,2);
    DECLARE v_new_stage VARCHAR(50);
    DECLARE v_new_status VARCHAR(50);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

    START TRANSACTION;

    SELECT deal_value, status INTO v_deal_value, v_old_status
    FROM fact_opportunities WHERE id = p_opportunity_id AND tenant_id = p_tenant_id;
    IF v_deal_value IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Opportunity not found';
    END IF;

    SELECT CONCAT(first_name, ' ', last_name), commission_rate INTO v_agent_name, v_rate
    FROM dim_sales_agents WHERE id = p_agent_id AND tenant_id = p_tenant_id;
    IF v_agent_name IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sales agent not found';
    END IF;

    -- p_outcome (e.g. "Closed Won"/"Closed Lost") is a fact_opportunities.stage value, NOT a
    -- status value — status is the narrower enum('New','Contacted','Qualified','Lost','Won').
    -- The original procedure hardcoded status='Deal_Won', which was never a valid enum value
    -- either; derive both columns correctly here instead.
    SET v_new_stage = CASE WHEN p_outcome IS NOT NULL AND p_outcome <> '' THEN p_outcome ELSE 'Closed Won' END;
    SET v_new_status = CASE WHEN v_new_stage LIKE '%Lost%' THEN 'Lost' ELSE 'Won' END;
    SET v_earned = v_deal_value * (v_rate / 100);

    UPDATE fact_opportunities SET status = v_new_status, stage = v_new_stage, updated_by = p_updated_by
    WHERE id = p_opportunity_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_opportunity_logs (tenant_id, opportunity_id, old_status, new_status, changed_by)
    VALUES (p_tenant_id, p_opportunity_id, v_old_status, v_new_status, p_updated_by);

    INSERT INTO fact_commissions (
        tenant_id, agent_id, agent_name, period, deal_ref, gross_amount, rate, earned, total, status, created_by
    ) VALUES (
        p_tenant_id, p_agent_id, v_agent_name, DATE_FORMAT(CURDATE(), '%Y-%m'),
        CAST(p_opportunity_id AS CHAR), v_deal_value, v_rate, v_earned, v_earned, 'Pending', p_updated_by
    );

    SET p_commission_id = LAST_INSERT_ID();
    COMMIT;
END$$

DELIMITER ;