CREATE DATABASE IF NOT EXISTS atrapec CHARACTER SET utf8mb4; USE atrapec;
CREATE TABLE provincias(id INT AUTO_INCREMENT PRIMARY KEY,nome VARCHAR(80) UNIQUE);INSERT INTO provincias(nome) VALUES('Sofala');
CREATE TABLE motoristas(id INT AUTO_INCREMENT PRIMARY KEY,nome VARCHAR(150) NOT NULL,carta VARCHAR(50) NOT NULL,telefone VARCHAR(30),provincia_id INT,tel_familiar VARCHAR(30),foto VARCHAR(120),criado_em DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE rotas(id INT AUTO_INCREMENT PRIMARY KEY,nome VARCHAR(120) NOT NULL,estado ENUM('ativa','inativa') DEFAULT 'ativa',criado_em DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE viaturas(id INT AUTO_INCREMENT PRIMARY KEY,matricula VARCHAR(30) NOT NULL UNIQUE,marca VARCHAR(60),modelo VARCHAR(60),motorista_id INT NULL,rota_id INT,estado ENUM('ativa','inativa') DEFAULT 'ativa',FOREIGN KEY(motorista_id) REFERENCES motoristas(id),FOREIGN KEY(rota_id) REFERENCES rotas(id));
CREATE TABLE utilizadores(id INT AUTO_INCREMENT PRIMARY KEY,nome VARCHAR(150),telefone VARCHAR(30),username VARCHAR(100) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,tipo ENUM('ADMINISTRADOR','COBRADOR','MOTORISTA') NOT NULL,motorista_id INT NULL,estado ENUM('ativo','inativo') DEFAULT 'ativo');
CREATE TABLE cobrancas(id INT AUTO_INCREMENT PRIMARY KEY,viatura_id INT NOT NULL,motorista_id INT NOT NULL,cobrador_id INT NOT NULL,rota_id INT NOT NULL,data_cobranca DATE NOT NULL,hora_cobranca TIME NOT NULL,
 valor_total DECIMAL(10,2),valor_atrapec DECIMAL(10,2),valor_comissao DECIMAL(10,2),referencia VARCHAR(60) UNIQUE,externo_status ENUM('PENDENTE','ENVIADO','ERRO') DEFAULT 'PENDENTE',externo_enviado_em DATETIME NULL,externo_resposta TEXT,externo_tentativas INT DEFAULT 0,criado_em DATETIME DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_viatura_dia(viatura_id,data_cobranca),INDEX(data_cobranca),INDEX(externo_status));
CREATE TABLE logs_api(id INT AUTO_INCREMENT PRIMARY KEY,cobranca_id INT,http_code INT,resposta TEXT,criado_em DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE login_tentativas(id INT AUTO_INCREMENT PRIMARY KEY,ip VARCHAR(45),criado_em DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE configuracoes(chave VARCHAR(60) PRIMARY KEY,valor VARCHAR(255));
-- Admin inicial: utilizador "admin" / password "password" (ALTERE JÁ!)
INSERT INTO utilizadores(nome,username,password_hash,tipo) VALUES('Administrador','admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','ADMINISTRADOR');
-- NO SERVIDOR EXTERNO:
-- CREATE TABLE comissoes(id INT AUTO_INCREMENT PRIMARY KEY,referencia VARCHAR(60) UNIQUE,matricula VARCHAR(30),motorista VARCHAR(150),data_cobranca DATE,hora_cobranca TIME,valor DECIMAL(10,2),origem VARCHAR(30),recebido_em DATETIME DEFAULT CURRENT_TIMESTAMP);