USE db_abos_v0.1;

-- Consolidated fix for QA Bugs #7 (stored-procedure contract drift), #9 (NOT NULL columns never
-- populated), and the schema addition needed for #7's employee-termination fix. Covers the
-- CRM/Finance core flow (Opportunity, Invoice, Pay) and the HRM/Platform-Admin procedures that
-- need more than a one-parameter fix (see plans/java-fixes-plan.md for the simpler cases that only
-- need a Java-side change, no SQL). Safe to re-run — every ALTER is idempotent and every
-- CREATE PROCEDURE is preceded by DROP PROCEDURE IF EXISTS.
--
-- Pair this with the matching Java changes in plans/java-fixes-plan.md before deploying — the
-- Java *ProcedureRepository classes must declare the exact same parameter list, in the same order,
-- as each procedure below, or SimpleJdbcCall will bind values to the wrong positions.

-- =============================================================================================
-- 1. sp_create_opportunity — was missing p_tenant_id, ignored 6 fields Java already sends, and
--    never populated fact_opportunities.full_name (NOT NULL, no default) — see Bug #9.
-- =============================================================================================
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_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, 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_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 ;

-- =============================================================================================
-- 2. sp_create_invoice_with_ledger — was missing p_tenant_id, ignored 5 fields Java already
--    sends, and never populated fact_invoices.client_name (NOT NULL, no default) — see Bug #9.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_create_invoice_with_ledger;

DELIMITER $$

CREATE PROCEDURE sp_create_invoice_with_ledger(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_wallet_id INT,
    IN p_client_id INT,
    IN p_invoice_no VARCHAR(100),
    IN p_amount DECIMAL(15,2),
    IN p_tax_amount DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_issue_date DATE,
    IN p_due_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_invoice_id BIGINT
)
BEGIN
    DECLARE v_client_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT company_name INTO v_client_name
    FROM dim_companies
    WHERE id = p_client_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_invoices (
        tenant_id, opportunity_id, wallet_id, client_id, invoice_no, client_name, amount,
        tax_amount, currency, status, issue_date, due_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_opportunity_id, p_wallet_id, p_client_id, p_invoice_no, v_client_name,
        p_amount, p_tax_amount, p_currency, 'Draft', p_issue_date, p_due_date, p_notes, p_created_by
    );

    SET p_invoice_id = LAST_INSERT_ID();

    INSERT INTO fact_ledger_entries (
        tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by
    ) VALUES (
        p_tenant_id, p_wallet_id, 'Debit', p_amount + p_tax_amount, 'Invoice', p_invoice_id, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 3. sp_pay_invoice — discovered during Finance re-test (2026-07-12): fact_transactions.description
--    is NOT NULL with no default, and this procedure's INSERT never populated it — same schema-
--    drift pattern as Bug #9. Parameter list is unchanged from the original procedure; only the
--    fact_transactions INSERT is fixed to supply a description.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_pay_invoice;

DELIMITER $$

CREATE PROCEDURE sp_pay_invoice(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_client_wallet_id INT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT (amount + tax_amount) INTO v_total_amount FROM fact_invoices WHERE id = p_invoice_id;

    UPDATE fact_invoices SET status = 'Paid', updated_by = p_updated_by
    WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_transactions (
        tenant_id, from_wallet_id, to_wallet_id, amount, description, reference_entity,
        reference_id, transaction_type, status, created_by
    ) VALUES (
        p_tenant_id, p_client_wallet_id, p_corp_wallet_id, v_total_amount,
        CONCAT('Payment for Invoice #', p_invoice_id), 'Invoice', p_invoice_id, 'Income',
        'Completed', p_updated_by
    );

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_client_wallet_id, 'Debit', v_total_amount, 'Invoice', p_invoice_id, p_updated_by);

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 4. sp_onboard_employee_full — was missing p_tenant_id (had it, but 6 other fields Java sends
--    were dropped: phone, manager, location, currency, password_salt, and department/designation
--    were never resolved from their ID columns to the display-string columns dim_employees also
--    has). Rewritten to accept the full contract OnboardEmployeePayload already sends.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_onboard_employee_full;

DELIMITER $$

CREATE PROCEDURE sp_onboard_employee_full(
    IN p_tenant_id INT,
    IN p_first_name VARCHAR(100),
    IN p_last_name VARCHAR(100),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(50),
    IN p_department_id INT,
    IN p_designation_id INT,
    IN p_contract_type VARCHAR(50),
    IN p_salary DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_join_date DATE,
    IN p_manager VARCHAR(100),
    IN p_location VARCHAR(100),
    IN p_contract_start DATE,
    IN p_contract_end DATE,
    IN p_allowances_json JSON,
    IN p_create_user TINYINT,
    IN p_password_hash VARCHAR(512),
    IN p_password_salt VARCHAR(255),
    IN p_role_id INT,
    IN p_created_by INT,
    OUT p_employee_id INT
)
BEGIN
    DECLARE v_user_id INT DEFAULT NULL;
    DECLARE v_department_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_job_title VARCHAR(100) DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_department_id IS NOT NULL THEN
        SELECT department_name INTO v_department_name
        FROM dim_departments
        WHERE id = p_department_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_designation_id IS NOT NULL THEN
        SELECT title INTO v_job_title
        FROM dim_designations
        WHERE id = p_designation_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_create_user = 1 THEN
        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt, role_id,
            is_enabled, created_by
        ) VALUES (
            p_tenant_id, p_first_name, p_last_name, p_email, p_password_hash, p_password_salt,
            p_role_id, b'1', p_created_by
        );
        SET v_user_id = LAST_INSERT_ID();
    END IF;

    INSERT INTO dim_employees (
        tenant_id, user_id, department_id, designation_id, first_name, last_name, email, phone,
        department, job_title, contract_type, salary, currency, join_date, manager, location,
        created_by
    ) VALUES (
        p_tenant_id, v_user_id, p_department_id, p_designation_id, p_first_name, p_last_name,
        p_email, p_phone, v_department_name, v_job_title, p_contract_type, p_salary, p_currency,
        p_join_date, p_manager, p_location, p_created_by
    );
    SET p_employee_id = LAST_INSERT_ID();

    INSERT INTO dim_employee_contracts (
        tenant_id, employee_id, start_date, end_date, allowances_json, created_by
    ) VALUES (
        p_tenant_id, p_employee_id, p_contract_start, p_contract_end, p_allowances_json, p_created_by
    );

    INSERT INTO dim_wallets (
        tenant_id, wallet_name, owner_type, employee_id, currency, created_by
    ) VALUES (
        p_tenant_id, CONCAT(p_first_name, ' ', p_last_name, ' Wallet'), 'Employee', p_employee_id,
        p_currency, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 5. sp_terminate_employee — dim_employees has no columns to record termination_date/reason;
--    add them (idempotent), then rewrite the procedure to store what TerminateEmployeePayload
--    already sends instead of silently dropping it.
-- =============================================================================================
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employees' AND COLUMN_NAME = 'termination_date'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_employees ADD COLUMN termination_date DATE NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employees' AND COLUMN_NAME = 'termination_reason'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_employees ADD COLUMN termination_reason VARCHAR(500) NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP PROCEDURE IF EXISTS sp_terminate_employee;

DELIMITER $$

CREATE PROCEDURE sp_terminate_employee(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_termination_date DATE,
    IN p_reason VARCHAR(500),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT user_id INTO v_user_id FROM dim_employees WHERE id = p_employee_id;

    UPDATE dim_employees
    SET status = 'Terminated',
        is_deleted = 1,
        termination_date = p_termination_date,
        termination_reason = p_reason,
        updated_by = p_updated_by
    WHERE id = p_employee_id AND tenant_id = p_tenant_id;

    UPDATE dim_wallets
    SET status = 'Frozen', updated_by = p_updated_by
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id;

    IF v_user_id IS NOT NULL THEN
        UPDATE users SET is_enabled = b'0', is_deleted = 1, updated_by = p_updated_by
        WHERE id = v_user_id AND tenant_id = p_tenant_id;

        UPDATE user_api_keys SET is_deleted = 1 WHERE user_id = v_user_id;
    END IF;

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 6. sp_suspend_tenant — Java already sends p_reason/p_suspend_until, but the procedure ignored
--    both. Store them in platform_audit_logs.description (existing JSON audit-trail column)
--    rather than adding new tenants columns.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_suspend_tenant;

DELIMITER $$

CREATE PROCEDURE sp_suspend_tenant(
    IN p_tenant_id INT,
    IN p_reason VARCHAR(500),
    IN p_suspend_until DATE,
    IN p_performed_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE tenants SET status = 'Suspended', updated_by = p_performed_by WHERE id = p_tenant_id;
    UPDATE users SET is_enabled = b'0', status = 'Inactive', updated_by = p_performed_by
    WHERE tenant_id = p_tenant_id;

    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description)
    VALUES (
        p_tenant_id, 'SUSPEND_TENANT', p_performed_by,
        JSON_OBJECT('status', 'Suspended', 'reason', p_reason, 'suspendUntil', p_suspend_until)
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- Not included here (no SQL change needed — Java-only fixes, see plans/java-fixes-plan.md):
--   sp_soft_delete_user, sp_soft_delete_company, sp_link_employee_to_user,
--   sp_update_opportunity_status, sp_assign_role_to_user (Tier 2 — Java just needs to send a
--   parameter it already has, or one already-defined-elsewhere value, the procedures are correct
--   as deployed) and sp_snapshot_tenant_usage (Java needs to match the procedure's simpler
--   single-tenant contract, not the other way around).
-- =============================================================================================