CREATE DATABASE IF NOT EXISTS vts_v2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE vts_v2;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  mobile VARCHAR(15) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  mobile_verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sessions (
  token CHAR(64) PRIMARY KEY,
  user_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS otp_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(15) NOT NULL,
  code VARCHAR(6) NOT NULL,
  purpose VARCHAR(40) NOT NULL,
  expires_at DATETIME NOT NULL,
  used TINYINT(1) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  public_id VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL DEFAULT 'My Vehicle',
  imei VARCHAR(20) NOT NULL UNIQUE,
  iccid VARCHAR(22) NULL,
  ingest_secret CHAR(64) NOT NULL,
  status ENUM('online','offline') NOT NULL DEFAULT 'offline',
  last_seen DATETIME NULL,
  battery_percent INT NULL
);

CREATE TABLE IF NOT EXISTS device_activation (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('NEW','USED') NOT NULL DEFAULT 'NEW',
  activated_at DATETIME NULL,
  FOREIGN KEY (device_id) REFERENCES devices(id)
);

CREATE TABLE IF NOT EXISTS device_ownership (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  owner_id INT NOT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  FOREIGN KEY (device_id) REFERENCES devices(id),
  FOREIGN KEY (owner_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS device_sharing (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  owner_id INT NOT NULL,
  viewer_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_share (device_id, viewer_id)
);

CREATE TABLE IF NOT EXISTS transfer_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  old_owner_id INT NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  status ENUM('OPEN','USED','CANCELLED') NOT NULL DEFAULT 'OPEN'
);

CREATE TABLE IF NOT EXISTS locations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  recorded_at DATETIME NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  speed DECIMAL(6,2) NULL,
  direction DECIMAL(6,2) NULL,
  accuracy DECIMAL(8,2) NULL,
  INDEX idx_device_time (device_id, recorded_at)
);

CREATE TABLE IF NOT EXISTS geofences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  name VARCHAR(80) NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  radius_m INT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_inside TINYINT(1) NULL
);

CREATE TABLE IF NOT EXISTS position_locks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL UNIQUE,
  lat DECIMAL(10,7) NOT NULL,
  lng DECIMAL(10,7) NOT NULL,
  radius_m INT NOT NULL,
  locked_at DATETIME NOT NULL,
  status ENUM('ON','OFF') NOT NULL DEFAULT 'ON',
  consecutive_outside INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  type VARCHAR(40) NOT NULL,
  message VARCHAR(255) NOT NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_read TINYINT(1) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(80) NOT NULL,
  user_id INT NULL,
  device_id INT NULL,
  detail VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan VARCHAR(40) NOT NULL DEFAULT 'annual',
  starts_at DATE NOT NULL,
  ends_at DATE NOT NULL
);