CREATE DATABASE IF NOT EXISTS `digichat_control`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `digichat_control`;

CREATE TABLE IF NOT EXISTS `visitors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `visitor_id` VARCHAR(255) NOT NULL,
  `fingerprint` VARCHAR(255) NOT NULL,
  `site_id` VARCHAR(100) DEFAULT NULL,
  `chat_server` VARCHAR(255) DEFAULT NULL,
  `first_seen_at` DATETIME NOT NULL,
  `last_seen_at` DATETIME NOT NULL,
  `last_ip` VARCHAR(64) DEFAULT NULL,
  `last_user_agent` TEXT DEFAULT NULL,
  `source` VARCHAR(255) DEFAULT NULL,
  `login_count` INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_visitors_visitor_id` (`visitor_id`),
  UNIQUE KEY `uq_visitors_fingerprint` (`fingerprint`),
  KEY `idx_visitors_last_seen` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `blocks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `block_type` ENUM('id','fingerprint') NOT NULL,
  `block_value` VARCHAR(255) NOT NULL,
  `reason` VARCHAR(500) DEFAULT NULL,
  `blocked_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_blocks_type_value` (`block_type`, `block_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;