-- Procurement Purchases: SP-write / View-read stack for fact_purchases.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/purchases_sp_views.sql

USE db_abos_v0.1;

-- Add notes column (frontend sends purchase notes) if not present.
-- Uses DATABASE() rather than a hardcoded schema name — a literal 'db_abos_v0.1' here
-- silently never matches on any other target database (dev renamed, staging, production),
-- so the guard always reports "column missing" and re-attempts the ADD COLUMN, which then
-- fails with "Duplicate column name" once it already exists. DATABASE() is what every other
-- guarded ALTER in this codebase already uses.
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = DATABASE() AND table_name = 'fact_purchases' AND column_name = 'notes');
SET @ddl = IF(@col_exists = 0, 'ALTER TABLE fact_purchases ADD COLUMN notes TEXT NULL AFTER purchase_date', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE OR REPLACE VIEW vw_purchases AS
SELECT
    p.id                     AS id,
    p.id                     AS purchaseId,
    p.tenant_id              AS tenant_id,
    p.supplier_id            AS supplierId,
    s.vendor_name            AS supplierName,
    s.vendor_name            AS vendorName,
    p.wallet_id              AS walletId,
    w.wallet_name            AS walletName,
    p.amount                 AS amount,
    p.is_capital_expenditure AS isCapitalExpenditure,
    p.purchase_date          AS purchaseDate,
    p.notes                  AS notes,
    p.created_at             AS createdAt,
    p.updated_at             AS updatedAt
FROM fact_purchases p
LEFT JOIN dim_suppliers s ON s.id = p.supplier_id
LEFT JOIN dim_wallets   w ON w.id = p.wallet_id
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_purchase;
DROP PROCEDURE IF EXISTS sp_update_purchase;
DROP PROCEDURE IF EXISTS sp_soft_delete_purchase;

DELIMITER $$

CREATE PROCEDURE sp_create_purchase(
    IN  p_tenant_id     INT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_purchase_id   BIGINT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_supplier_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier is required';
    END IF;
    IF p_wallet_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wallet is required';
    END IF;

    INSERT INTO fact_purchases (
        tenant_id, supplier_id, wallet_id, is_capital_expenditure, amount,
        purchase_date, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_supplier_id, p_wallet_id, COALESCE(p_is_capex, 0), COALESCE(p_amount, 0),
        p_purchase_date, p_notes, NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_purchase_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_purchase(
    IN  p_tenant_id     INT,
    IN  p_purchase_id   BIGINT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_purchases
        WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Purchase not found';
    END IF;

    UPDATE fact_purchases SET
        supplier_id            = COALESCE(p_supplier_id, supplier_id),
        wallet_id              = COALESCE(p_wallet_id, wallet_id),
        is_capital_expenditure = COALESCE(p_is_capex, is_capital_expenditure),
        amount                 = COALESCE(p_amount, amount),
        purchase_date          = COALESCE(p_purchase_date, purchase_date),
        notes                  = COALESCE(p_notes, notes),
        updated_at             = NOW(),
        updated_by             = p_updated_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_purchase(
    IN  p_tenant_id   INT,
    IN  p_purchase_id BIGINT,
    IN  p_deleted_by  INT
)
proc: BEGIN
    UPDATE fact_purchases SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;