-- ============================================================================================
-- FIX: sp_process_ai_bank_statement — MariaDB compatibility
-- The target server is MariaDB, whose JSON_VALUE() does not support MySQL 8's
-- "RETURNING <type>" clause (it always returns text). Replaced with CAST(JSON_VALUE(...) AS ...)
-- for the three numeric fields (beginningBalance, currentBalance, interestRate).
-- Safe to re-run: DROP PROCEDURE IF EXISTS + CREATE PROCEDURE.
-- This same fix has also been applied in full_schema_snapshot.sql.
-- ============================================================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_process_ai_bank_statement`$$
CREATE PROCEDURE `sp_process_ai_bank_statement`(
    IN  p_statement_json     LONGTEXT,
    IN  p_transactions_json  LONGTEXT,
    IN  p_created_by         INT,
    IN  p_entity_type        VARCHAR(50),
    IN  p_entity_id          BIGINT,
    OUT p_statement_id       BIGINT,
    OUT p_transaction_count  INT
)
proc: BEGIN
    DECLARE v_tenant_id     INT;
    DECLARE v_entity_id     BIGINT;
    DECLARE v_entity_type   VARCHAR(20);
    DECLARE v_account_no    VARCHAR(100);
    DECLARE v_bank_name     VARCHAR(255);
    DECLARE v_entity_label  VARCHAR(255);
    DECLARE v_business_id   INT;

    SET v_tenant_id = @current_tenant_id;
    IF v_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant context (@current_tenant_id) is required';
    END IF;

    -- Selected business scope (nullable). Persisted on the entity + statement so
    -- business-filtered list screens can find these imported records.
    SET v_business_id = @current_business_id;

    IF p_statement_json IS NULL OR JSON_VALID(p_statement_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid statement JSON';
    END IF;
    IF p_transactions_json IS NULL OR JSON_VALID(p_transactions_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transactions JSON';
    END IF;

    -- Normalise entity type to the dim_financial_entities enum ('Bank' | 'Credit Card')
    SET v_entity_type = CASE
        WHEN p_entity_type IN ('CreditCard', 'Credit Card', 'CREDIT_CARD') THEN 'Credit Card'
        ELSE 'Bank'
    END;

    SET v_account_no = JSON_VALUE(p_statement_json, '$.accountNumber');

    -- ── Ensure the bank exists in the global reference list (dim_banks) ──────
    -- The AI extraction may surface a bank/issuer not yet in the Bank Name
    -- dropdown. Register it so it becomes selectable for future entities.
    -- INSERT IGNORE + UNIQUE(name) makes this an idempotent "add if missing".
    SET v_bank_name = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.bankName')), '');
    IF v_bank_name IS NOT NULL THEN
        INSERT IGNORE INTO dim_banks (name) VALUES (v_bank_name);
    END IF;

    -- User-supplied account label/nickname (falls back to bank/holder name below).
    SET v_entity_label = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.entityLabel')), '');

    -- ── Resolve the financial entity ────────────────────────────────────────
    -- 1) explicit entity_id, 2) existing tenant entity by account number, 3) create.
    IF p_entity_id IS NOT NULL THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE id = p_entity_id AND tenant_id = v_tenant_id AND is_deleted = 0
        LIMIT 1;
    END IF;

    IF v_entity_id IS NULL AND v_account_no IS NOT NULL AND v_account_no <> '' THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE tenant_id = v_tenant_id
          AND account_number = v_account_no
          AND entity_type = v_entity_type
          AND is_deleted = 0
        ORDER BY id LIMIT 1;
    END IF;

    IF v_entity_id IS NULL THEN
        INSERT INTO dim_financial_entities (
            tenant_id, business_id, entity_name, entity_type, account_holder_name, account_number,
            bank_or_issuer_name, currency, status, created_by, updated_by, is_deleted
        ) VALUES (
            v_tenant_id,
            v_business_id,
            COALESCE(
                v_entity_label,
                v_bank_name,
                NULLIF(JSON_VALUE(p_statement_json, '$.accountHolderName'), ''),
                'Imported Entity'),
            v_entity_type,
            JSON_VALUE(p_statement_json, '$.accountHolderName'),
            v_account_no,
            v_bank_name,
            COALESCE(NULLIF(JSON_VALUE(p_statement_json, '$.currency'), ''), 'USD'),
            'Active',
            p_created_by, p_created_by, 0
        );
        SET v_entity_id = LAST_INSERT_ID();
    END IF;

    -- Backfill business scope on a pre-existing entity that was imported without one
    -- (e.g. before business_id propagation existed), so it appears in scoped lists.
    IF v_business_id IS NOT NULL AND v_entity_id IS NOT NULL THEN
        UPDATE dim_financial_entities
        SET business_id = v_business_id
        WHERE id = v_entity_id AND tenant_id = v_tenant_id AND business_id IS NULL;
    END IF;

    -- ── Insert the statement header ─────────────────────────────────────────
    INSERT INTO fact_statements (
        tenant_id, business_id, entity_id, statement_period, beginning_balance, current_balance,
        current_interest_rate, created_by, updated_by, is_deleted
    ) VALUES (
        v_tenant_id,
        v_business_id,
        v_entity_id,
        JSON_VALUE(p_statement_json, '$.statementPeriod'),
        CAST(JSON_VALUE(p_statement_json, '$.beginningBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.currentBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.interestRate') AS DECIMAL(5,2)),
        p_created_by, p_created_by, 0
    );

    SET p_statement_id = LAST_INSERT_ID();

    -- ── Insert the transactions ─────────────────────────────────────────────
    INSERT INTO fact_statement_transactions (
        tenant_id, statement_id, transaction_date, transaction_type, description, vendor,
        amount, cash_in_or_out, personal_or_business, associated_card_number, home_tax_percent,
        person_or_project_1, expense_labels, pdf_link, created_by, updated_by, is_deleted
    )
    SELECT
        v_tenant_id,
        p_statement_id,
        jt.transaction_date,
        CASE
            WHEN jt.transaction_type IN ('Credit','Debit','Transfer','Fee','Interest','Reversal')
                THEN jt.transaction_type
            WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Credit'
            ELSE 'Debit'
        END,
        jt.description,
        jt.vendor,
        jt.amount,
        CASE WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Cash In' ELSE 'Cash Out' END,
        CASE WHEN jt.personal_or_business IN ('Personal','Business') THEN jt.personal_or_business ELSE NULL END,
        jt.associated_card_number,
        jt.home_tax_percent,
        jt.person_or_project_1,
        jt.expense_labels,
        jt.pdf_link,
        p_created_by, p_created_by, 0
    FROM JSON_TABLE(
        p_transactions_json,
        '$[*]' COLUMNS (
            transaction_date      DATE           PATH '$.transactionDate',
            transaction_type      VARCHAR(50)    PATH '$.transactionType',
            description           TEXT           PATH '$.description',
            vendor                VARCHAR(255)   PATH '$.vendor',
            amount                DECIMAL(15,2)  PATH '$.amount',
            cash_in_or_out        VARCHAR(20)    PATH '$.cashInOrOut',
            personal_or_business  VARCHAR(20)    PATH '$.personalOrBusiness',
            associated_card_number VARCHAR(100)  PATH '$.associatedCardNumber',
            home_tax_percent      DECIMAL(5,2)   PATH '$.homeTaxPercent',
            person_or_project_1   VARCHAR(255)   PATH '$.personOrProject1',
            expense_labels        TEXT           PATH '$.expenseLabels',
            pdf_link              VARCHAR(512)   PATH '$.pdfLink'
        )
    ) AS jt;

    SET p_transaction_count = ROW_COUNT();
END proc $$

DELIMITER ;