USE db_abos_v0.1;

-- Fixes sp_create_opportunity to persist fact_opportunities.stage (2026-07-28).
-- fact_opportunities already has a `stage` enum('Discovery','Proposal','Negotiation',
-- 'Closed Won','Closed Lost') column distinct from `status` enum('New','Contacted','Qualified',
-- 'Lost','Won') — but the stored procedure never accepted or persisted it, so the Deals UI's
-- pipeline stage was silently dropped on create (and the frontend was additionally sending the
-- stage value into `status`, which fails validation since the two enums don't overlap).
-- Safe to re-run: DROP PROCEDURE IF EXISTS + CREATE.

DROP PROCEDURE IF EXISTS sp_create_opportunity;

DELIMITER $$

CREATE PROCEDURE sp_create_opportunity(
    IN p_tenant_id INT,
    IN p_company_id INT,
    IN p_contact_id INT,
    IN p_title VARCHAR(255),
    IN p_status VARCHAR(50),
    IN p_stage VARCHAR(50),
    IN p_source VARCHAR(100),
    IN p_probability INT,
    IN p_deal_value DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_assigned_to INT,
    IN p_expected_close_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_opportunity_id BIGINT
)
BEGIN
    DECLARE v_full_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_full_name = p_title;
    IF p_contact_id IS NOT NULL THEN
        SELECT CONCAT(first_name, ' ', last_name) INTO v_full_name
        FROM dim_contacts
        WHERE id = p_contact_id AND tenant_id = p_tenant_id;
        IF v_full_name IS NULL THEN
            SET v_full_name = p_title;
        END IF;
    END IF;

    INSERT INTO fact_opportunities (
        tenant_id, company_id, contact_id, full_name, title, deal_title, status, stage, source,
        probability, deal_value, currency, assigned_to, expected_close_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_company_id, p_contact_id, v_full_name, p_title, p_title, p_status, p_stage, p_source,
        p_probability, p_deal_value, p_currency, p_assigned_to, p_expected_close_date, p_notes, p_created_by
    );

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

DELIMITER ;