-- Geo Traffic 1.0.0 schema version 1.
-- Analytics are aggregate-only; plaintext IP addresses and raw user agents are not stored.

CREATE TABLE IF NOT EXISTS mm_geo_meta (
    meta_key VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    meta_value MEDIUMTEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_daily (
    stat_date DATE NOT NULL PRIMARY KEY,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_visitor_day (
    stat_date DATE NOT NULL,
    visitor_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, visitor_hash),
    INDEX idx_mm_geo_visitor_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_country_daily (
    stat_date DATE NOT NULL,
    country_code CHAR(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, country_code),
    INDEX idx_mm_geo_country_hits (stat_date, hit_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_country_visitor_day (
    stat_date DATE NOT NULL,
    country_code CHAR(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    visitor_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, country_code, visitor_hash),
    INDEX idx_mm_geo_country_visitor_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_page_daily (
    stat_date DATE NOT NULL,
    page_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    page_path VARCHAR(1000) NOT NULL,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, page_hash),
    INDEX idx_mm_geo_page_hits (stat_date, hit_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_referrer_daily (
    stat_date DATE NOT NULL,
    referrer_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    referrer_host VARCHAR(253) NOT NULL,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, referrer_hash),
    INDEX idx_mm_geo_referrer_hits (stat_date, hit_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_technology_daily (
    stat_date DATE NOT NULL,
    technology_type ENUM('browser','os') NOT NULL,
    technology_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    technology_name VARCHAR(100) NOT NULL,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (stat_date, technology_type, technology_hash),
    INDEX idx_mm_geo_technology_hits (stat_date, technology_type, hit_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_geo_legacy_total (
    dimension_type ENUM('browser','os') NOT NULL,
    dimension_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    dimension_name VARCHAR(100) NOT NULL,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    migrated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dimension_type, dimension_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;