CREATE TABLE IF NOT EXISTS admins (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS applications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  registration_no VARCHAR(32) UNIQUE DEFAULT NULL,
  category_code VARCHAR(4) NOT NULL,
  category_name VARCHAR(80) NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  kecamatan VARCHAR(80) NOT NULL,
  village VARCHAR(100) DEFAULT NULL,
  address TEXT NOT NULL,
  business_name VARCHAR(180) DEFAULT NULL,
  service_details TEXT DEFAULT NULL,
  vehicle_type VARCHAR(80) DEFAULT NULL,
  vehicle_year VARCHAR(10) DEFAULT NULL,
  license_number VARCHAR(80) DEFAULT NULL,
  plate_number VARCHAR(30) DEFAULT NULL,
  latitude DECIMAL(10,7) DEFAULT NULL,
  longitude DECIMAL(10,7) DEFAULT NULL,
  ktp_file VARCHAR(255) DEFAULT NULL,
  selfie_file VARCHAR(255) DEFAULT NULL,
  license_file VARCHAR(255) DEFAULT NULL,
  vehicle_doc_file VARCHAR(255) DEFAULT NULL,
  business_doc_file VARCHAR(255) DEFAULT NULL,
  terms_version VARCHAR(30) NOT NULL,
  privacy_version VARCHAR(30) NOT NULL,
  consent_at DATETIME NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'Menunggu Peninjauan',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  INDEX idx_category (category_code),
  INDEX idx_status (status),
  INDEX idx_created (created_at),
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  application_id BIGINT UNSIGNED DEFAULT NULL,
  recipient VARCHAR(190) NOT NULL,
  mail_type VARCHAR(40) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_application (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;