-- Gestão de Lotes e Séries - Rastreabilidade Completa

-- ============================================
-- LOTES
-- ============================================

CREATE TABLE IF NOT EXISTS product_lots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados do lote
    product_id BIGINT UNSIGNED NOT NULL,
    numero_lote VARCHAR(100) NOT NULL,
    codigo_lote VARCHAR(100) NULL, -- Código alternativo
    
    -- Validade
    data_fabricacao DATE NULL,
    data_validade DATE NULL,
    dias_validade INT NULL, -- Calculado automaticamente se não informado
    
    -- Quantidade
    quantidade_inicial DECIMAL(18,3) NOT NULL DEFAULT 0,
    quantidade_atual DECIMAL(18,3) NOT NULL DEFAULT 0,
    quantidade_reservada DECIMAL(18,3) NOT NULL DEFAULT 0,
    
    -- Localização
    warehouse_id BIGINT UNSIGNED NULL,
    bin_id BIGINT UNSIGNED NULL,
    
    -- Fornecedor e origem
    fornecedor_id BIGINT UNSIGNED NULL,
    nota_fiscal_id BIGINT UNSIGNED NULL, -- NFe de entrada
    numero_nota_fiscal VARCHAR(50) NULL,
    
    -- Status
    status ENUM('ativo', 'bloqueado', 'vencido', 'esgotado', 'recall') DEFAULT 'ativo',
    
    -- Metadados
    observacoes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_numero_lote (numero_lote),
    INDEX idx_validade (data_validade),
    INDEX idx_status (status),
    INDEX idx_fornecedor (fornecedor_id),
    INDEX idx_warehouse (warehouse_id),
    UNIQUE KEY uk_tenant_product_lote (tenant_id, product_id, numero_lote)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Movimentações de lotes
CREATE TABLE IF NOT EXISTS lot_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    lot_id BIGINT UNSIGNED NOT NULL,
    
    -- Movimentação
    tipo ENUM('entrada', 'saida', 'transferencia', 'ajuste', 'reserva', 'liberacao_reserva') NOT NULL,
    quantidade DECIMAL(18,3) NOT NULL,
    
    -- Origem/Destino
    origem_tipo VARCHAR(50) NULL, -- 'compra', 'venda', 'producao', 'ajuste', etc
    origem_id BIGINT UNSIGNED NULL,
    origem_numero VARCHAR(100) NULL,
    
    destino_tipo VARCHAR(50) NULL,
    destino_id BIGINT UNSIGNED NULL,
    destino_numero VARCHAR(100) NULL,
    
    -- Localização
    warehouse_id_origem BIGINT UNSIGNED NULL,
    warehouse_id_destino BIGINT UNSIGNED NULL,
    
    -- Metadados
    motivo VARCHAR(255) NULL,
    observacoes TEXT NULL,
    user_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_lot (lot_id),
    INDEX idx_tipo (tipo),
    INDEX idx_origem (origem_tipo, origem_id),
    INDEX idx_destino (destino_tipo, destino_id),
    INDEX idx_created (created_at),
    FOREIGN KEY (lot_id) REFERENCES product_lots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- NÚMEROS DE SÉRIE
-- ============================================

CREATE TABLE IF NOT EXISTS product_serial_numbers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados da série
    product_id BIGINT UNSIGNED NOT NULL,
    numero_serie VARCHAR(255) NOT NULL,
    
    -- Status
    status ENUM('disponivel', 'reservado', 'vendido', 'garantia', 'defeito', 'recall') DEFAULT 'disponivel',
    
    -- Localização
    warehouse_id BIGINT UNSIGNED NULL,
    bin_id BIGINT UNSIGNED NULL,
    
    -- Origem
    fornecedor_id BIGINT UNSIGNED NULL,
    nota_fiscal_id BIGINT UNSIGNED NULL,
    numero_nota_fiscal VARCHAR(50) NULL,
    lote_id BIGINT UNSIGNED NULL, -- Se o produto tiver lote
    
    -- Venda/Cliente
    venda_id BIGINT UNSIGNED NULL, -- Pedido de venda
    cliente_id BIGINT UNSIGNED NULL,
    data_venda DATE NULL,
    
    -- Garantia
    data_compra DATE NULL,
    data_inicio_garantia DATE NULL,
    data_fim_garantia DATE NULL,
    dias_garantia INT NULL,
    
    -- Metadados
    observacoes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_numero_serie (numero_serie),
    INDEX idx_status (status),
    INDEX idx_cliente (cliente_id),
    INDEX idx_venda (venda_id),
    INDEX idx_lote (lote_id),
    INDEX idx_garantia (data_fim_garantia),
    UNIQUE KEY uk_tenant_product_serie (tenant_id, product_id, numero_serie)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Histórico de movimentações de séries
CREATE TABLE IF NOT EXISTS serial_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    serial_id BIGINT UNSIGNED NOT NULL,
    
    -- Movimentação
    tipo ENUM('entrada', 'saida', 'transferencia', 'reserva', 'liberacao', 'garantia', 'defeito') NOT NULL,
    status_anterior ENUM('disponivel', 'reservado', 'vendido', 'garantia', 'defeito', 'recall') NULL,
    status_novo ENUM('disponivel', 'reservado', 'vendido', 'garantia', 'defeito', 'recall') NULL,
    
    -- Origem/Destino
    origem_tipo VARCHAR(50) NULL,
    origem_id BIGINT UNSIGNED NULL,
    origem_numero VARCHAR(100) NULL,
    
    destino_tipo VARCHAR(50) NULL,
    destino_id BIGINT UNSIGNED NULL,
    destino_numero VARCHAR(100) NULL,
    
    -- Localização
    warehouse_id_origem BIGINT UNSIGNED NULL,
    warehouse_id_destino BIGINT UNSIGNED NULL,
    
    -- Metadados
    motivo VARCHAR(255) NULL,
    observacoes TEXT NULL,
    user_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_serial (serial_id),
    INDEX idx_tipo (tipo),
    INDEX idx_origem (origem_tipo, origem_id),
    INDEX idx_destino (destino_tipo, destino_id),
    INDEX idx_created (created_at),
    FOREIGN KEY (serial_id) REFERENCES product_serial_numbers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- RASTREABILIDADE
-- ============================================

-- Tabela de recall de produtos
CREATE TABLE IF NOT EXISTS product_recalls (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Dados do recall
    product_id BIGINT UNSIGNED NULL, -- NULL = recall geral
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT NOT NULL,
    
    -- Critérios
    criterio_tipo ENUM('lote', 'serie', 'data_fabricacao', 'data_compra', 'fornecedor', 'geral') NOT NULL,
    criterio_valor VARCHAR(255) NULL, -- Número do lote, série, data, etc
    
    -- Status
    status ENUM('rascunho', 'ativo', 'concluido', 'cancelado') DEFAULT 'rascunho',
    data_inicio DATE NULL,
    data_fim DATE NULL,
    
    -- Impacto
    lotes_afetados INT DEFAULT 0,
    series_afetadas INT DEFAULT 0,
    clientes_afetados INT DEFAULT 0,
    
    -- Metadados
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_status (status),
    INDEX idx_criterio (criterio_tipo, criterio_valor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clientes afetados por recall
CREATE TABLE IF NOT EXISTS recall_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    recall_id BIGINT UNSIGNED NOT NULL,
    
    -- Cliente
    cliente_id BIGINT UNSIGNED NOT NULL,
    nome_cliente VARCHAR(255) NOT NULL,
    email_cliente VARCHAR(255) NULL,
    telefone_cliente VARCHAR(50) NULL,
    
    -- Produto
    product_id BIGINT UNSIGNED NOT NULL,
    lote_id BIGINT UNSIGNED NULL,
    serial_id BIGINT UNSIGNED NULL,
    venda_id BIGINT UNSIGNED NULL,
    
    -- Status de notificação
    notificado BOOLEAN DEFAULT FALSE,
    data_notificacao DATETIME NULL,
    resposta_cliente ENUM('pendente', 'devolveu', 'recusou', 'sem_resposta') DEFAULT 'pendente',
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_recall (recall_id),
    INDEX idx_cliente (cliente_id),
    INDEX idx_notificado (notificado),
    FOREIGN KEY (recall_id) REFERENCES product_recalls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CONFIGURAÇÕES
-- ============================================

CREATE TABLE IF NOT EXISTS lotes_series_config (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    -- Política de estoque
    politica_estoque ENUM('fifo', 'fefo', 'lifo', 'manual') DEFAULT 'fifo',
    alerta_validade_dias INT DEFAULT 30, -- Alertar X dias antes do vencimento
    bloquear_vencido BOOLEAN DEFAULT TRUE,
    
    -- Validações
    validar_serie_duplicada BOOLEAN DEFAULT TRUE,
    validar_lote_duplicado BOOLEAN DEFAULT TRUE,
    obrigar_lote_entrada BOOLEAN DEFAULT FALSE,
    obrigar_serie_entrada BOOLEAN DEFAULT FALSE,
    
    -- Metadados
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

