ALTER TABLE shop_products
    ADD COLUMN promotion_billing_party ENUM('headquarters', 'acdf') NOT NULL DEFAULT 'headquarters' AFTER promotion_percent;

ALTER TABLE order_items
    ADD COLUMN promotion_percent DECIMAL(8, 2) NOT NULL DEFAULT 0.00 AFTER line_total_ht,
    ADD COLUMN promotion_billing_party ENUM('none', 'headquarters', 'acdf') NOT NULL DEFAULT 'none' AFTER promotion_percent,
    ADD COLUMN promotion_delta_ht DECIMAL(12, 2) NOT NULL DEFAULT 0.00 AFTER promotion_billing_party;

CREATE TABLE IF NOT EXISTS promotion_charge_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    billing_year SMALLINT UNSIGNED NOT NULL,
    billing_month TINYINT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status ENUM('issued', 'cancelled') NOT NULL DEFAULT 'issued',
    document_number VARCHAR(80) NOT NULL,
    recipient_email VARCHAR(190) NULL,
    recipient_name VARCHAR(190) NULL,
    amount_ht DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    issued_at DATETIME NOT NULL,
    created_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_promotion_charge_invoice_number (document_number),
    UNIQUE KEY uq_promotion_charge_invoice_period (network_id, billing_year, billing_month),
    KEY idx_promotion_charge_invoice_network (network_id),
    KEY idx_promotion_charge_invoice_period_start (period_start),
    KEY idx_promotion_charge_invoice_issued_at (issued_at),
    CONSTRAINT fk_promotion_charge_invoice_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_promotion_charge_invoice_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS promotion_charge_invoice_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NOT NULL,
    shop_id BIGINT UNSIGNED NOT NULL,
    shop_product_id BIGINT UNSIGNED NULL,
    order_number VARCHAR(80) NOT NULL,
    shop_name VARCHAR(190) NOT NULL,
    line_label VARCHAR(190) NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    promotion_percent DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
    amount_ht DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_promotion_charge_invoice_lines_invoice (invoice_id),
    KEY idx_promotion_charge_invoice_lines_order (order_id),
    KEY idx_promotion_charge_invoice_lines_order_item (order_item_id),
    CONSTRAINT fk_promotion_charge_invoice_lines_invoice FOREIGN KEY (invoice_id) REFERENCES promotion_charge_invoices(id),
    CONSTRAINT fk_promotion_charge_invoice_lines_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_promotion_charge_invoice_lines_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id),
    CONSTRAINT fk_promotion_charge_invoice_lines_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
);
