-- Inventory/Procurement Suppliers: SP-write / View-read stack for dim_suppliers.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/suppliers_sp_views.sql

USE db_abos_v0.1;

CREATE OR REPLACE VIEW vw_suppliers AS
SELECT
    s.id          AS id,
    s.id          AS supplierId,
    s.tenant_id   AS tenant_id,
    s.vendor_name AS vendorName,
    s.vendor_name AS name,
    s.category    AS category,
    s.tax_id      AS taxId,
    s.created_at  AS createdAt,
    s.updated_at  AS updatedAt
FROM dim_suppliers s
WHERE s.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_supplier;
DROP PROCEDURE IF EXISTS sp_update_supplier;
DROP PROCEDURE IF EXISTS sp_soft_delete_supplier;

DELIMITER $$

CREATE PROCEDURE sp_create_supplier(
    IN  p_tenant_id   INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_created_by  INT,
    OUT p_supplier_id INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_vendor_name IS NULL OR p_vendor_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Vendor name is required';
    END IF;

    INSERT INTO dim_suppliers (
        tenant_id, vendor_name, category, tax_id,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_vendor_name, p_category, p_tax_id,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_supplier_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_supplier(
    IN  p_tenant_id   INT,
    IN  p_supplier_id INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM dim_suppliers
        WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier not found';
    END IF;

    UPDATE dim_suppliers SET
        vendor_name = COALESCE(p_vendor_name, vendor_name),
        category    = COALESCE(p_category, category),
        tax_id      = COALESCE(p_tax_id, tax_id),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

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

DELIMITER ;