-- ============================================================
-- Data Retention: Partitioning, Summary Tables, Purge Scripts
-- ============================================================

-- ============================================================
-- 1. Range partitioning for pageviews (monthly)
-- ============================================================
-- Note: To use partitioning, recreate table with PARTITION BY RANGE
-- The partition key must be part of any unique/primary index

CREATE TABLE IF NOT EXISTS pageviews_partitioned (
    id                INT AUTO_INCREMENT,
    url               VARCHAR(2048) NOT NULL,
    type              ENUM('pageview','event','error','performance') NOT NULL DEFAULT 'pageview',
    user_agent        VARCHAR(512),
    viewport_width    SMALLINT UNSIGNED,
    viewport_height   SMALLINT UNSIGNED,
    referrer          VARCHAR(2048),
    client_timestamp  DATETIME,
    server_timestamp  DATETIME NOT NULL,
    client_ip         VARCHAR(45),
    session_id        VARCHAR(64),
    payload           JSON,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id, server_timestamp),
    INDEX idx_pv_url (url(255)),
    INDEX idx_pv_type (type),
    INDEX idx_pv_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(server_timestamp)) (
    PARTITION p2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')),
    PARTITION p2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')),
    PARTITION p2025_05 VALUES LESS THAN (TO_DAYS('2025-06-01')),
    PARTITION p2025_06 VALUES LESS THAN (TO_DAYS('2025-07-01')),
    PARTITION p2025_07 VALUES LESS THAN (TO_DAYS('2025-08-01')),
    PARTITION p2025_08 VALUES LESS THAN (TO_DAYS('2025-09-01')),
    PARTITION p2025_09 VALUES LESS THAN (TO_DAYS('2025-10-01')),
    PARTITION p2025_10 VALUES LESS THAN (TO_DAYS('2025-11-01')),
    PARTITION p2025_11 VALUES LESS THAN (TO_DAYS('2025-12-01')),
    PARTITION p2025_12 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ============================================================
-- 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;

    -- Upsert daily summary
    INSERT INTO daily_summary (day, total_pageviews, unique_sessions, avg_load_time_ms, total_errors, top_page)
    SELECT
        target_date,
        (SELECT COUNT(*) FROM pageviews WHERE DATE(server_timestamp) = target_date AND type = 'pageview'),
        (SELECT COUNT(DISTINCT session_id) FROM pageviews WHERE DATE(server_timestamp) = target_date),
        (SELECT ROUND(AVG(load_time)) FROM performance WHERE DATE(server_timestamp) = target_date),
        (SELECT COUNT(*) FROM errors WHERE DATE(server_timestamp) = target_date),
        v_top_page
    ON DUPLICATE KEY UPDATE
        total_pageviews  = VALUES(total_pageviews),
        unique_sessions  = VALUES(unique_sessions),
        avg_load_time_ms = VALUES(avg_load_time_ms),
        total_errors     = VALUES(total_errors),
        top_page         = VALUES(top_page);
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));
