CREATE TABLE admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) DEFAULT NULL,
  email VARCHAR(190) 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 qr_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(30) NOT NULL UNIQUE,
  client_name VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  type ENUM('url','vcard') NOT NULL DEFAULT 'url',
  target_url TEXT DEFAULT NULL,
  contact_fullname VARCHAR(190) DEFAULT NULL,
  contact_company VARCHAR(190) DEFAULT NULL,
  contact_job_title VARCHAR(190) DEFAULT NULL,
  contact_phone VARCHAR(60) DEFAULT NULL,
  contact_mobile VARCHAR(60) DEFAULT NULL,
  contact_email VARCHAR(190) DEFAULT NULL,
  contact_address TEXT DEFAULT NULL,
  contact_website TEXT DEFAULT NULL,
  contact_notes TEXT DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_qr_codes_client_name (client_name),
  INDEX idx_qr_codes_type (type),
  INDEX idx_qr_codes_active (is_active),
  CONSTRAINT fk_qr_created_by FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE qr_scans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  qr_code_id INT UNSIGNED NOT NULL,
  scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_address VARCHAR(45) DEFAULT NULL,
  ip_hash CHAR(64) DEFAULT NULL,
  user_agent TEXT DEFAULT NULL,
  js_user_agent TEXT DEFAULT NULL,
  referrer TEXT DEFAULT NULL,
  accept_language VARCHAR(255) DEFAULT NULL,
  device_type VARCHAR(50) DEFAULT NULL,
  os VARCHAR(80) DEFAULT NULL,
  browser VARCHAR(80) DEFAULT NULL,
  screen_width INT UNSIGNED DEFAULT NULL,
  screen_height INT UNSIGNED DEFAULT NULL,
  js_timezone VARCHAR(100) DEFAULT NULL,
  js_language VARCHAR(50) DEFAULT NULL,
  js_platform VARCHAR(120) DEFAULT NULL,
  country_code VARCHAR(10) DEFAULT NULL,
  country_name VARCHAR(120) DEFAULT NULL,
  city VARCHAR(120) DEFAULT NULL,
  INDEX idx_qr_scans_qr_date (qr_code_id, scanned_at),
  INDEX idx_qr_scans_ip_hash (ip_hash),
  CONSTRAINT fk_qr_scans_qr FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
