-- Azure D365 Replica Portal schema
-- Import this file in cPanel phpMyAdmin into database: r1uvoa3o_D365_Replica
-- All project tables start with Azure_D365_ as requested.

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

CREATE TABLE IF NOT EXISTS Azure_D365_Customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_account VARCHAR(100) NOT NULL,
    organization_name VARCHAR(255) NULL,
    name_alias VARCHAR(255) NULL,
    known_as VARCHAR(255) NULL,
    customer_group_id VARCHAR(100) NULL,
    party_number VARCHAR(100) NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(100) NULL,
    address_city VARCHAR(150) NULL,
    address_street TEXT NULL,
    country_region_id VARCHAR(50) NULL,
    sales_currency_code VARCHAR(20) NULL,
    on_hold_status INT DEFAULT 0,
    credit_limit DECIMAL(18,2) DEFAULT 0.00,
    payment_terms VARCHAR(100) NULL,
    payment_method VARCHAR(100) NULL,
    sales_tax_group VARCHAR(100) NULL,
    data_area_id VARCHAR(20) NULL,
    d365_sync_datetime DATETIME NULL,
    last_sync_at DATETIME NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_customer_company (customer_account, data_area_id),
    KEY Azure_D365_IDX_customer_account (customer_account),
    KEY Azure_D365_IDX_customer_name (organization_name),
    KEY Azure_D365_IDX_data_area (data_area_id),
    KEY Azure_D365_IDX_last_sync (d365_sync_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS Azure_D365_Sync_State (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    module_code VARCHAR(100) NOT NULL,
    source_table VARCHAR(150) NOT NULL,
    last_success_sync_datetime DATETIME NULL,
    last_run_at DATETIME NULL,
    last_status VARCHAR(50) NULL,
    last_message TEXT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_module_source (module_code, source_table)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS Azure_D365_Sync_Log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sync_name VARCHAR(100) NOT NULL,
    module_code VARCHAR(100) NULL,
    mode VARCHAR(30) NULL,
    status VARCHAR(50) NOT NULL,
    message TEXT NULL,
    rows_processed INT DEFAULT 0,
    rows_failed INT DEFAULT 0,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY Azure_D365_IDX_sync_name (sync_name),
    KEY Azure_D365_IDX_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS Azure_D365_Saved_Views (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    module_code VARCHAR(100) NOT NULL,
    view_name VARCHAR(150) NOT NULL,
    view_config JSON NULL,
    created_by VARCHAR(100) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_module_view (module_code, view_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS Azure_D365_Module_Config (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    module_code VARCHAR(100) NOT NULL,
    module_name VARCHAR(150) NOT NULL,
    icon_class VARCHAR(100) NULL,
    local_table VARCHAR(150) NOT NULL,
    source_table VARCHAR(150) NULL,
    is_enabled TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 100,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY Azure_D365_UQ_module_code (module_code)
) 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
('customers', 'Customers', 'fa-solid fa-users', 'Azure_D365_Customers', 'CustCustomerV3Staging', 1, 10)
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);
-- 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);