SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS retrocession_statement_lines;
DROP TABLE IF EXISTS retrocession_statements;
DROP TABLE IF EXISTS retrocession_policies;
DROP TABLE IF EXISTS credit_transactions;
DROP TABLE IF EXISTS credit_accounts;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS price_policies;
DROP TABLE IF EXISTS price_grid_rows;
DROP TABLE IF EXISTS price_grids;
DROP TABLE IF EXISTS shop_product_assets;
DROP TABLE IF EXISTS shop_products;
DROP TABLE IF EXISTS product_attribute_options;
DROP TABLE IF EXISTS product_attributes;
DROP TABLE IF EXISTS product_assets;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS user_shop_memberships;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS shop_modules;
DROP TABLE IF EXISTS modules;
DROP TABLE IF EXISTS shop_domains;
DROP TABLE IF EXISTS shops;
DROP TABLE IF EXISTS networks;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE networks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    retrocession_rate DECIMAL(8,4) NULL,
    status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
    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;

CREATE TABLE shops (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NULL,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
    is_generic TINYINT NOT NULL DEFAULT 0,
    logo_path VARCHAR(255) NULL,
    primary_color VARCHAR(20) NULL,
    secondary_color VARCHAR(20) NULL,
    retrocession_rate DECIMAL(8,4) NULL,
    settings_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_shops_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shop_domains (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id BIGINT UNSIGNED NOT NULL,
    host VARCHAR(190) NOT NULL UNIQUE,
    is_primary TINYINT NOT NULL DEFAULT 1,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_shop_domains_shop (shop_id),
    CONSTRAINT fk_shop_domains_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE modules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shop_modules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id BIGINT UNSIGNED NOT NULL,
    module_id BIGINT UNSIGNED NOT NULL,
    enabled TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_shop_module (shop_id, module_id),
    CONSTRAINT fk_shop_modules_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
    CONSTRAINT fk_shop_modules_module FOREIGN KEY (module_id) REFERENCES modules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(120) NOT NULL,
    last_name VARCHAR(120) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    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;

CREATE TABLE addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('billing', 'shipping') NOT NULL,
    label VARCHAR(120) NULL,
    company VARCHAR(190) NULL,
    line1 VARCHAR(190) NOT NULL,
    line2 VARCHAR(190) NULL,
    postal_code VARCHAR(30) NOT NULL,
    city VARCHAR(120) NOT NULL,
    country VARCHAR(2) NOT NULL DEFAULT 'FR',
    phone VARCHAR(40) NULL,
    is_default TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_addresses_user (user_id),
    CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_shop_memberships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    shop_id BIGINT UNSIGNED NOT NULL,
    role_code VARCHAR(100) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_shop_role (user_id, shop_id, role_code),
    INDEX idx_user_shop_memberships_shop (shop_id),
    CONSTRAINT fk_user_shop_memberships_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_user_shop_memberships_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(120) NOT NULL UNIQUE,
    default_name VARCHAR(190) NOT NULL,
    default_description TEXT NULL,
    product_type VARCHAR(100) NOT NULL,
    category_name VARCHAR(120) NULL,
    subcategory_name VARCHAR(120) NULL,
    base_spec_json JSON 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,
    INDEX idx_products_category (category_name),
    INDEX idx_products_subcategory (subcategory_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_assets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    asset_type ENUM('image', 'video', 'pdf') NOT NULL,
    asset_role VARCHAR(50) NOT NULL DEFAULT 'gallery',
    path VARCHAR(255) NOT NULL,
    position INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_assets_product (product_id),
    CONSTRAINT fk_product_assets_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_attributes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(190) NOT NULL,
    input_type ENUM('select', 'multiselect', 'text', 'number', 'boolean') NOT NULL DEFAULT 'select',
    is_required TINYINT NOT NULL DEFAULT 0,
    is_pricing_quantity TINYINT NOT NULL DEFAULT 0,
    position INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_attributes_product_code (product_id, code),
    INDEX idx_product_attributes_product (product_id),
    CONSTRAINT fk_product_attributes_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_attribute_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_attribute_id BIGINT UNSIGNED NOT NULL,
    value VARCHAR(190) NOT NULL,
    label VARCHAR(190) NOT NULL,
    position INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_attribute_options_value (product_attribute_id, value),
    INDEX idx_product_attribute_options_attribute (product_attribute_id),
    CONSTRAINT fk_product_attribute_options_attribute FOREIGN KEY (product_attribute_id) REFERENCES product_attributes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shop_products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id BIGINT UNSIGNED NOT NULL,
    source_product_id BIGINT UNSIGNED NOT NULL,
    parent_shop_product_id BIGINT UNSIGNED NULL,
    item_type ENUM('activation', 'derived') NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    is_visible TINYINT NOT NULL DEFAULT 1,
    override_name VARCHAR(190) NULL,
    override_description TEXT NULL,
    override_spec_json JSON NULL,
    override_settings_json JSON NULL,
    price_grid_id_override BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_shop_products_shop (shop_id),
    INDEX idx_shop_products_source (source_product_id),
    INDEX idx_shop_products_parent (parent_shop_product_id),
    CONSTRAINT fk_shop_products_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
    CONSTRAINT fk_shop_products_source_product FOREIGN KEY (source_product_id) REFERENCES products(id),
    CONSTRAINT fk_shop_products_parent FOREIGN KEY (parent_shop_product_id) REFERENCES shop_products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shop_product_assets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_product_id BIGINT UNSIGNED NOT NULL,
    asset_type ENUM('image', 'video', 'pdf') NOT NULL,
    asset_role VARCHAR(50) NOT NULL DEFAULT 'gallery',
    path VARCHAR(255) NOT NULL,
    position INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_shop_product_assets_shop_product (shop_product_id),
    CONSTRAINT fk_shop_product_assets_shop_product FOREIGN KEY (shop_product_id) REFERENCES shop_products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE price_grids (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(190) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'EUR',
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_price_grids_product_code (product_id, code),
    CONSTRAINT fk_price_grids_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE price_grid_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price_grid_id BIGINT UNSIGNED NOT NULL,
    criteria_key VARCHAR(255) NOT NULL DEFAULT 'default',
    criteria_json JSON NULL,
    min_qty INT NOT NULL DEFAULT 1,
    max_qty INT NULL,
    nominal_price_ht DECIMAL(12,2) NOT NULL,
    cost_price_ht DECIMAL(12,2) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_price_grid_rows_grid_qty (price_grid_id, min_qty, max_qty),
    CONSTRAINT fk_price_grid_rows_grid FOREIGN KEY (price_grid_id) REFERENCES price_grids(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE price_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_type ENUM('global', 'network', 'shop') NOT NULL,
    scope_id BIGINT UNSIGNED NULL,
    coefficient DECIMAL(10,4) NOT NULL DEFAULT 1.0000,
    rounding_mode ENUM('none', 'cent', '0_05', '1') NOT NULL DEFAULT 'cent',
    priority INT NOT NULL DEFAULT 100,
    valid_from DATETIME NULL,
    valid_to DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_price_policies_scope (scope_type, scope_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('draft', 'submitted', 'paid', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'draft',
    billing_address_json JSON NULL,
    shipping_address_json JSON NULL,
    subtotal_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    credit_used_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_ttc DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_shop (shop_id),
    INDEX idx_orders_user (user_id),
    CONSTRAINT fk_orders_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    source_product_id BIGINT UNSIGNED NOT NULL,
    shop_product_id BIGINT UNSIGNED NULL,
    label VARCHAR(190) NOT NULL,
    quantity INT NOT NULL,
    unit_nominal_ht DECIMAL(12,2) NOT NULL,
    applied_coefficient DECIMAL(10,4) NOT NULL,
    unit_sale_ht DECIMAL(12,2) NOT NULL,
    line_total_ht DECIMAL(12,2) NOT NULL,
    configuration_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_items_order (order_id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_items_source_product FOREIGN KEY (source_product_id) REFERENCES products(id),
    CONSTRAINT fk_order_items_shop_product FOREIGN KEY (shop_product_id) REFERENCES shop_products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE credit_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_type ENUM('network', 'shop', 'user') NOT NULL,
    owner_id BIGINT UNSIGNED NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'EUR',
    balance_cached DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_credit_account_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE credit_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    movement_type ENUM('grant', 'consume', 'adjust') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    reference VARCHAR(120) NULL,
    reason VARCHAR(255) NULL,
    created_by_user_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_credit_transactions_account (account_id),
    CONSTRAINT fk_credit_transactions_account FOREIGN KEY (account_id) REFERENCES credit_accounts(id),
    CONSTRAINT fk_credit_transactions_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_credit_transactions_user FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE retrocession_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_type ENUM('network', 'shop') NOT NULL,
    scope_id BIGINT UNSIGNED NOT NULL,
    rate_percent DECIMAL(8,4) NOT NULL,
    calculation_base ENUM('order_total_ht') NOT NULL DEFAULT 'order_total_ht',
    periodicity ENUM('monthly', 'quarterly') NOT NULL DEFAULT 'monthly',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_retrocession_policies_scope (scope_type, scope_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE retrocession_statements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_type ENUM('network', 'shop') NOT NULL,
    scope_id BIGINT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    base_amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    retrocession_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'validated', 'exported') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_retrocession_statements_scope_period (scope_type, scope_id, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE retrocession_statement_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    statement_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    order_amount_ht DECIMAL(12,2) NOT NULL,
    applied_rate_percent DECIMAL(8,4) NOT NULL,
    retrocession_amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_retrocession_statement_lines_statement (statement_id),
    CONSTRAINT fk_retrocession_statement_lines_statement FOREIGN KEY (statement_id) REFERENCES retrocession_statements(id),
    CONSTRAINT fk_retrocession_statement_lines_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
