-- NEXCON MineTrack MySQL schema (save & import via phpMyAdmin)
CREATE DATABASE IF NOT EXISTS minetrack;
USE minetrack;

CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  subdomain VARCHAR(150) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  role VARCHAR(50) DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS vehicles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  vehicle_tag VARCHAR(100) UNIQUE,
  vehicle_number VARCHAR(100),
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hardware (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  device_code VARCHAR(100),
  device_name VARCHAR(150),
  location VARCHAR(255),
  meta JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS maps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  filename VARCHAR(255),
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tracking_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  vehicle_id INT,
  vehicle_tag VARCHAR(100),
  lat DECIMAL(10,6),
  lon DECIMAL(10,6),
  source VARCHAR(50),
  raw_event JSON,
  detected_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL
);

CREATE INDEX idx_trak_tenant_time ON tracking_logs (tenant_id, detected_at);
CREATE INDEX idx_trak_vehicle_time ON tracking_logs (vehicle_id, detected_at);

CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  user_id INT,
  name VARCHAR(255),
  html_content LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS nova_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  user_id INT,
  nova_role VARCHAR(100),
  input_text LONGTEXT,
  output_text LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT,
  service_name VARCHAR(100),
  key_value TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- sample seed
INSERT INTO tenants (name, subdomain) VALUES ('Demo Mine', 'demo');
INSERT INTO users (tenant_id, email, password_hash, role) VALUES (1, 'admin@demo.local', '', 'admin');