-- Visitor Tracking System
-- For LandSales Pro and LandScanner

-- Visitors table - stores unique visitors
CREATE TABLE IF NOT EXISTS visitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_hash VARCHAR(64) NOT NULL UNIQUE,
    ip_address VARCHAR(45) NOT NULL,
    first_visit DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_visit DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_visits INT DEFAULT 1,
    total_pageviews INT DEFAULT 0,

    -- Device info
    user_agent TEXT,
    device_type ENUM('desktop', 'mobile', 'tablet', 'bot', 'unknown') DEFAULT 'unknown',
    browser VARCHAR(100),
    browser_version VARCHAR(50),
    os VARCHAR(100),
    os_version VARCHAR(50),

    -- Bot detection
    is_bot TINYINT(1) DEFAULT 0,
    bot_name VARCHAR(100),

    -- Geolocation
    country_code VARCHAR(2),
    country_name VARCHAR(100),
    region VARCHAR(100),
    city VARCHAR(100),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    timezone VARCHAR(50),
    isp VARCHAR(200),

    -- Source tracking
    site VARCHAR(50) NOT NULL DEFAULT 'landsales',

    INDEX idx_ip (ip_address),
    INDEX idx_first_visit (first_visit),
    INDEX idx_last_visit (last_visit),
    INDEX idx_is_bot (is_bot),
    INDEX idx_country (country_code),
    INDEX idx_site (site)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sessions table - tracks individual browsing sessions
CREATE TABLE IF NOT EXISTS visitor_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    session_id VARCHAR(64) NOT NULL,
    site VARCHAR(50) NOT NULL DEFAULT 'landsales',
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME,
    duration_seconds INT DEFAULT 0,
    pageviews INT DEFAULT 0,

    -- Entry info
    entry_page VARCHAR(500),
    exit_page VARCHAR(500),
    referrer VARCHAR(500),
    referrer_domain VARCHAR(200),

    -- UTM tracking
    utm_source VARCHAR(200),
    utm_medium VARCHAR(200),
    utm_campaign VARCHAR(200),
    utm_term VARCHAR(200),
    utm_content VARCHAR(200),

    -- Screen info
    screen_width INT,
    screen_height INT,
    viewport_width INT,
    viewport_height INT,

    FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE,
    INDEX idx_visitor (visitor_id),
    INDEX idx_session (session_id),
    INDEX idx_started (started_at),
    INDEX idx_site (site)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Page views table - tracks every page view
CREATE TABLE IF NOT EXISTS visitor_pageviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    session_id INT NOT NULL,
    site VARCHAR(50) NOT NULL DEFAULT 'landsales',

    page_url VARCHAR(500) NOT NULL,
    page_path VARCHAR(300),
    page_title VARCHAR(300),
    query_string VARCHAR(500),

    viewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    time_on_page INT DEFAULT 0,

    -- Scroll tracking
    scroll_depth INT DEFAULT 0,

    FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES visitor_sessions(id) ON DELETE CASCADE,
    INDEX idx_visitor (visitor_id),
    INDEX idx_session (session_id),
    INDEX idx_viewed (viewed_at),
    INDEX idx_page (page_path),
    INDEX idx_site (site)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Events table - tracks specific actions (clicks, forms, etc.)
CREATE TABLE IF NOT EXISTS visitor_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id INT NOT NULL,
    session_id INT NOT NULL,
    pageview_id INT,
    site VARCHAR(50) NOT NULL DEFAULT 'landsales',

    event_type VARCHAR(50) NOT NULL,
    event_category VARCHAR(100),
    event_action VARCHAR(100),
    event_label VARCHAR(200),
    event_value VARCHAR(500),

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES visitor_sessions(id) ON DELETE CASCADE,
    INDEX idx_visitor (visitor_id),
    INDEX idx_session (session_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created (created_at),
    INDEX idx_site (site)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Daily stats summary (for quick reporting)
CREATE TABLE IF NOT EXISTS visitor_daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL,
    site VARCHAR(50) NOT NULL DEFAULT 'landsales',

    total_visitors INT DEFAULT 0,
    new_visitors INT DEFAULT 0,
    returning_visitors INT DEFAULT 0,
    total_pageviews INT DEFAULT 0,
    total_sessions INT DEFAULT 0,
    bot_visits INT DEFAULT 0,

    avg_session_duration INT DEFAULT 0,
    avg_pages_per_session DECIMAL(5,2) DEFAULT 0,
    bounce_rate DECIMAL(5,2) DEFAULT 0,

    -- Top data (JSON)
    top_pages JSON,
    top_countries JSON,
    top_referrers JSON,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_date_site (stat_date, site),
    INDEX idx_date (stat_date),
    INDEX idx_site (site)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
