USE db_abos_v0.1;

-- =============================================================================
-- order_types_module.sql — Order Type catalog + itemized order line items (Phase K).
--
-- Mirrors dim_business_types' exact pattern (Phase L1): a lookup table, not a hardcoded
-- Java enum, so a tenant can add its own custom order type alongside the system-seeded
-- ones. tenant_id NULL marks a system type (visible to every tenant); a tenant-owned row
-- has tenant_id set.
--
-- requires_line_items drives whether CRMOrders.tsx shows the itemized line-item table
-- (mirroring fact_opportunity_line_items' exact shape/generated-total-price convention —
-- see FactOpportunityLineItem.java) instead of a single free-text total.
--
-- fulfillment_type is informational only here: 'PROJECT' hints the UI to prompt for the
-- existing linked_project_id column (order_project_link.sql); a full Order<->Web Store
-- fulfillment bridge was explicitly deferred in that migration's notes and stays out of
-- scope here too — 'WEB_STORE' is seeded as a value for forward compatibility only.
--
-- fact_orders is entirely stored-procedure/view-backed (no JPA entity) — order_type_id
-- is threaded through sp_create_order/sp_update_order/vw_orders alongside the existing
-- columns. Line items get their own JPA entity + repo, exactly like FactOpportunityLineItem,
-- since they're a child collection naturally suited to delete-then-recreate-on-save.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS, guarded ALTER, WHERE NOT EXISTS seed guards,
-- DROP PROCEDURE IF EXISTS + CREATE, CREATE OR REPLACE VIEW.
-- =============================================================================

SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS dim_order_types (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT NULL,
    type_key             VARCHAR(50) NOT NULL,
    name                 VARCHAR(255) NOT NULL,
    description          VARCHAR(500) NULL,
    requires_line_items  TINYINT NOT NULL DEFAULT 0,
    fulfillment_type     VARCHAR(20) NOT NULL DEFAULT 'NONE',
    sort_order           INT NOT NULL DEFAULT 0,
    is_system            TINYINT NOT NULL DEFAULT 0,
    created_at           TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by           INT NULL,
    updated_at           TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by           INT NULL,
    is_deleted           TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_order_types_key (tenant_id, type_key),
    INDEX idx_dim_order_types_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Seed system order types (tenant_id NULL = visible to every tenant) ──────────
INSERT INTO dim_order_types (tenant_id, type_key, name, description, requires_line_items, fulfillment_type, sort_order, is_system, is_deleted)
SELECT NULL, v.type_key, v.name, v.description, v.requires_line_items, v.fulfillment_type, v.sort_order, 1, 0
FROM (
    SELECT 'standard' type_key, 'Standard Order' name, 'A simple order with a single total amount' description, 0 requires_line_items, 'NONE' fulfillment_type, 10 sort_order
    UNION ALL SELECT 'product_sale', 'Product Sale', 'Itemized order for one or more physical/digital products', 1, 'NONE', 20
    UNION ALL SELECT 'service_project', 'Service (Project Fulfillment)', 'Itemized service order fulfilled via a linked Project', 1, 'PROJECT', 30
    UNION ALL SELECT 'web_store', 'Web Store Fulfillment', 'Order originating from or fulfilled through the web storefront', 0, 'WEB_STORE', 40
) v
WHERE NOT EXISTS (
    SELECT 1 FROM dim_order_types t WHERE t.tenant_id IS NULL AND t.type_key = v.type_key
);

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

-- ── fact_order_line_items — mirrors fact_opportunity_line_items exactly ─────────
CREATE TABLE IF NOT EXISTS fact_order_line_items (
    id               BIGINT NOT NULL AUTO_INCREMENT,
    tenant_id        INT NOT NULL,
    order_id         BIGINT NOT NULL,
    product_item_id  BIGINT NULL,
    description      VARCHAR(255) NULL,
    quantity         INT NOT NULL DEFAULT 1,
    unit_price       DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    discount_amount  DECIMAL(15,2) DEFAULT 0.00,
    total_price      DECIMAL(15,2) GENERATED ALWAYS AS ((quantity * unit_price) - discount_amount) STORED,
    created_at       TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by       INT DEFAULT NULL,
    updated_at       TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by       INT DEFAULT NULL,
    is_deleted       TINYINT(1) DEFAULT '0',
    PRIMARY KEY (id),
    KEY idx_fact_order_line_items_tenant (tenant_id),
    KEY idx_fact_order_line_items_order (order_id),
    KEY idx_fact_order_line_items_product (product_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── vw_orders: expose orderTypeId alongside the existing columns ────────────────
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.order_type_id             AS orderTypeId,
    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_order_type_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, order_type_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_order_type_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_order_type_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),
        order_type_id     = COALESCE(p_order_type_id, order_type_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 ;