-- Check current status
SHOW VARIABLES LIKE 'event_scheduler';

-- Enable for the current session (not persistent)
SET GLOBAL event_scheduler = ON;

-- Disable
SET GLOBAL event_scheduler = OFF;

# Make it persistent — add to my.cnf / my.ini
[mysqld]
event_scheduler = ON

CREATE [DEFINER = user]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'description']
DO event_body;

-- Fire once in 1 hour from now
CREATE EVENT evt_send_reminder
ON SCHEDULE AT NOW() + INTERVAL 1 HOUR
DO
  INSERT INTO notification_queue (type, message, created_at)
  VALUES ('reminder', 'Check your pending orders!', NOW());

-- Fire at a specific datetime
CREATE EVENT evt_new_year_promo
ON SCHEDULE AT '2025-01-01 00:00:00'
DO
  UPDATE products SET discount_pct = 20 WHERE category = 'seasonal';

-- Keep the event after it fires (do not auto-drop)
CREATE EVENT evt_one_time_archive
ON SCHEDULE AT NOW() + INTERVAL 30 MINUTE
ON COMPLETION PRESERVE
DO
  INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < '2023-01-01';

-- Run every hour, forever
CREATE EVENT evt_cleanup_sessions
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM user_sessions WHERE expires_at < NOW();

-- Run every day at midnight, starting from a specific date
CREATE EVENT evt_daily_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
COMMENT 'Generates daily sales summary'
DO
BEGIN
  INSERT INTO daily_sales_summary (summary_date, total_orders, revenue)
  SELECT
    CURDATE() - INTERVAL 1 DAY,
    COUNT(*),
    SUM(total_amount)
  FROM orders
  WHERE DATE(order_date) = CURDATE() - INTERVAL 1 DAY
    AND status = 'completed';
END;

-- Run every week for 3 months only
CREATE EVENT evt_weekly_report
ON SCHEDULE EVERY 1 WEEK
STARTS  CURDATE()
ENDS    CURDATE() + INTERVAL 3 MONTH
DO
  CALL generate_weekly_report();

-- Run every 15 minutes
CREATE EVENT evt_cache_refresh
ON SCHEDULE EVERY 15 MINUTE
DO
  CALL refresh_product_cache();

-- Default: event is dropped after firing
CREATE EVENT evt_temp_fix
ON SCHEDULE AT NOW() + INTERVAL 10 MINUTE
DO UPDATE config SET value = 'fixed' WHERE key = 'broken_setting';

-- Preserved: event stays in the system after firing
CREATE EVENT evt_preserved_fix
ON SCHEDULE AT NOW() + INTERVAL 10 MINUTE
ON COMPLETION PRESERVE
DO UPDATE config SET value = 'fixed' WHERE key = 'broken_setting';

-- List all events in the current database
SHOW EVENTSG

-- Events in a specific database
SHOW EVENTS FROM mydbG

-- Detailed query via information_schema
SELECT
  EVENT_NAME,
  EVENT_TYPE,
  EXECUTE_AT,
  INTERVAL_VALUE,
  INTERVAL_FIELD,
  STARTS,
  ENDS,
  STATUS,
  ON_COMPLETION,
  LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE()
ORDER BY EVENT_NAME;

-- Change the schedule
ALTER EVENT evt_cleanup_sessions
ON SCHEDULE EVERY 30 MINUTE;

-- Disable without dropping (useful during maintenance)
ALTER EVENT evt_cleanup_sessions DISABLE;

-- Re-enable
ALTER EVENT evt_cleanup_sessions ENABLE;

-- Rename an event
ALTER EVENT evt_cleanup_sessions
RENAME TO evt_purge_expired_sessions;

-- Change the body
ALTER EVENT evt_cache_refresh
DO CALL refresh_all_caches();

-- Drop an event
DROP EVENT IF EXISTS evt_cleanup_sessions;

-- Events are also dropped when the database is dropped

-- Delete soft-deleted records older than 90 days
CREATE EVENT evt_hard_delete_old_records
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 03:00:00'   -- Run at 3 AM
COMMENT 'Hard-delete soft-deleted rows older than 90 days'
DO
BEGIN
  DELETE FROM customers
  WHERE deleted_at IS NOT NULL
    AND deleted_at < NOW() - INTERVAL 90 DAY
  LIMIT 10000;  -- Limit rows per run to avoid locking

  DELETE FROM orders
  WHERE deleted_at IS NOT NULL
    AND deleted_at < NOW() - INTERVAL 90 DAY
  LIMIT 10000;
END;

DELIMITER //

-- Helper procedure for the archive job
CREATE PROCEDURE archive_old_orders()
BEGIN
  DECLARE v_cutoff DATE;
  SET v_cutoff = DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

  START TRANSACTION;

  -- Copy to archive table
  INSERT INTO orders_archive
  SELECT * FROM orders
  WHERE order_date < v_cutoff AND status = 'completed';

  -- Remove from main table
  DELETE FROM orders
  WHERE order_date < v_cutoff AND status = 'completed';

  -- Log the run
  INSERT INTO maintenance_log (task, rows_affected, run_at)
  VALUES ('archive_orders', ROW_COUNT(), NOW());

  COMMIT;
END //

DELIMITER ;

-- Create the scheduled event
CREATE EVENT evt_archive_orders
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 02:00:00'
COMMENT 'Archive completed orders older than 2 years'
DO CALL archive_old_orders();

-- Pre-aggregate expensive stats into a summary table each night
DELIMITER //

CREATE PROCEDURE nightly_stats_rollup()
BEGIN
  DECLARE v_date DATE DEFAULT CURDATE() - INTERVAL 1 DAY;

  -- Delete existing row for this date (idempotent)
  DELETE FROM daily_stats WHERE stat_date = v_date;

  -- Insert fresh aggregation
  INSERT INTO daily_stats (
    stat_date,
    new_users,
    active_users,
    new_orders,
    completed_orders,
    gross_revenue,
    refund_amount
  )
  SELECT
    v_date,
    (SELECT COUNT(*) FROM users WHERE DATE(created_at) = v_date),
    (SELECT COUNT(DISTINCT user_id) FROM sessions WHERE DATE(started_at) = v_date),
    COUNT(CASE WHEN DATE(order_date) = v_date THEN 1 END),
    COUNT(CASE WHEN DATE(completed_at) = v_date THEN 1 END),
    SUM(CASE WHEN DATE(order_date) = v_date THEN total_amount ELSE 0 END),
    SUM(CASE WHEN DATE(refunded_at) = v_date THEN refund_amount ELSE 0 END)
  FROM orders;

  INSERT INTO maintenance_log (task, run_at, note)
  VALUES ('nightly_stats_rollup', NOW(), CONCAT('Stats for ', v_date));
END //

DELIMITER ;

-- Schedule it to run every night at 1:00 AM
CREATE EVENT evt_nightly_stats
ON SCHEDULE EVERY 1 DAY
STARTS (CURDATE() + INTERVAL 1 DAY) + INTERVAL 1 HOUR
ON COMPLETION PRESERVE
COMMENT 'Nightly statistics rollup into daily_stats table'
DO CALL nightly_stats_rollup();

-- Every 15 minutes, expire promo codes whose end_date has passed
CREATE EVENT evt_expire_promo_codes
ON SCHEDULE EVERY 15 MINUTE
COMMENT 'Deactivate expired promo codes'
DO
  UPDATE promo_codes
  SET status = 'expired'
  WHERE status = 'active'
    AND end_date < NOW();

-- Check when each event last ran and its current status
SELECT
  EVENT_NAME,
  STATUS,
  LAST_EXECUTED,
  STARTS,
  ENDS,
  EVENT_TYPE,
  INTERVAL_VALUE,
  INTERVAL_FIELD
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE()
ORDER BY LAST_EXECUTED DESC;

-- Check if the event scheduler is running
SHOW PROCESSLIST;
-- Look for a row with User = 'event_scheduler' and Command = 'Daemon'

-- If an event fails, the error is logged in the MySQL error log
-- Check the error log path
SHOW VARIABLES LIKE 'log_error';