-- Azure D365 Sales Orders module update
-- Import this file after your existing Azure_D365_schema.sql / current database dump.
-- It adds the Sales Orders module without deleting customer data.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+04:00";

CREATE TABLE IF NOT EXISTS Azure_D365_Sales_Orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sales_order_number VARCHAR(100) NOT NULL,
    customer_account VARCHAR(100) NULL,
    invoice_customer_account VARCHAR(100) NULL,
    customer_name VARCHAR(255) NULL,
    sales_order_status VARCHAR(100) NULL,
    sales_order_type VARCHAR(100) NULL,
    document_status VARCHAR(100) NULL,
    currency_code VARCHAR(20) NULL,
    order_date DATE NULL,
    requested_ship_date DATE NULL,
    requested_receipt_date DATE NULL,
    confirmed_ship_date DATE NULL,
    confirmed_receipt_date DATE NULL,
    delivery_city VARCHAR(150) NULL,
    delivery_country_region_id VARCHAR(50) NULL,
    total_amount DECIMAL(18,2) DEFAULT 0.00,
    data_area_id VARCHAR(20) NULL,
    d365_sync_datetime DATETIME NULL,
    raw_json LONGTEXT NULL,
    last_sync_at DATETIME NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_sales_order_company (sales_order_number, data_area_id),
    KEY Azure_D365_IDX_sales_order_number (sales_order_number),
    KEY Azure_D365_IDX_sales_customer (customer_account),
    KEY Azure_D365_IDX_sales_status (sales_order_status),
    KEY Azure_D365_IDX_sales_company (data_area_id),
    KEY Azure_D365_IDX_sales_order_date (order_date),
    KEY Azure_D365_IDX_sales_sync (d365_sync_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO Azure_D365_Module_Config
(module_code, module_name, icon_class, local_table, source_table, is_enabled, sort_order)
VALUES
('sales_orders', 'Sales Orders', 'fa-solid fa-file-invoice-dollar', 'Azure_D365_Sales_Orders', 'SalesOrderHeaderV2Staging', 1, 20)
ON DUPLICATE KEY UPDATE
module_name = VALUES(module_name),
icon_class = VALUES(icon_class),
local_table = VALUES(local_table),
source_table = VALUES(source_table),
is_enabled = VALUES(is_enabled),
sort_order = VALUES(sort_order);


-- Sales order lines table for the detail page.
CREATE TABLE IF NOT EXISTS Azure_D365_Sales_Order_Lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sales_order_number VARCHAR(100) NOT NULL,
    data_area_id VARCHAR(20) NULL,
    line_key VARCHAR(150) NOT NULL,
    line_number VARCHAR(100) NULL,
    item_number VARCHAR(100) NULL,
    product_name VARCHAR(255) NULL,
    product_variant VARCHAR(150) NULL,
    site_id VARCHAR(100) NULL,
    warehouse_id VARCHAR(100) NULL,
    quantity_ordered DECIMAL(18,4) DEFAULT 0.0000,
    sales_unit VARCHAR(50) NULL,
    unit_price DECIMAL(18,4) DEFAULT 0.0000,
    line_discount_amount DECIMAL(18,2) DEFAULT 0.00,
    line_amount DECIMAL(18,2) DEFAULT 0.00,
    line_status VARCHAR(100) NULL,
    requested_ship_date DATE NULL,
    confirmed_ship_date DATE NULL,
    d365_sync_datetime DATETIME NULL,
    raw_json LONGTEXT NULL,
    last_sync_at DATETIME NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_sales_order_line (sales_order_number, data_area_id, line_key),
    KEY Azure_D365_IDX_sol_order (sales_order_number, data_area_id),
    KEY Azure_D365_IDX_sol_item (item_number),
    KEY Azure_D365_IDX_sol_sync (d365_sync_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO Azure_D365_Module_Config
(module_code, module_name, icon_class, local_table, source_table, is_enabled, sort_order)
VALUES
('sales_order_lines', 'Sales Order Lines', 'fa-solid fa-list-check', 'Azure_D365_Sales_Order_Lines', 'SalesOrderLineV2Staging', 1, 21)
ON DUPLICATE KEY UPDATE
module_name = VALUES(module_name),
icon_class = VALUES(icon_class),
local_table = VALUES(local_table),
source_table = VALUES(source_table),
is_enabled = VALUES(is_enabled),
sort_order = VALUES(sort_order);