CREATE TABLE IF NOT EXISTS order_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    shop_id BIGINT UNSIGNED NOT NULL,
    network_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    document_type ENUM('invoice', 'credit_note') NOT NULL,
    document_number VARCHAR(80) NOT NULL,
    order_number VARCHAR(80) NOT NULL,
    customer_email VARCHAR(190) NOT NULL,
    customer_name VARCHAR(190) NOT NULL,
    amount_ht DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    amount_ttc DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    issued_at DATETIME NOT NULL,
    issued_by_user_id BIGINT UNSIGNED NULL,
    payload_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uq_order_documents_number (document_number),
    UNIQUE KEY uq_order_documents_order_type (order_id, document_type),
    KEY idx_order_documents_order (order_id),
    KEY idx_order_documents_shop (shop_id),
    KEY idx_order_documents_network (network_id),
    KEY idx_order_documents_type (document_type),
    KEY idx_order_documents_issued_at (issued_at),
    CONSTRAINT fk_order_documents_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_documents_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
    CONSTRAINT fk_order_documents_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_order_documents_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_order_documents_issued_by FOREIGN KEY (issued_by_user_id) REFERENCES users(id)
);
