events_scripts.sql

-- =========================================================
-- FILE: events.sql
-- PROJECT: TechSphere Solutions Database System
-- AUTHOR: Benard Onyango Omoga
-- DATABASE: MySQL
-- DESCRIPTION:
-- Demonstrates Events (Scheduled Automation) in MySQL
-- using the TechSphere database schema.
-- =========================================================


-- =========================================================
-- USE DATABASE
-- =========================================================
USE techsphere_solutions;


-- =========================================================
-- SECTION 1: INTRODUCTION TO EVENTS
-- PURPOSE:
-- Events are scheduled SQL tasks that execute automatically
-- based on time intervals (not table actions).
-- =========================================================

-- TYPES OF EVENTS:
-- 1. Scheduled Reporting Events
-- 2. Data Cleanup Events
-- 3. Data Archiving Events


-- =========================================================
-- SECTION 2: ENABLE EVENT SCHEDULER
-- PURPOSE:
-- Ensures MySQL event engine is active
-- =========================================================

SET GLOBAL event_scheduler = ON;


-- =========================================================
-- SECTION 3: CREATE TABLES FOR EVENTS
-- PURPOSE:
-- Store results generated by scheduled jobs
-- =========================================================

-- Salary summary log table
CREATE TABLE IF NOT EXISTS salary_summary_log
(
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    total_employees INT,
    total_payroll DECIMAL(12,2),
    average_salary DECIMAL(12,2),
    log_timestamp DATETIME
);


-- =========================================================
-- SECTION 4: DAILY SALARY REPORT EVENT
-- PURPOSE:
-- Generate daily payroll snapshot automatically
-- =========================================================

DELIMITER $$

CREATE EVENT daily_salary_report

ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP

DO
BEGIN

    INSERT INTO salary_summary_log
    (
        total_employees,
        total_payroll,
        average_salary,
        log_timestamp
    )

    SELECT
        COUNT(e.employee_id),
        SUM(jr.base_salary),
        AVG(jr.base_salary),
        NOW()

    FROM employees e
    JOIN employee_positions ep
        ON e.employee_id = ep.employee_id
    JOIN job_roles jr
        ON ep.role_id = jr.role_id;

END $$

DELIMITER ;


-- WHAT IT DOES:
-- 1. Runs automatically every day
-- 2. Calculates payroll statistics
-- 3. Stores snapshot in salary_summary_log
-- 4. Used for HR and finance reporting


-- =========================================================
-- SECTION 5: WEEKLY CLEANUP EVENT
-- PURPOSE:
-- Remove old audit logs automatically
-- =========================================================

DELIMITER $$

CREATE EVENT cleanup_old_audit_logs

ON SCHEDULE EVERY 1 WEEK

DO
BEGIN

    DELETE FROM employee_audit_log
    WHERE action_timestamp < NOW() - INTERVAL 90 DAY;

END $$

DELIMITER ;


-- WHAT IT DOES:
-- 1. Runs weekly
-- 2. Deletes audit logs older than 90 days
-- 3. Keeps database optimized and clean


-- =========================================================
-- SECTION 6: MONTHLY ARCHIVING EVENT
-- PURPOSE:
-- Archive long-serving employees
-- =========================================================

DELIMITER $$

CREATE EVENT archive_long_term_employees

ON SCHEDULE EVERY 1 MONTH

DO
BEGIN

    INSERT INTO deleted_employees_archive
    (
        employee_id,
        first_name,
        last_name,
        gender,
        date_of_birth,
        hire_date,
        department_id,
        deleted_at
    )

    SELECT
        employee_id,
        first_name,
        last_name,
        gender,
        date_of_birth,
        hire_date,
        department_id,
        NOW()

    FROM employees
    WHERE hire_date < DATE_SUB(NOW(), INTERVAL 5 YEAR);

END $$

DELIMITER ;


-- WHAT IT DOES:
-- 1. Runs monthly
-- 2. Moves old employees into archive table
-- 3. Preserves historical employee data


-- =========================================================
-- SECTION 7: VERIFY EVENTS
-- PURPOSE:
-- Check all active scheduled events
-- =========================================================

SHOW EVENTS;


-- =========================================================
-- SECTION 8: DROP EVENT (MAINTENANCE OPTION)
-- PURPOSE:
-- Remove existing events if needed
-- =========================================================

DROP EVENT IF EXISTS daily_salary_report;


-- =========================================================
-- KEY CONCEPT SUMMARY
-- =========================================================

-- EVENT:
-- A scheduled SQL task that runs automatically

-- ON SCHEDULE:
-- Defines timing (daily, weekly, monthly, etc.)

-- STARTS:
-- Defines when execution begins

-- DO BEGIN ... END:
-- Defines event logic block

-- EVENT SCHEDULER:
-- MySQL engine that runs events automatically


-- =========================================================
-- BUSINESS USE CASES
-- =========================================================

-- EVENTS ARE USED FOR:
-- - Automated reporting
-- - Payroll summaries
-- - Data cleanup
-- - Data archiving
-- - System maintenance
-- - ETL automation


-- =========================================================
-- IMPORTANT NOTES
-- =========================================================

-- Events run without user interaction
-- They reduce manual database workload
-- They improve system automation and efficiency
-- They work best with triggers for full automation systems


-- =========================================================
-- END OF FILE
-- =========================================================