SET NAMES utf8mb4;

CREATE TABLE shipping_zones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_type ENUM('global', 'network', 'shop') NOT NULL DEFAULT 'global',
    network_id BIGINT UNSIGNED NULL,
    shop_id BIGINT UNSIGNED NULL,
    code VARCHAR(120) NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    country_code CHAR(2) NULL,
    postal_code_pattern VARCHAR(80) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_shipping_zones_code_scope (code, scope_type, network_id, shop_id),
    INDEX idx_shipping_zones_scope (scope_type, network_id, shop_id),
    INDEX idx_shipping_zones_country (country_code),
    CONSTRAINT fk_shipping_zones_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_shipping_zones_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shipping_methods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_type ENUM('global', 'network', 'shop') NOT NULL DEFAULT 'global',
    network_id BIGINT UNSIGNED NULL,
    shop_id BIGINT UNSIGNED NULL,
    code VARCHAR(120) NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    carrier_code VARCHAR(120) NULL,
    base_amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    variable_percent DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    free_shipping_threshold_ht DECIMAL(12,2) NULL,
    tax_rate DECIMAL(8,4) NOT NULL DEFAULT 20.0000,
    is_default TINYINT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_shipping_methods_code_scope (code, scope_type, network_id, shop_id),
    INDEX idx_shipping_methods_scope (scope_type, network_id, shop_id),
    INDEX idx_shipping_methods_default (is_default),
    CONSTRAINT fk_shipping_methods_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_shipping_methods_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shipping_method_rates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipping_method_id BIGINT UNSIGNED NOT NULL,
    shipping_zone_id BIGINT UNSIGNED NOT NULL,
    min_order_amount_ht DECIMAL(12,2) NULL,
    max_order_amount_ht DECIMAL(12,2) NULL,
    flat_amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    variable_percent DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    item_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_shipping_method_rates_method (shipping_method_id),
    INDEX idx_shipping_method_rates_zone (shipping_zone_id),
    CONSTRAINT fk_shipping_method_rates_method FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods(id),
    CONSTRAINT fk_shipping_method_rates_zone FOREIGN KEY (shipping_zone_id) REFERENCES shipping_zones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shipping_method_exclusions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipping_method_id BIGINT UNSIGNED NOT NULL,
    shipping_zone_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_shipping_method_exclusion (shipping_method_id, shipping_zone_id),
    INDEX idx_shipping_method_exclusions_zone (shipping_zone_id),
    CONSTRAINT fk_shipping_method_exclusions_method FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods(id),
    CONSTRAINT fk_shipping_method_exclusions_zone FOREIGN KEY (shipping_zone_id) REFERENCES shipping_zones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE orders
    ADD COLUMN shipping_method_id BIGINT UNSIGNED NULL AFTER shipping_address_json,
    ADD COLUMN shipping_method_label VARCHAR(190) NULL AFTER shipping_method_id,
    ADD COLUMN shipping_amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER credit_used_ht,
    ADD COLUMN shipping_amount_ttc DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER shipping_amount_ht,
    ADD COLUMN shipping_tax_rate DECIMAL(8,4) NOT NULL DEFAULT 20.0000 AFTER shipping_amount_ttc,
    ADD COLUMN shipping_rule_snapshot_json JSON NULL AFTER shipping_tax_rate,
    ADD CONSTRAINT fk_orders_shipping_method FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods(id);

INSERT INTO permissions (code, name)
SELECT 'shipping.manage', 'Gérer les frais de port'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'shipping.manage');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'shipping.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
