-- CRM Orders: SP-write / View-read stack for fact_orders.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/orders_sp_views.sql

USE db_abos_v0.1;

-- ---------------------------------------------------------------------------
-- Read model: view exposes camelCase aliases matching the frontend orderFromApi
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW vw_orders AS
SELECT
    o.id                        AS id,
    o.id                        AS orderId,
    o.tenant_id                 AS tenant_id,
    o.order_no                  AS orderNo,
    o.client_name               AS clientName,
    o.status                    AS status,
    o.total_amount              AS totalAmount,
    o.total_amount              AS total,
    o.currency                  AS currency,
    o.no_of_items               AS items,
    o.order_date                AS orderDate,
    o.delivery_date             AS deliveryDate,
    o.notes                     AS notes,
    o.created_at                AS createdAt,
    o.updated_at                AS updatedAt
FROM fact_orders o
WHERE o.is_deleted = 0;

-- ---------------------------------------------------------------------------
-- Write model: create / update / soft-delete stored procedures
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_create_order;
DROP PROCEDURE IF EXISTS sp_update_order;
DROP PROCEDURE IF EXISTS sp_soft_delete_order;

DELIMITER $$

CREATE PROCEDURE sp_create_order(
    IN  p_tenant_id     INT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_order_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_order_no IS NULL OR p_order_no = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order number is required';
    END IF;
    IF p_client_name IS NULL OR p_client_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Client name is required';
    END IF;

    INSERT INTO fact_orders (
        tenant_id, order_no, client_name, status, total_amount, currency,
        no_of_items, order_date, delivery_date, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_order_no, p_client_name,
        COALESCE(p_status, 'Pending'),
        COALESCE(p_total_amount, 0),
        COALESCE(p_currency, 'USD'),
        COALESCE(p_no_of_items, 0),
        p_order_date, p_delivery_date, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );

    SET p_order_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_order(
    IN  p_tenant_id     INT,
    IN  p_order_id      BIGINT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_orders
        WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order not found';
    END IF;

    UPDATE fact_orders SET
        order_no      = COALESCE(p_order_no, order_no),
        client_name   = COALESCE(p_client_name, client_name),
        status        = COALESCE(p_status, status),
        total_amount  = COALESCE(p_total_amount, total_amount),
        currency      = COALESCE(p_currency, currency),
        no_of_items   = COALESCE(p_no_of_items, no_of_items),
        order_date    = COALESCE(p_order_date, order_date),
        delivery_date = COALESCE(p_delivery_date, delivery_date),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

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

DELIMITER ;