CREATE TABLE IF NOT EXISTS popular_site_clicks (
    id SERIAL PRIMARY KEY,
    site_id INTEGER NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    category VARCHAR(100) NOT NULL DEFAULT 'general',
    clicked_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_popular_site_clicks_time ON popular_site_clicks (clicked_at);
CREATE INDEX idx_popular_site_clicks_category ON popular_site_clicks (category);
CREATE INDEX idx_popular_site_clicks_category_time ON popular_site_clicks (category, clicked_at);
CREATE INDEX idx_popular_site_clicks_site_time ON popular_site_clicks (site_id, clicked_at);