-- Inventory Products: purpose-fit table + SP-write / View-read stack.
-- The legacy dim_products (category_id FK, base_cost/base_retail) does not match the
-- frontend Product shape and is unused, so we introduce inv_products keyed to the UI contract.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/products_sp_views.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS inv_products (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    sku           VARCHAR(100) NULL,
    category      VARCHAR(150) NULL,
    unit_price    DECIMAL(15,2) NOT NULL DEFAULT 0,
    cost_price    DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    stock_qty     INT NULL DEFAULT 0,
    reorder_level INT NULL DEFAULT 0,
    unit          VARCHAR(50) NULL,
    status        ENUM('Active','Inactive','Discontinued') NULL DEFAULT 'Active',
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by    INT NULL,
    updated_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT NULL,
    is_deleted    TINYINT(1) NULL DEFAULT 0,
    KEY idx_inv_products_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_products AS
SELECT
    p.id            AS id,
    p.id            AS productId,
    p.tenant_id     AS tenant_id,
    p.name          AS name,
    p.sku           AS sku,
    p.category      AS category,
    p.unit_price    AS unitPrice,
    p.cost_price    AS costPrice,
    p.currency      AS currency,
    p.stock_qty     AS stockQty,
    p.reorder_level AS reorderLevel,
    p.unit          AS unit,
    p.status        AS status,
    p.created_at    AS createdAt,
    p.updated_at    AS updatedAt
FROM inv_products p
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_product;
DROP PROCEDURE IF EXISTS sp_update_product;
DROP PROCEDURE IF EXISTS sp_soft_delete_product;

DELIMITER $$

CREATE PROCEDURE sp_create_product(
    IN  p_tenant_id     INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_created_by    INT,
    OUT p_product_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_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product name is required';
    END IF;

    INSERT INTO inv_products (
        tenant_id, name, sku, category, unit_price, cost_price, currency,
        stock_qty, reorder_level, unit, status,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_sku, p_category, COALESCE(p_unit_price, 0), p_cost_price,
        COALESCE(p_currency, 'USD'), COALESCE(p_stock_qty, 0), COALESCE(p_reorder_level, 0),
        p_unit, COALESCE(p_status, 'Active'),
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_product_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_product(
    IN  p_tenant_id     INT,
    IN  p_product_id    INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_products
        WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found';
    END IF;

    UPDATE inv_products SET
        name          = COALESCE(p_name, name),
        sku           = COALESCE(p_sku, sku),
        category      = COALESCE(p_category, category),
        unit_price    = COALESCE(p_unit_price, unit_price),
        cost_price    = COALESCE(p_cost_price, cost_price),
        currency      = COALESCE(p_currency, currency),
        stock_qty     = COALESCE(p_stock_qty, stock_qty),
        reorder_level = COALESCE(p_reorder_level, reorder_level),
        unit          = COALESCE(p_unit, unit),
        status        = COALESCE(p_status, status),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

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

DELIMITER ;