-- Deduplicated browser fingerprints. Repeat visits with the same hash update
-- last_seen/hit_count, which feeds the "repeat visitor" session signal.
CREATE TABLE IF NOT EXISTS `fingerprints` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `hash`       CHAR(64)        NOT NULL,
    `components` JSON            NULL,
    `hit_count`  INT UNSIGNED    NOT NULL DEFAULT 1,
    `first_seen` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_fp_hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;