-- Migration 007: Create marketplace_orders table
-- Requirements: 6.1, 6.4, 16.1
-- Design: Data Models -> 7. MARKETPLACE ORDERS

CREATE TABLE IF NOT EXISTS marketplace_orders (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    marketplace_source      ENUM('SHOPEE','TOKOPEDIA','TIKTOK_SHOP') NOT NULL,
    order_id                VARCHAR(128) NOT NULL,
    order_date              DATETIME(3)  NOT NULL,
    order_status            ENUM('IN_TRANSIT','SETTLED','RETURNED') NOT NULL DEFAULT 'IN_TRANSIT',
    -- Komponen finansial (precomputed estimated_net_revenue)
    gross_revenue           DECIMAL(15,2) NOT NULL DEFAULT 0,
    admin_fee               DECIMAL(15,2) NOT NULL DEFAULT 0,
    voucher_discount        DECIMAL(15,2) NOT NULL DEFAULT 0,
    ads_cost                DECIMAL(15,2) NOT NULL DEFAULT 0,
    affiliate_commission    DECIMAL(15,2) NOT NULL DEFAULT 0,
    shipping_adjustment     DECIMAL(15,2) NOT NULL DEFAULT 0,
    estimated_net_revenue   DECIMAL(15,2) NOT NULL DEFAULT 0,
    -- Settlement / cash basis
    settlement_amount       DECIMAL(15,2) NULL,
    settlement_date         DATETIME(3)  NULL,
    discrepancy             DECIMAL(15,2) NULL,
    is_verified             TINYINT(1)   NULL,
    -- Retur
    refund_amount           DECIMAL(15,2) NOT NULL DEFAULT 0,
    returned_at             DATETIME(3)  NULL,
    -- Audit / ETL provenance (FK ditambahkan terpisah untuk hindari forward dependency ke upload_jobs)
    last_upload_job_id      BIGINT UNSIGNED NULL,
    created_at              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_orders_source_orderid (marketplace_source, order_id),
    KEY idx_orders_status_date_source (order_status, order_date, marketplace_source),
    KEY idx_orders_settle (order_status, settlement_date),
    KEY idx_orders_verify (is_verified, order_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;