CREATE TABLE IF NOT EXISTS `{{prefix}}users` (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','superadmin') NOT NULL DEFAULT 'user',
  reset_token_hash CHAR(64) NULL,
  reset_expires_at DATETIME NULL,
  reset_requested_at DATETIME NULL,
  email_verified_at DATETIME NULL,
  verification_token_hash CHAR(64) NULL,
  verification_expires_at DATETIME NULL,
  failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
  login_locked_until DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}qr_groups` (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES `{{prefix}}users`(id) ON DELETE CASCADE,
  UNIQUE KEY user_group_name (user_id, name),
  INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}qr_group_shares` (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  access_role ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id) REFERENCES `{{prefix}}qr_groups`(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES `{{prefix}}users`(id) ON DELETE CASCADE,
  UNIQUE KEY group_user (group_id, user_id),
  INDEX(user_id),
  INDEX(group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}qr_codes` (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  group_id INT UNSIGNED NULL,
  code VARCHAR(32) NOT NULL UNIQUE,
  title VARCHAR(190) NOT NULL,
  target_url TEXT NOT NULL,
  foreground_color CHAR(7) NOT NULL DEFAULT '#000000',
  background_color CHAR(7) NOT NULL DEFAULT '#FFFFFF',
  transparent_background TINYINT(1) NOT NULL DEFAULT 0,
  logo_path VARCHAR(255) NULL,
  utm_source VARCHAR(120) NULL,
  utm_medium VARCHAR(120) NULL,
  utm_campaign VARCHAR(190) NULL,
  utm_term VARCHAR(190) NULL,
  utm_content VARCHAR(190) NULL,
  scan_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_scan_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES `{{prefix}}users`(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES `{{prefix}}qr_groups`(id) ON DELETE SET NULL,
  INDEX(code),
  INDEX(user_id),
  INDEX(group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}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_hash CHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  referer VARCHAR(500) NULL,
  country_code CHAR(2) NULL,
  country_name VARCHAR(120) NULL,
  region_name VARCHAR(120) NULL,
  city VARCHAR(120) NULL,
  FOREIGN KEY (qr_code_id) REFERENCES `{{prefix}}qr_codes`(id) ON DELETE CASCADE,
  INDEX(qr_code_id),
  INDEX(scanned_at),
  INDEX(country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}rate_limits` (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  endpoint VARCHAR(80) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 1,
  window_start DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  blocked_until DATETIME NULL,
  last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY endpoint_key (endpoint, key_hash),
  INDEX(blocked_until),
  INDEX(last_attempt_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
