-- Draft schema for generic orders domain (not auto-run by Migrator).
-- Source API fields were inspired by external marketplace/order APIs (Apilo used only as example).

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NULL,
    source VARCHAR(32) NOT NULL DEFAULT 'external',
    source_order_id VARCHAR(64) NOT NULL,
    external_order_id VARCHAR(128) NULL,
    external_platform_id VARCHAR(64) NULL,
    external_platform_account_id VARCHAR(64) NULL,
    external_status_id VARCHAR(64) NULL,
    external_payment_type_id VARCHAR(64) NULL,
    payment_status TINYINT UNSIGNED NULL,
    external_carrier_id VARCHAR(64) NULL,
    external_carrier_account_id VARCHAR(64) NULL,
    customer_login VARCHAR(128) NULL,
    is_invoice TINYINT(1) NOT NULL DEFAULT 0,
    is_encrypted TINYINT(1) NOT NULL DEFAULT 0,
    is_canceled_by_buyer TINYINT(1) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL,
    total_without_tax DECIMAL(12,2) NULL,
    total_with_tax DECIMAL(12,2) NULL,
    total_paid DECIMAL(12,2) NULL,
    send_date_min DATETIME NULL,
    send_date_max DATETIME NULL,
    ordered_at DATETIME NULL,
    source_created_at DATETIME NULL,
    source_updated_at DATETIME NULL,
    preferences_json JSON NULL,
    payload_json JSON NULL,
    fetched_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY orders_integration_source_order_unique (integration_id, source_order_id),
    KEY orders_integration_external_idx (integration_id, external_order_id),
    KEY orders_status_idx (external_status_id),
    KEY orders_source_updated_idx (source_updated_at),
    KEY orders_ordered_at_idx (ordered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    address_type ENUM('customer', 'delivery', 'invoice') NOT NULL,
    name VARCHAR(128) NOT NULL,
    phone VARCHAR(64) NULL,
    email VARCHAR(128) NULL,
    street_name VARCHAR(128) NULL,
    street_number VARCHAR(32) NULL,
    city VARCHAR(128) NULL,
    zip_code VARCHAR(16) NULL,
    country CHAR(2) NULL,
    department VARCHAR(128) NULL,
    parcel_external_id VARCHAR(64) NULL,
    parcel_name VARCHAR(128) NULL,
    address_class VARCHAR(32) NULL,
    company_tax_number VARCHAR(64) NULL,
    company_name VARCHAR(128) NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_addresses_order_type_unique (order_id, address_type),
    KEY order_addresses_email_idx (email),
    CONSTRAINT order_addresses_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_item_id VARCHAR(64) NULL,
    external_item_id VARCHAR(64) NULL,
    ean VARCHAR(32) NULL,
    sku VARCHAR(128) NULL,
    original_name VARCHAR(255) NOT NULL,
    original_code VARCHAR(128) NULL,
    original_price_with_tax DECIMAL(12,2) NULL,
    original_price_without_tax DECIMAL(12,2) NULL,
    media_url VARCHAR(512) NULL,
    quantity DECIMAL(12,3) NOT NULL DEFAULT 1.000,
    tax_rate DECIMAL(6,2) NULL,
    item_status VARCHAR(32) NULL,
    unit VARCHAR(16) NULL,
    item_type VARCHAR(32) NOT NULL COMMENT 'e.g. product, shipment, service',
    source_product_id VARCHAR(64) NULL,
    source_product_set_id VARCHAR(64) NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_items_order_source_item_unique (order_id, source_item_id),
    KEY order_items_order_idx (order_id),
    KEY order_items_sku_idx (sku),
    KEY order_items_ean_idx (ean),
    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_payment_id VARCHAR(64) NULL,
    external_payment_id VARCHAR(64) NULL,
    payment_type_id VARCHAR(64) NOT NULL,
    payment_date DATETIME NULL,
    amount DECIMAL(12,2) NULL,
    currency CHAR(3) NULL,
    comment VARCHAR(255) NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_payments_order_source_payment_unique (order_id, source_payment_id),
    KEY order_payments_order_idx (order_id),
    KEY order_payments_date_idx (payment_date),
    CONSTRAINT order_payments_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_shipments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_shipment_id VARCHAR(64) NULL,
    external_shipment_id VARCHAR(64) NULL,
    tracking_number VARCHAR(128) NOT NULL,
    carrier_provider_id VARCHAR(64) NOT NULL,
    posted_at DATETIME NULL,
    media_uuid CHAR(36) NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_shipments_order_source_shipment_unique (order_id, source_shipment_id),
    KEY order_shipments_order_idx (order_id),
    KEY order_shipments_tracking_idx (tracking_number),
    CONSTRAINT order_shipments_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_document_id VARCHAR(64) NULL,
    external_document_id VARCHAR(64) NULL,
    document_number VARCHAR(64) NULL,
    price_with_tax DECIMAL(12,2) NULL,
    price_without_tax DECIMAL(12,2) NULL,
    currency CHAR(3) NULL,
    currency_value DECIMAL(12,4) NULL,
    document_type_id VARCHAR(64) NULL,
    media_uuid CHAR(36) NULL,
    source_created_at DATETIME NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_documents_order_source_document_unique (order_id, source_document_id),
    KEY order_documents_order_idx (order_id),
    KEY order_documents_number_idx (document_number),
    CONSTRAINT order_documents_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_note_id VARCHAR(64) NULL,
    note_type VARCHAR(32) NOT NULL,
    created_at_external DATETIME NULL,
    comment TEXT NOT NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_notes_order_source_note_unique (order_id, source_note_id),
    KEY order_notes_order_idx (order_id),
    CONSTRAINT order_notes_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    from_status_id VARCHAR(64) NULL,
    to_status_id VARCHAR(64) NOT NULL,
    changed_at DATETIME NOT NULL,
    change_source ENUM('import', 'manual', 'api', 'sync') NOT NULL DEFAULT 'import',
    comment VARCHAR(255) NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY order_status_history_order_changed_idx (order_id, changed_at),
    KEY order_status_history_to_status_idx (to_status_id),
    CONSTRAINT order_status_history_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_tags_dict (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NULL,
    source_tag_id VARCHAR(64) NOT NULL,
    tag_name VARCHAR(128) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    updated_at_external DATETIME NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY order_tags_dict_integration_source_tag_unique (integration_id, source_tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_tag_links (
    order_id BIGINT UNSIGNED NOT NULL,
    tag_dict_id BIGINT UNSIGNED NOT NULL,
    assigned_at DATETIME NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, tag_dict_id),
    KEY order_tag_links_tag_idx (tag_dict_id),
    CONSTRAINT order_tag_links_order_fk
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT order_tag_links_tag_fk
        FOREIGN KEY (tag_dict_id) REFERENCES order_tags_dict(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_order_sync_state (
    integration_id INT UNSIGNED NOT NULL PRIMARY KEY,
    last_synced_order_updated_at DATETIME NULL,
    last_synced_source_order_id VARCHAR(64) NULL,
    last_synced_external_order_id VARCHAR(128) NULL,
    last_run_at DATETIME NULL,
    last_success_at DATETIME NULL,
    last_error VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;