CREATE TABLE admins (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 nome VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 senha_hash VARCHAR(255) NOT NULL,
 ativo TINYINT(1) NOT NULL DEFAULT 1,
 criado_em DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE revendedores (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 nome VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 senha_hash VARCHAR(255) NOT NULL,
 saldo DECIMAL(12,2) NOT NULL DEFAULT 0,
 ativo TINYINT(1) NOT NULL DEFAULT 1,
 criado_em DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE consultas_apis (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 nome VARCHAR(160) NOT NULL,
 nome_fornecedor VARCHAR(160) NOT NULL UNIQUE,
 custo DECIMAL(12,2) NOT NULL DEFAULT 0,
 preco DECIMAL(12,2) NOT NULL DEFAULT 0,
 ativo TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE consultas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 revendedor_id INT UNSIGNED NULL,
 api_id INT UNSIGNED NOT NULL,
 api_name VARCHAR(160) NOT NULL,
 identifier VARCHAR(80) NOT NULL,
 tipo VARCHAR(20) NOT NULL DEFAULT 'placa',
 valor_custo DECIMAL(12,2) NOT NULL DEFAULT 0,
 valor_venda DECIMAL(12,2) NOT NULL DEFAULT 0,
 resposta_json LONGTEXT NULL,
 documento_pdf LONGBLOB NULL,
 criado_em DATETIME NOT NULL,
 INDEX(revendedor_id),
 INDEX(api_id),
 INDEX(criado_em),
 CONSTRAINT fk_cons_rev FOREIGN KEY (revendedor_id) REFERENCES revendedores(id) ON DELETE SET NULL,
 CONSTRAINT fk_cons_api FOREIGN KEY (api_id) REFERENCES consultas_apis(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO consultas_apis(nome,nome_fornecedor,custo,preco) VALUES
('VIP CAR','VIP CAR',0,29.90),
('Histórico de Roubo e Furto','HISTORICO DE ROUBO E FURTO',0,19.90),
('Proprietário Atual','PROPRIETARIO ATUAL',0,19.90),
('Consulta Veicular Completa','CONSULTA VEICULAR COMPLETA',0,49.90),
('CRV Veicular','CRV VEICULAR',0,29.90),
('Leilão','LEILAO',0,29.90),
('Leilão Completo + Score + Sinistro','LEILAO COMPLETO + SCORE + SINISTRO',0,59.90),
('Renajud','RENAJUD',0,29.90),
('Renainf - Infrações Completa','RENAINF - INFRACOES COMPLETA',0,29.90),
('Gravame','GRAVAME',0,29.90),
('CRLV-e AL','CRLV-e AL',0,39.90),
('CRLV-e AP','CRLV-e AP',0,39.90),
('CRLV-e GO','CRLV-e GO',0,39.90),
('CRLV-e MA','CRLV-e MA',0,39.90),
('CRLV-e MG','CRLV-e MG',0,39.90),
('CRLV-e MS','CRLV-e MS',0,39.90),
('CRLV-e MT','CRLV-e MT',0,39.90),
('CRLV-e PI','CRLV-e PI',0,39.90),
('CRLV-e PR','CRLV-e PR',0,39.90),
('CRLV-e RO','CRLV-e RO',0,39.90),
('CRLV-e RR','CRLV-e RR',0,39.90),
('CRLV-e SE','CRLV-e SE',0,39.90),
('CRLV-e TO','CRLV-e TO',0,39.90);