-- ================================================================
-- TERCEIRO FRETES APP - ESTRUTURA DO BANCO DE DADOS
-- Data: 13/01/2026
-- Prefixo: app_fretes_
-- ================================================================

-- ================================================================
-- 1. TABELA: app_fretes_motoristas (Motoristas Autônomos)
-- ================================================================
CREATE TABLE app_fretes_motoristas (
    id INT(11) NOT NULL AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    cpf VARCHAR(14) NOT NULL UNIQUE,
    rg VARCHAR(20) NULL,
    telefone VARCHAR(20) NOT NULL,
    telefone_secundario VARCHAR(20) NULL,
    email VARCHAR(255) NULL,

    -- Foto
    foto_perfil VARCHAR(255) NULL,

    -- Endereço
    cep VARCHAR(10) NULL,
    endereco VARCHAR(255) NULL,
    numero VARCHAR(20) NULL,
    complemento VARCHAR(100) NULL,
    bairro VARCHAR(100) NULL,
    cidade VARCHAR(100) NULL,
    estado VARCHAR(2) NULL,

    -- CNH
    cnh_numero VARCHAR(20) NULL,
    cnh_categoria VARCHAR(5) NULL COMMENT 'A, B, C, D, E, AB, etc',
    cnh_validade DATE NULL,

    -- Dados Bancários
    banco_nome VARCHAR(100) NULL,
    banco_codigo VARCHAR(10) NULL,
    agencia VARCHAR(20) NULL,
    conta VARCHAR(30) NULL,
    tipo_conta ENUM('corrente', 'poupanca') NULL,
    titular_conta VARCHAR(255) NULL,
    cpf_titular VARCHAR(14) NULL,

    -- PIX
    pix_tipo ENUM('cpf', 'cnpj', 'telefone', 'email', 'chave_aleatoria') NULL,
    pix_chave VARCHAR(255) NULL,

    -- Status e controle
    status ENUM('pendente', 'em_analise', 'aprovado', 'bloqueado', 'rejeitado') DEFAULT 'pendente',
    motivo_rejeicao TEXT NULL,
    aprovado_por INT(11) NULL COMMENT 'ID do usuário admin que aprovou',
    aprovado_em DATETIME NULL,

    -- Token autenticação
    token VARCHAR(500) NULL,

    ativo TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_cpf (cpf),
    INDEX idx_telefone (telefone),
    INDEX idx_status (status),
    INDEX idx_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 2. TABELA: app_fretes_motoristas_referencias (Contatos de Referência)
-- ================================================================
CREATE TABLE app_fretes_motoristas_referencias (
    id INT(11) NOT NULL AUTO_INCREMENT,
    motorista_id INT(11) NOT NULL,

    nome VARCHAR(255) NOT NULL,
    telefone VARCHAR(20) NOT NULL,
    parentesco VARCHAR(50) NULL COMMENT 'Amigo, Familiar, Ex-empregador, etc',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_motorista (motorista_id),
    CONSTRAINT fk_ref_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 3. TABELA: app_fretes_veiculos (Veículos - Cavalo/Carreta/Truck/Van)
-- ================================================================
CREATE TABLE app_fretes_veiculos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    motorista_id INT(11) NOT NULL,

    -- Tipo do veículo
    tipo ENUM('cavalo', 'carreta', 'truck', 'toco', '3/4', 'van', 'furgao', 'bitrem', 'rodotrem', 'outro') NOT NULL,

    -- Dados do veículo
    placa VARCHAR(10) NOT NULL,
    renavam VARCHAR(20) NULL,
    chassi VARCHAR(50) NULL,

    marca VARCHAR(50) NOT NULL,
    modelo VARCHAR(100) NOT NULL,
    ano_fabricacao INT(4) NULL,
    ano_modelo INT(4) NULL,
    cor VARCHAR(30) NULL,

    -- Tipo de carroceria (para carretas e trucks)
    carroceria ENUM('bau', 'sider', 'graneleiro', 'tanque', 'cegonha', 'prancha', 'aberta', 'container', 'refrigerado', 'outro') NULL,

    -- Capacidades
    capacidade_kg DECIMAL(10,2) NULL COMMENT 'Capacidade em KG',
    capacidade_m3 DECIMAL(10,2) NULL COMMENT 'Capacidade em M³',
    comprimento DECIMAL(5,2) NULL COMMENT 'Em metros',
    largura DECIMAL(5,2) NULL COMMENT 'Em metros',
    altura DECIMAL(5,2) NULL COMMENT 'Em metros',

    -- ANTT/RNTRC
    antt_numero VARCHAR(30) NULL,
    antt_validade DATE NULL,

    -- Rastreador
    tem_rastreador TINYINT(1) DEFAULT 0,
    empresa_rastreador VARCHAR(100) NULL,

    -- Validação
    status ENUM('pendente', 'aprovado', 'bloqueado') DEFAULT 'pendente',
    aprovado_por INT(11) NULL,
    aprovado_em DATETIME NULL,

    ativo TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_motorista (motorista_id),
    INDEX idx_placa (placa),
    INDEX idx_tipo (tipo),
    INDEX idx_status (status),
    CONSTRAINT fk_veiculo_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 4. TABELA: app_fretes_documentos (Documentos Motorista e Veículo)
-- ================================================================
CREATE TABLE app_fretes_documentos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    motorista_id INT(11) NOT NULL,
    veiculo_id INT(11) NULL COMMENT 'Se NULL, é documento do motorista',

    tipo ENUM(
        -- Documentos do motorista
        'cnh_frente',
        'cnh_verso',
        'selfie_cnh',
        'comprovante_residencia',
        -- Documentos do veículo
        'crlv',
        'antt',
        'veiculo_frente',
        'veiculo_traseira',
        'veiculo_lateral_esquerda',
        'veiculo_lateral_direita',
        'veiculo_interno',
        -- Outros
        'outros'
    ) NOT NULL,

    descricao VARCHAR(255) NULL,
    arquivo VARCHAR(255) NOT NULL COMMENT 'Path do arquivo no servidor',
    arquivo_nome VARCHAR(255) NULL COMMENT 'Nome original do arquivo',

    validade DATE NULL COMMENT 'Para documentos com vencimento',

    -- Validação pelo admin
    status ENUM('pendente', 'aprovado', 'rejeitado') DEFAULT 'pendente',
    validado_por INT(11) NULL,
    validado_em DATETIME NULL,
    motivo_rejeicao VARCHAR(500) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_motorista (motorista_id),
    INDEX idx_veiculo (veiculo_id),
    INDEX idx_tipo (tipo),
    INDEX idx_status (status),
    CONSTRAINT fk_doc_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE,
    CONSTRAINT fk_doc_veiculo FOREIGN KEY (veiculo_id)
        REFERENCES app_fretes_veiculos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 5. TABELA: app_fretes (Fretes)
-- ================================================================
CREATE TABLE app_fretes (
    id INT(11) NOT NULL AUTO_INCREMENT,

    -- Vínculo com cotação existente
    cotacao_id BIGINT UNSIGNED NULL COMMENT 'FK para solcotacao',

    -- Código único do frete
    codigo VARCHAR(20) NOT NULL UNIQUE COMMENT 'Ex: TF20260113001',

    -- Motorista e veículos
    motorista_id INT(11) NULL,
    veiculo_id INT(11) NULL COMMENT 'Cavalo ou veículo único',
    carreta_id INT(11) NULL COMMENT 'Carreta (se usar)',

    -- Origem
    origem_cidade VARCHAR(100) NOT NULL,
    origem_estado VARCHAR(2) NOT NULL,
    origem_endereco VARCHAR(255) NULL,
    origem_cep VARCHAR(10) NULL,
    origem_latitude DECIMAL(10,8) NULL,
    origem_longitude DECIMAL(11,8) NULL,
    origem_contato_nome VARCHAR(255) NULL,
    origem_contato_telefone VARCHAR(20) NULL,

    -- Destino
    destino_cidade VARCHAR(100) NOT NULL,
    destino_estado VARCHAR(2) NOT NULL,
    destino_endereco VARCHAR(255) NULL,
    destino_cep VARCHAR(10) NULL,
    destino_latitude DECIMAL(10,8) NULL,
    destino_longitude DECIMAL(11,8) NULL,
    destino_contato_nome VARCHAR(255) NULL,
    destino_contato_telefone VARCHAR(20) NULL,

    -- Cliente
    cliente_nome VARCHAR(255) NOT NULL,
    cliente_documento VARCHAR(20) NULL COMMENT 'CNPJ ou CPF',
    cliente_telefone VARCHAR(20) NULL,

    -- Carga
    tipo_carga VARCHAR(100) NOT NULL,
    descricao_carga TEXT NULL,
    peso_kg DECIMAL(10,2) NULL,
    volumes INT(11) NULL,
    valor_nota DECIMAL(15,2) NULL COMMENT 'Valor da mercadoria',

    -- Dimensões da carga
    carga_comprimento DECIMAL(5,2) NULL,
    carga_largura DECIMAL(5,2) NULL,
    carga_altura DECIMAL(5,2) NULL,

    -- Veículo solicitado
    tipo_veiculo_solicitado VARCHAR(100) NULL,
    tipo_carroceria_solicitada VARCHAR(100) NULL,

    -- Rastreamento
    exige_rastreador TINYINT(1) DEFAULT 0,
    info_rastreamento TEXT NULL,

    -- Valores
    valor_frete DECIMAL(10,2) NOT NULL,
    valor_pedagio DECIMAL(10,2) DEFAULT 0,
    pedagio_incluso TINYINT(1) DEFAULT 0 COMMENT 'Se pedágio já está no valor do frete',

    -- Forma de pagamento
    forma_pagamento ENUM('a_vista', '50_50', '60_40', '70_30', '80_20') DEFAULT '70_30',
    percentual_adiantamento INT(3) DEFAULT 70,
    valor_adiantamento DECIMAL(10,2) NULL,
    valor_saldo DECIMAL(10,2) NULL,

    -- Datas
    data_liberacao DATETIME NULL COMMENT 'Quando pode carregar',
    data_prevista_coleta DATE NULL,
    data_prevista_entrega DATE NULL,
    data_carregamento DATETIME NULL,
    data_descarregamento DATETIME NULL,

    -- Status detalhado
    status ENUM(
        'atribuido',            -- Frete criado e atribuído ao motorista
        'contrato_pendente',    -- Aguardando aceite do contrato
        'contrato_assinado',    -- Contrato assinado
        'adiantamento_pago',    -- Adiantamento liberado
        'em_transito',          -- Indo para carregar
        'carregado',            -- Carregou a mercadoria
        'em_viagem',            -- Viajando para destino
        'descarregado',         -- Descarregou
        'saldo_pago',           -- Saldo liberado
        'finalizado',           -- Frete concluído
        'cancelado'             -- Cancelado
    ) DEFAULT 'atribuido',

    motivo_cancelamento TEXT NULL,
    cancelado_por INT(11) NULL,
    cancelado_em DATETIME NULL,

    -- KM
    km_previsto DECIMAL(10,2) NULL,
    km_total DECIMAL(10,2) DEFAULT 0 COMMENT 'Calculado pelo tracking',

    -- Observações
    observacoes TEXT NULL,
    observacoes_internas TEXT NULL COMMENT 'Não visível ao motorista',

    -- Controle
    criado_por INT(11) NULL COMMENT 'ID do usuário admin',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_cotacao (cotacao_id),
    INDEX idx_motorista (motorista_id),
    INDEX idx_veiculo (veiculo_id),
    INDEX idx_carreta (carreta_id),
    INDEX idx_codigo (codigo),
    INDEX idx_status (status),
    INDEX idx_data_liberacao (data_liberacao),
    CONSTRAINT fk_frete_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE SET NULL,
    CONSTRAINT fk_frete_veiculo FOREIGN KEY (veiculo_id)
        REFERENCES app_fretes_veiculos(id) ON DELETE SET NULL,
    CONSTRAINT fk_frete_carreta FOREIGN KEY (carreta_id)
        REFERENCES app_fretes_veiculos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 6. TABELA: app_fretes_contratos (Contrato + Assinatura Digital)
-- ================================================================
CREATE TABLE app_fretes_contratos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    frete_id INT(11) NOT NULL,
    motorista_id INT(11) NOT NULL,

    -- Texto do contrato (snapshot do momento da assinatura)
    texto_contrato LONGTEXT NOT NULL,

    -- Aceite
    aceite TINYINT(1) DEFAULT 0,
    aceite_ip VARCHAR(45) NULL,
    aceite_dispositivo VARCHAR(255) NULL COMMENT 'Modelo do celular',
    aceite_em DATETIME NULL,

    -- Assinatura digital
    assinatura_arquivo VARCHAR(255) NULL COMMENT 'Path da imagem da assinatura',
    assinatura_em DATETIME NULL,

    -- Localização no momento da assinatura
    latitude DECIMAL(10,8) NULL,
    longitude DECIMAL(11,8) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_frete (frete_id),
    INDEX idx_motorista (motorista_id),
    CONSTRAINT fk_contrato_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE CASCADE,
    CONSTRAINT fk_contrato_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 7. TABELA: app_fretes_pagamentos (Histórico Financeiro)
-- ================================================================
CREATE TABLE app_fretes_pagamentos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    frete_id INT(11) NOT NULL,
    motorista_id INT(11) NOT NULL,

    tipo ENUM('adiantamento', 'saldo', 'diaria', 'bonus', 'desconto', 'outro') NOT NULL,

    valor DECIMAL(10,2) NOT NULL,

    -- Dados do pagamento
    forma_pagamento ENUM('pix', 'transferencia', 'deposito', 'dinheiro', 'outro') NULL,
    comprovante VARCHAR(255) NULL COMMENT 'Path do comprovante',

    -- Dados bancários usados
    banco VARCHAR(100) NULL,
    agencia VARCHAR(20) NULL,
    conta VARCHAR(30) NULL,
    pix_chave VARCHAR(255) NULL,

    observacao TEXT NULL,

    -- Controle
    status ENUM('pendente', 'pago', 'cancelado') DEFAULT 'pendente',
    pago_em DATETIME NULL,
    pago_por INT(11) NULL COMMENT 'ID do usuário admin',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_frete (frete_id),
    INDEX idx_motorista (motorista_id),
    INDEX idx_tipo (tipo),
    INDEX idx_status (status),
    CONSTRAINT fk_pagamento_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pagamento_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 8. TABELA: app_fretes_eventos (Carregamento/Descarregamento + Fotos)
-- ================================================================
CREATE TABLE app_fretes_eventos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    frete_id INT(11) NOT NULL,

    tipo ENUM(
        'contrato_assinado',
        'adiantamento_recebido',
        'iniciou_transito',
        'chegou_origem',
        'carregando',
        'carregado',
        'iniciou_viagem',
        'chegou_destino',
        'descarregando',
        'descarregado',
        'ocorrencia',
        'pausa',
        'retomou'
    ) NOT NULL,

    -- Localização
    latitude DECIMAL(10,8) NULL,
    longitude DECIMAL(11,8) NULL,
    endereco VARCHAR(255) NULL COMMENT 'Endereço reverso do GPS',

    observacao TEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_frete (frete_id),
    INDEX idx_tipo (tipo),
    INDEX idx_created (created_at),
    CONSTRAINT fk_evento_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 9. TABELA: app_fretes_eventos_fotos (Fotos dos Eventos)
-- ================================================================
CREATE TABLE app_fretes_eventos_fotos (
    id INT(11) NOT NULL AUTO_INCREMENT,
    evento_id INT(11) NOT NULL,

    arquivo VARCHAR(255) NOT NULL COMMENT 'Path da foto',
    descricao VARCHAR(255) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_evento (evento_id),
    CONSTRAINT fk_foto_evento FOREIGN KEY (evento_id)
        REFERENCES app_fretes_eventos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 10. TABELA: app_fretes_tracking (Rastreamento GPS)
-- ================================================================
CREATE TABLE app_fretes_tracking (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    frete_id INT(11) NOT NULL,

    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,

    speed DECIMAL(5,2) NULL COMMENT 'Velocidade em km/h',
    bearing DECIMAL(5,2) NULL COMMENT 'Direção em graus',
    accuracy DECIMAL(6,2) NULL COMMENT 'Precisão em metros',
    altitude DECIMAL(8,2) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_frete (frete_id),
    INDEX idx_created (created_at),
    CONSTRAINT fk_tracking_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 11. TABELA: app_fretes_estadias (Controle Free Time 12h)
-- ================================================================
CREATE TABLE app_fretes_estadias (
    id INT(11) NOT NULL AUTO_INCREMENT,
    frete_id INT(11) NOT NULL,

    tipo ENUM('carregamento', 'descarregamento') NOT NULL,

    -- Horários
    hora_chegada DATETIME NOT NULL COMMENT 'Quando chegou no local',
    hora_liberacao DATETIME NULL COMMENT 'Quando foi liberado',

    -- Free time (12h padrão)
    free_time_horas INT(3) DEFAULT 12,

    -- Cálculo
    horas_aguardando DECIMAL(5,2) NULL COMMENT 'Total de horas esperando',
    horas_excedentes DECIMAL(5,2) NULL COMMENT 'Horas além do free time',

    -- Diária
    valor_diaria DECIMAL(10,2) NULL COMMENT 'Valor a pagar por excesso',
    diaria_aprovada TINYINT(1) DEFAULT 0,
    aprovada_por INT(11) NULL,
    aprovada_em DATETIME NULL,

    observacao TEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_frete (frete_id),
    INDEX idx_tipo (tipo),
    CONSTRAINT fk_estadia_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 12. TABELA: app_fretes_notificacoes (Push Notifications)
-- ================================================================
CREATE TABLE app_fretes_notificacoes (
    id INT(11) NOT NULL AUTO_INCREMENT,
    motorista_id INT(11) NOT NULL,

    titulo VARCHAR(255) NOT NULL,
    mensagem TEXT NOT NULL,

    tipo ENUM('info', 'frete', 'pagamento', 'documento', 'contrato', 'alerta', 'sistema') DEFAULT 'info',

    -- Relacionamentos opcionais
    frete_id INT(11) NULL,

    -- Dados extras (JSON)
    dados JSON NULL,

    -- Status
    lido TINYINT(1) DEFAULT 0,
    lido_em DATETIME NULL,

    -- Push enviado
    push_enviado TINYINT(1) DEFAULT 0,
    push_enviado_em DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_motorista (motorista_id),
    INDEX idx_frete (frete_id),
    INDEX idx_tipo (tipo),
    INDEX idx_lido (lido),
    CONSTRAINT fk_notif_motorista FOREIGN KEY (motorista_id)
        REFERENCES app_fretes_motoristas(id) ON DELETE CASCADE,
    CONSTRAINT fk_notif_frete FOREIGN KEY (frete_id)
        REFERENCES app_fretes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- 13. TABELA: app_fretes_codigos_verificacao (Códigos WhatsApp)
-- ================================================================
CREATE TABLE app_fretes_codigos_verificacao (
    id INT(11) NOT NULL AUTO_INCREMENT,
    motorista_id INT(11) NULL,
    cpf VARCHAR(14) NOT NULL,
    telefone VARCHAR(20) NOT NULL,

    codigo VARCHAR(6) NOT NULL,

    usado TINYINT(1) DEFAULT 0,
    usado_em DATETIME NULL,

    expira_em DATETIME NOT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_cpf (cpf),
    INDEX idx_codigo (codigo),
    INDEX idx_expira (expira_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ================================================================
-- VIEWS ÚTEIS
-- ================================================================

-- View: Motoristas com estatísticas
CREATE OR REPLACE VIEW vw_app_fretes_motoristas_stats AS
SELECT
    m.*,
    (SELECT COUNT(*) FROM app_fretes WHERE motorista_id = m.id) as total_fretes,
    (SELECT COUNT(*) FROM app_fretes WHERE motorista_id = m.id AND status = 'finalizado') as fretes_finalizados,
    (SELECT COUNT(*) FROM app_fretes WHERE motorista_id = m.id AND status = 'cancelado') as fretes_cancelados,
    (SELECT SUM(valor_frete) FROM app_fretes WHERE motorista_id = m.id AND status = 'finalizado') as valor_total_fretes,
    (SELECT SUM(km_total) FROM app_fretes WHERE motorista_id = m.id AND status = 'finalizado') as km_total,
    (SELECT COUNT(*) FROM app_fretes_veiculos WHERE motorista_id = m.id AND ativo = 1) as total_veiculos
FROM app_fretes_motoristas m;


-- View: Fretes ativos
CREATE OR REPLACE VIEW vw_app_fretes_ativos AS
SELECT
    f.*,
    m.nome as motorista_nome,
    m.telefone as motorista_telefone,
    v.placa as veiculo_placa,
    v.modelo as veiculo_modelo,
    c.placa as carreta_placa,
    c.modelo as carreta_modelo,
    (SELECT COUNT(*) FROM app_fretes_tracking WHERE frete_id = f.id) as pontos_tracking,
    (SELECT created_at FROM app_fretes_tracking WHERE frete_id = f.id ORDER BY id DESC LIMIT 1) as ultima_posicao_em
FROM app_fretes f
LEFT JOIN app_fretes_motoristas m ON f.motorista_id = m.id
LEFT JOIN app_fretes_veiculos v ON f.veiculo_id = v.id
LEFT JOIN app_fretes_veiculos c ON f.carreta_id = c.id
WHERE f.status NOT IN ('finalizado', 'cancelado');


-- View: Pagamentos pendentes
CREATE OR REPLACE VIEW vw_app_fretes_pagamentos_pendentes AS
SELECT
    p.id,
    p.frete_id,
    p.motorista_id,
    p.tipo,
    p.valor,
    p.forma_pagamento,
    p.comprovante,
    p.banco as pagamento_banco,
    p.agencia as pagamento_agencia,
    p.conta as pagamento_conta,
    p.pix_chave as pagamento_pix_chave,
    p.observacao,
    p.status,
    p.pago_em,
    p.pago_por,
    p.created_at,
    p.updated_at,
    f.codigo as frete_codigo,
    f.origem_cidade,
    f.destino_cidade,
    m.nome as motorista_nome,
    m.telefone as motorista_telefone,
    m.pix_tipo as motorista_pix_tipo,
    m.pix_chave as motorista_pix_chave
FROM app_fretes_pagamentos p
JOIN app_fretes f ON p.frete_id = f.id
JOIN app_fretes_motoristas m ON p.motorista_id = m.id
WHERE p.status = 'pendente';


-- ================================================================
-- TRIGGERS
-- ================================================================

-- Trigger: Calcular valores de adiantamento/saldo ao inserir frete
DELIMITER //
CREATE TRIGGER trg_app_fretes_calcular_valores
BEFORE INSERT ON app_fretes
FOR EACH ROW
BEGIN
    IF NEW.percentual_adiantamento IS NOT NULL AND NEW.valor_frete IS NOT NULL THEN
        SET NEW.valor_adiantamento = (NEW.valor_frete * NEW.percentual_adiantamento) / 100;
        SET NEW.valor_saldo = NEW.valor_frete - NEW.valor_adiantamento;
    END IF;
END//
DELIMITER ;

-- Trigger: Atualizar valores ao atualizar frete
DELIMITER //
CREATE TRIGGER trg_app_fretes_atualizar_valores
BEFORE UPDATE ON app_fretes
FOR EACH ROW
BEGIN
    IF NEW.percentual_adiantamento != OLD.percentual_adiantamento
       OR NEW.valor_frete != OLD.valor_frete THEN
        SET NEW.valor_adiantamento = (NEW.valor_frete * NEW.percentual_adiantamento) / 100;
        SET NEW.valor_saldo = NEW.valor_frete - NEW.valor_adiantamento;
    END IF;
END//
DELIMITER ;


-- ================================================================
-- DADOS INICIAIS (para teste)
-- ================================================================

-- Inserir um motorista de teste
INSERT INTO app_fretes_motoristas (
    nome, cpf, telefone, email, status,
    cnh_numero, cnh_categoria, cnh_validade,
    cidade, estado
) VALUES (
    'João Motorista Teste',
    '111.222.333-44',
    '(41) 99999-8888',
    'joao.teste@email.com',
    'aprovado',
    '12345678900',
    'E',
    '2027-12-31',
    'Curitiba',
    'PR'
);

-- Inserir referências
INSERT INTO app_fretes_motoristas_referencias (motorista_id, nome, telefone, parentesco) VALUES
(1, 'Maria Silva', '(41) 98888-7777', 'Esposa'),
(1, 'Carlos Transportes', '(41) 97777-6666', 'Ex-empregador');

-- Inserir veículos
INSERT INTO app_fretes_veiculos (
    motorista_id, tipo, placa, marca, modelo, ano_fabricacao, cor,
    capacidade_kg, tem_rastreador, empresa_rastreador, status
) VALUES
(1, 'cavalo', 'ABC-1234', 'Volvo', 'FH 540', 2022, 'Branco', NULL, 1, 'Sascar', 'aprovado'),
(1, 'carreta', 'XYZ-5678', 'Randon', 'Prancha 3 Eixos', 2021, 'Preta', 35000, 0, NULL, 'aprovado');

ALTER TABLE app_fretes_motoristas
ADD COLUMN push_token VARCHAR(500) NULL AFTER token,
ADD COLUMN push_plataforma ENUM('android', 'ios') NULL AFTER push_token;
