-- =============================================================================================
-- order_project_link.sql — lets a CRM Order (fact_orders) point at a Project it's fulfilling.
--
-- fact_orders is entirely stored-procedure/view-backed (no JPA entity) — see orders_sp_views.sql.
-- This adds one nullable back-link column, mirroring dim_projects.opportunity_id's existing
-- back-link convention (see project_deal_link.sql). Deliberately just one column, not a full
-- fulfillment-type system — that broader "Order Types" design (Product/Service/fulfillment_type)
-- is scoped separately and shouldn't be duplicated here.
--
-- Safe to re-run: guarded ALTER, DROP PROCEDURE IF EXISTS + CREATE, CREATE OR REPLACE VIEW.
-- =============================================================================================

USE db_abos_v0.1;

SET @db := DATABASE();

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_orders' AND COLUMN_NAME='linked_project_id'),
  'ALTER TABLE fact_orders ADD COLUMN linked_project_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

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.linked_project_id         AS linkedProjectId,
    o.notes                     AS notes,
    o.created_at                AS createdAt,
    o.updated_at                AS updatedAt
FROM fact_orders o
WHERE o.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_order;
DROP PROCEDURE IF EXISTS sp_update_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_linked_project_id BIGINT,
    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, linked_project_id, 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_linked_project_id, 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_linked_project_id BIGINT,
    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),
        linked_project_id = COALESCE(p_linked_project_id, linked_project_id),
        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$$

DELIMITER ;