-- SpheriGo panel enhancements v25
-- Safe additive migration. No destructive column drops.

CREATE TABLE IF NOT EXISTS default_zones (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  zone_code VARCHAR(60) NOT NULL,
  zone_name VARCHAR(180) NOT NULL,
  city VARCHAR(120) NULL,
  country VARCHAR(120) NOT NULL DEFAULT 'Qatar',
  polygon_json JSON NULL,
  center_latitude DECIMAL(11,8) NULL,
  center_longitude DECIMAL(11,8) NULL,
  color VARCHAR(30) NULL DEFAULT '#0863E6',
  status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  is_delete TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  deleted_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_default_zone_code (zone_code),
  KEY idx_default_zone_status_delete (status, is_delete)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS driver_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  driver_id BIGINT UNSIGNED NOT NULL,
  document_title VARCHAR(150) NULL,
  original_name VARCHAR(255) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_url VARCHAR(500) NOT NULL,
  mime_type VARCHAR(150) NOT NULL,
  file_size INT UNSIGNED NOT NULL DEFAULT 0,
  is_delete TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  deleted_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_driver_docs_driver_delete (driver_id, is_delete),
  KEY idx_driver_docs_tenant_delete (tenant_id, is_delete)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE users ADD COLUMN IF NOT EXISTS user_code VARCHAR(80) NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS default_warehouse_id BIGINT UNSIGNED NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS mobile_device_id VARCHAR(255) NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_users_user_code_global ON users (user_code);
