-- Page hit logging schema for MySQL 8+
-- Tracks: hit date/time, username (if known), IP, user agent, and client type.

CREATE TABLE IF NOT EXISTS page_hits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    hit_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    action ENUM('enter_site','page_view','login_success','login_failed','other') NOT NULL DEFAULT 'page_view',
    username VARCHAR(100) NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(1024) NOT NULL,
    client_type ENUM('desktop','mobile','tablet','bot','api','unknown') NOT NULL DEFAULT 'unknown',
    page_path VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_hit_at (hit_at),
    INDEX idx_username (username),
    INDEX idx_ip_address (ip_address),
    INDEX idx_client_type (client_type),
    INDEX idx_page_path (page_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Example insert template (use prepared statements in PHP):
-- INSERT INTO page_hits (action, username, ip_address, user_agent, client_type, page_path)
-- VALUES (?, ?, ?, ?, ?, ?);