-- Tabelas para funcionalidades mobile

-- Metas e Objetivos
CREATE TABLE IF NOT EXISTS goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    tipo ENUM('vendas', 'clientes', 'pedidos') NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    valor_meta DECIMAL(18,2) NOT NULL,
    valor_atual DECIMAL(18,2) DEFAULT 0.00,
    periodo ENUM('diario', 'semanal', 'mensal', 'anual') NOT NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL,
    vendedor_id BIGINT UNSIGNED NULL,
    
    ativo BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_vendedor (vendedor_id),
    INDEX idx_tipo (tipo),
    INDEX idx_ativo (ativo),
    INDEX idx_periodo (data_inicio, data_fim)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comissões
CREATE TABLE IF NOT EXISTS commissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    order_id BIGINT UNSIGNED NULL,
    vendedor_id BIGINT UNSIGNED NULL,
    valor_pedido DECIMAL(18,2) NOT NULL,
    percentual_comissao DECIMAL(5,2) NOT NULL,
    valor_comissao DECIMAL(18,2) NOT NULL,
    status ENUM('pendente', 'paga', 'cancelada') DEFAULT 'pendente',
    data_calculo DATETIME NOT NULL,
    data_pagamento DATETIME NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_order (order_id),
    INDEX idx_vendedor (vendedor_id),
    INDEX idx_status (status),
    INDEX idx_data_calculo (data_calculo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Check-ins/Check-outs
CREATE TABLE IF NOT EXISTS checkins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    
    client_id BIGINT UNSIGNED NULL,
    tipo ENUM('checkin', 'checkout') NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    accuracy FLOAT NULL,
    endereco VARCHAR(500) NULL,
    observacoes TEXT NULL,
    vendedor_id BIGINT UNSIGNED NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_tenant (tenant_id),
    INDEX idx_client (client_id),
    INDEX idx_vendedor (vendedor_id),
    INDEX idx_tipo (tipo),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Adicionar campos de coordenadas em clients se não existirem
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'clients' 
     AND COLUMN_NAME = 'latitude') > 0,
    'SELECT 1',
    'ALTER TABLE clients ADD COLUMN latitude DECIMAL(10,8) NULL, ADD COLUMN longitude DECIMAL(11,8) NULL'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

