

-- ============================================================
-- 2. Daily summary aggregation table
-- ============================================================
CREATE TABLE IF NOT EXISTS daily_summary (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    day              DATE NOT NULL UNIQUE,
    total_pageviews  INT UNSIGNED NOT NULL DEFAULT 0,
    unique_sessions  INT UNSIGNED NOT NULL DEFAULT 0,
    avg_load_time_ms INT UNSIGNED,
    total_errors     INT UNSIGNED NOT NULL DEFAULT 0,
    top_page         VARCHAR(2048),
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_ds_day (day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Nightly aggregation procedure
-- ============================================================
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS aggregate_daily(IN target_date DATE)
BEGIN
    DECLARE v_top_page VARCHAR(2048);

    -- Find top page for the day
    SELECT url INTO v_top_page
    FROM pageviews
    WHERE DATE(server_timestamp) = target_date AND type = 'pageview'
    GROUP BY url
    ORDER BY COUNT(*) DESC
    LIMIT 1;
END //

DELIMITER ;

-- ============================================================
-- 4. Purge procedure — drop partitions older than N months
-- ============================================================
-- Usage: CALL purge_old_partitions(6);  -- drop partitions older than 6 months
-- Note: This is a template. In production, build partition name dynamically.
-- For non-partitioned tables, use DELETE with LIMIT:

-- DELETE FROM pageviews
-- WHERE server_timestamp < DATE_SUB(NOW(), INTERVAL 6 MONTH)
-- LIMIT 10000;
-- (Run in a loop until 0 rows affected)

-- Example: Drop a specific old partition
-- ALTER TABLE pageviews_partitioned DROP PARTITION p2025_01;

-- ============================================================
-- 5. Add a new monthly partition
-- ============================================================
-- Before the month starts, reorganize the catch-all partition:
-- ALTER TABLE pageviews_partitioned REORGANIZE PARTITION p_future INTO (
--     PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
--     PARTITION p_future VALUES LESS THAN MAXVALUE
-- );

-- ============================================================
-- 6. MySQL EVENT for nightly aggregation
-- ============================================================
-- SET GLOBAL event_scheduler = ON;
--
-- CREATE EVENT IF NOT EXISTS evt_nightly_aggregate
-- ON SCHEDULE EVERY 1 DAY
-- STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
-- DO CALL aggregate_daily(DATE_SUB(CURDATE(), INTERVAL 1 DAY));
