-- =====================================================================
-- SeedKey Platform - Schema + Seed
-- =====================================================================



-- =====================================================================
-- companies — oficinas/empresas clientes
-- =====================================================================
CREATE TABLE companies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  cnpj VARCHAR(20) NULL,
  workshop_serial VARCHAR(32) NULL COMMENT 'Serial do tablet workshop, ex 30235W019800',
  workshop_id VARCHAR(8) NULL COMMENT 'Workshop ID 4-char, ex 9800',
  address VARCHAR(255) NULL,
  city VARCHAR(80) NULL,
  state VARCHAR(2) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  max_distance_km DECIMAL(6,2) DEFAULT 50.00 COMMENT 'Raio aceito em km. Acima disso = alerta',
  block_far_usage TINYINT(1) DEFAULT 0 COMMENT 'Se 1, API bloqueia automaticamente uso fora do raio',
  price_per_seedkey DECIMAL(8,2) DEFAULT 0.00 COMMENT 'R$ cobrado por seedkey gerada',
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- users — login na plataforma. Master pode ter company_id NULL.
-- =====================================================================
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('master','company','user') NOT NULL,
  phone VARCHAR(40) NULL,
  active TINYINT(1) DEFAULT 1,
  last_login DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- devices — hardware fisico de cada oficina (>1 por company permitido)
-- =====================================================================
CREATE TABLE devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  hardware_serial VARCHAR(64) NULL,
  device_token VARCHAR(64) NOT NULL UNIQUE COMMENT 'Token de auth do hardware na API',
  notes TEXT NULL,
  active TINYINT(1) DEFAULT 1,
  blocked_reason VARCHAR(255) NULL,
  blocked_at DATETIME NULL,
  last_seen DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_devices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- operation_types — tipo de programacao executada
-- =====================================================================
CREATE TABLE operation_types (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- seedkey_logs — todo uso registrado, base pra billing e relatorios
-- =====================================================================
CREATE TABLE seedkey_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL COMMENT 'Tecnico que operou — pode ser null se hw nao identificar',
  operation_type_id INT UNSIGNED NULL,
  tachograph_serial VARCHAR(40) NULL,
  seedkey VARCHAR(64) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  distance_km DECIMAL(8,2) NULL,
  location_alert TINYINT(1) DEFAULT 0,
  delivered TINYINT(1) DEFAULT 1 COMMENT '0 se bloqueou',
  block_reason VARCHAR(120) NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_company_date (company_id, created_at),
  INDEX idx_logs_device_date (device_id, created_at),
  INDEX idx_logs_alert (location_alert, created_at),
  INDEX idx_logs_optype (operation_type_id),
  CONSTRAINT fk_logs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_logs_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_logs_optype FOREIGN KEY (operation_type_id) REFERENCES operation_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- api_blocks — log de bloqueios manuais/automaticos
-- =====================================================================
CREATE TABLE api_blocks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  reason VARCHAR(255) NOT NULL,
  blocked_by_user_id INT UNSIGNED NULL,
  resolved_at DATETIME NULL,
  resolved_by_user_id INT UNSIGNED NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_blocks_company (company_id),
  INDEX idx_blocks_device (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- settings — chave/valor global
-- =====================================================================
CREATE TABLE settings (
  k VARCHAR(80) PRIMARY KEY,
  v TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- SEED
-- =====================================================================

INSERT INTO operation_types (code, name, description) VALUES
  ('fw_update',   'Atualizacao FW',         'Atualizacao de firmware do tacografo'),
  ('param_prog',  'Programacao Parametro',  'Gravacao de parametros (DIDs) no tacografo'),
  ('library',     'Biblioteca OEM',         'Programacao de biblioteca veicular (Scania/Volvo/MAN/etc)'),
  ('calibration', 'Calibracao',             'Calibracao legal do tacografo'),
  ('data_download','Download Dados',         'Download de dados de motorista/veiculo'),
  ('seal_change', 'Troca de Selo',          'Troca de selo apos rompimento'),
  ('other',       'Outro',                  'Outras operacoes');

INSERT INTO settings (k, v) VALUES
  ('platform_name', 'SeedKey Manager'),
  ('default_max_distance_km', '50'),
  ('alert_threshold_count', '3');

-- Companies
INSERT INTO companies
  (name, cnpj, workshop_serial, workshop_id, address, city, state, lat, lng, max_distance_km, price_per_seedkey, active)
VALUES
  ('Oficina Alpha Tacografos Ltda', '12.345.678/0001-99', '30235W019800', '9800',
   'Av. Paulista, 1000', 'Sao Paulo', 'SP', -23.5614, -46.6562, 50.00, 12.50, 1),
  ('Oficina Beta Diagnostica EIRELI', '98.765.432/0001-11', '30235W019852', '9852',
   'Rua das Industrias, 200', 'Curitiba', 'PR', -25.4284, -49.2733, 30.00, 15.00, 1);

-- Users (senhas: master123, alpha123, tecnico123, beta123)
-- Gerados com password_hash() PHP 8.x default (bcrypt)
INSERT INTO users (company_id, name, email, password_hash, role, active) VALUES
  (NULL, 'Master Admin',    'master@seedkey.local',
   '$2y$10$bj/3FFFiUMcdJ8VlbjxL2.AVm7nGwTNRC1bSpzJfaYr2nWh.bDDhq', 'master', 1),
  (1,    'Admin Alpha',     'admin@oficinaalpha.com.br',
   '$2y$10$Sl5GsuVRGZ8x4xJqRWqzWuogD96POAVuyiqDfqkkN6Aoiwf4UTYNK', 'company', 1),
  (1,    'Tecnico Alpha',   'tecnico@oficinaalpha.com.br',
   '$2y$10$5opwfldz6Lhh2zXxxbsxn.lp19zQ6yECfdMc4UmrZ2HXc89E1f.4u', 'user', 1),
  (2,    'Admin Beta',      'admin@oficinabeta.com.br',
   '$2y$10$RWHfdYDPjsAUd6UY7BfsxewvqIWxRiCcKBahkywjzZ6cWX8tnVJOq', 'company', 1);

-- Devices (cada device com um token unico)
INSERT INTO devices (company_id, name, hardware_serial, device_token, active) VALUES
  (1, 'Hardware Alpha #1', 'HW-ALPHA-001', 'tok_alpha_001_xyz789abc123', 1),
  (1, 'Hardware Alpha #2', 'HW-ALPHA-002', 'tok_alpha_002_def456ghi789', 1),
  (2, 'Hardware Beta #1',  'HW-BETA-001',  'tok_beta_001_jkl321mno654',  1);

-- Logs de exemplo
INSERT INTO seedkey_logs
  (company_id, device_id, user_id, operation_type_id, tachograph_serial, seedkey,
   lat, lng, distance_km, location_alert, delivered, ip_address, created_at)
VALUES
  (1, 1, 3, 1, '45275260', '87403eea3031b5cc53ca01835bd8289b',
   -23.5614, -46.6562, 0.5, 0, 1, '192.168.1.10', DATE_SUB(NOW(), INTERVAL 5 HOUR)),
  (1, 1, 3, 2, '45001612', '62fb5114eecba0061f1b8fc95524e89c',
   -23.5620, -46.6570, 0.8, 0, 1, '192.168.1.10', DATE_SUB(NOW(), INTERVAL 1 DAY)),
  (1, 2, 3, 3, '45364328', '051475630d1be0eee601fda1e8cd99b6',
   -22.9068, -43.1729, 360.4, 1, 1, '189.5.5.5',   DATE_SUB(NOW(), INTERVAL 2 DAY)),
  (2, 3, NULL, 1, '45394554', 'd20a3b94f9cb5bcc3667ff99027a07bf',
   -25.4290, -49.2740, 0.1, 0, 1, '200.10.10.5',  DATE_SUB(NOW(), INTERVAL 3 HOUR));
