-- Configurações gerais do e-commerce
CREATE TABLE IF NOT EXISTS ecommerce_config (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    store_name VARCHAR(255) NOT NULL DEFAULT 'Minha Loja',
    store_description TEXT,
    logo_url VARCHAR(500),
    whatsapp VARCHAR(20),
    email_contato VARCHAR(255),
    template_id VARCHAR(100) DEFAULT 'default',
    theme_colors JSON,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Domínios e subdomínios
CREATE TABLE IF NOT EXISTS ecommerce_dominios (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    dominio VARCHAR(255) NOT NULL,
    tipo ENUM('proprio', 'subdominio') NOT NULL DEFAULT 'subdominio',
    subdominio VARCHAR(100),
    ssl_enabled BOOLEAN DEFAULT FALSE,
    ssl_certificate TEXT,
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_dominio (dominio),
    KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Configurações de pagamento
CREATE TABLE IF NOT EXISTS ecommerce_pagamentos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    provider VARCHAR(50) NOT NULL, -- 'mercadopago', 'pagbank', 'stripe', etc.
    enabled BOOLEAN DEFAULT FALSE,
    public_key VARCHAR(255),
    access_token TEXT, -- Criptografado
    secret_key TEXT, -- Criptografado
    webhook_url VARCHAR(500),
    webhook_secret VARCHAR(255),
    config JSON, -- Configurações específicas do provider
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_provider (tenant_id, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Produtos visíveis no e-commerce
CREATE TABLE IF NOT EXISTS ecommerce_produtos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    tenant_id VARCHAR(100) NOT NULL,
    visible BOOLEAN DEFAULT TRUE,
    featured BOOLEAN DEFAULT FALSE,
    order_index INT DEFAULT 0,
    seo_title VARCHAR(255),
    seo_description TEXT,
    seo_keywords VARCHAR(500),
    custom_price DECIMAL(18,2) NULL, -- Preço customizado para e-commerce (NULL = usa preço do produto)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_product (product_id, tenant_id),
    KEY idx_tenant_visible (tenant_id, visible),
    KEY idx_featured (tenant_id, featured),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categorias do e-commerce
CREATE TABLE IF NOT EXISTS ecommerce_categorias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    parent_id BIGINT UNSIGNED NULL,
    order_index INT DEFAULT 0,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_slug (tenant_id, slug),
    KEY idx_parent (parent_id),
    FOREIGN KEY (parent_id) REFERENCES ecommerce_categorias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Relação produtos-categorias
CREATE TABLE IF NOT EXISTS ecommerce_produto_categorias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    produto_id BIGINT UNSIGNED NOT NULL,
    categoria_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_produto_categoria (produto_id, categoria_id),
    FOREIGN KEY (produto_id) REFERENCES ecommerce_produtos(id) ON DELETE CASCADE,
    FOREIGN KEY (categoria_id) REFERENCES ecommerce_categorias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pedidos do e-commerce
CREATE TABLE IF NOT EXISTS ecommerce_pedidos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    numero_pedido VARCHAR(50) NOT NULL,
    cliente_nome VARCHAR(255) NOT NULL,
    cliente_email VARCHAR(255) NOT NULL,
    cliente_telefone VARCHAR(20),
    cliente_cpf VARCHAR(14),
    endereco_entrega JSON NOT NULL,
    status ENUM('pendente', 'pago', 'processando', 'enviado', 'entregue', 'cancelado') DEFAULT 'pendente',
    subtotal DECIMAL(18,2) NOT NULL,
    frete DECIMAL(18,2) DEFAULT 0,
    desconto DECIMAL(18,2) DEFAULT 0,
    total DECIMAL(18,2) NOT NULL,
    payment_provider VARCHAR(50),
    payment_id VARCHAR(255),
    payment_status VARCHAR(50),
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_numero (numero_pedido),
    KEY idx_tenant_status (tenant_id, status),
    KEY idx_cliente_email (cliente_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Itens do pedido
CREATE TABLE IF NOT EXISTS ecommerce_pedido_itens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pedido_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    codigo VARCHAR(100),
    nome VARCHAR(255) NOT NULL,
    quantidade INT NOT NULL,
    preco_unitario DECIMAL(18,2) NOT NULL,
    subtotal DECIMAL(18,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pedido (pedido_id),
    KEY idx_product (product_id),
    FOREIGN KEY (pedido_id) REFERENCES ecommerce_pedidos(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Templates disponíveis
CREATE TABLE IF NOT EXISTS ecommerce_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    preview_image VARCHAR(500),
    category VARCHAR(100), -- 'moda', 'eletronicos', 'alimentos', 'geral'
    config_schema JSON, -- Schema de configurações do template
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Configurações de frete
CREATE TABLE IF NOT EXISTS ecommerce_frete_config (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    provider VARCHAR(50) NOT NULL, -- 'correios', 'jadlog', 'melhor-envio', 'custom'
    enabled BOOLEAN DEFAULT TRUE,
    cep_origem VARCHAR(9) NOT NULL,
    config JSON, -- Configurações específicas do provider
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_provider (tenant_id, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

