USE db_abos_v0.1;

-- =============================================================================================
-- fix_opportunity_company_less_deals.sql — sp_create_opportunity never accepted a way to
-- snapshot who a deal is for when there's no CRM company/contact record (e.g. a web-store
-- order or a website service request). fact_opportunities already has client_name_text/
-- email/phone columns for exactly this ("company-less deal") case, but no INSERT ever
-- populated them. Adds three new IN params and writes them straight through; also widens
-- v_full_name's fallback so a company-less deal gets a real display name instead of just
-- the title. company_id/contact_id were already nullable here — no change needed to those.
--
-- Safe to re-run: DROP + 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_client_name_text VARCHAR(255),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(50),
    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 = COALESCE(p_client_name_text, 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 = COALESCE(p_client_name_text, 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,
        client_name_text, email, phone, 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_client_name_text, p_email, p_phone, p_created_by
    );

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

DELIMITER ;