-- SQLite example: accessing NEW and OLD
-- NEW.column_name: references the new data
-- OLD.column_name: references the old data

-- MySQL event scheduler example
-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;

-- Create a scheduled event: clean up expired data daily at midnight
CREATE EVENT cleanup_expired_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
    DELETE FROM orders 
    WHERE status = 'expired' 
    AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Create a one-time event
CREATE EVENT one_time_report
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
    INSERT INTO annual_report (year, total_sales)
    SELECT YEAR(NOW()), SUM(amount) FROM orders WHERE YEAR(created_at) = YEAR(NOW());

-- View all events
SHOW EVENTS;

-- Disable an event
ALTER EVENT cleanup_expired_orders DISABLE;

-- Delete an event
DROP EVENT IF EXISTS cleanup_expired_orders;

-- Basic trigger creation syntax
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- Trigger logic
END;

-- MySQL trigger syntax
DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic
    -- Use NEW and OLD to reference data
END //
DELIMITER ;

-- PostgreSQL requires creating a trigger function first
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic
    RETURN NEW; -- or RETURN OLD
END;
$$ LANGUAGE plpgsql;

-- Then create the trigger
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function_name();

-- SQLite version
-- First create the audit log table
CREATE TABLE IF NOT EXISTS salary_audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    changed_by TEXT,
    changed_at TEXT DEFAULT (datetime('now', 'localtime')),
    action TEXT
);

-- Create trigger: record salary updates
CREATE TRIGGER audit_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
    INSERT INTO salary_audit_log (
        employee_id, 
        old_salary, 
        new_salary, 
        changed_by, 
        action
    )
    VALUES (
        NEW.id,
        OLD.salary,
        NEW.salary,
        'system',  -- In practice, you can use the current user
        'UPDATE'
    );
END;

-- Test the trigger
UPDATE employees SET salary = 12000 WHERE id = 1;

-- View the audit log
SELECT * FROM salary_audit_log;

-- Create orders table
CREATE TABLE IF NOT EXISTS orders_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER DEFAULT 1,
    order_date TEXT,
    status TEXT DEFAULT 'pending',
    created_at TEXT
);

-- Create BEFORE INSERT trigger
CREATE TRIGGER set_order_defaults
BEFORE INSERT ON orders_new
FOR EACH ROW
BEGIN
    -- Automatically set order date to current date
    SET NEW.order_date = COALESCE(NEW.order_date, date('now', 'localtime'));
    -- Automatically set creation time
    SET NEW.created_at = COALESCE(NEW.created_at, datetime('now', 'localtime'));
    -- If quantity is null or 0, default to 1
    SET NEW.quantity = CASE WHEN NEW.quantity IS NULL OR NEW.quantity <= 0 THEN 1 ELSE NEW.quantity END;
END;

-- Test: insert without specifying order_date and status
INSERT INTO orders_new (customer_name, product_id, quantity) VALUES ('John Doe', 1, 3);

-- Verify the auto-filled result
SELECT * FROM orders_new;

id  customer_name  product_id  quantity  order_date   status   created_at
1   John Doe       1           3         2026-06-28   pending  2026-06-28 10:30:00

-- SQLite version
-- Create order summary table
CREATE TABLE IF NOT EXISTS order_summary (
    customer_id INTEGER PRIMARY KEY,
    total_orders INTEGER DEFAULT 0,
    total_amount REAL DEFAULT 0,
    last_order_date TEXT
);

-- Create trigger: auto-update summary on order insertion
CREATE TRIGGER update_order_summary_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO order_summary (customer_id, total_orders, total_amount, last_order_date)
    SELECT 
        NEW.customer_id,
        COALESCE(os.total_orders, 0) + 1,
        COALESCE(os.total_amount, 0) + NEW.amount,
        NEW.order_date
    FROM (SELECT 1) AS dummy
    LEFT JOIN order_summary os ON os.customer_id = NEW.customer_id;
END;

-- Create trigger: auto-update summary on order deletion
CREATE TRIGGER update_order_summary_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE order_summary
    SET 
        total_orders = total_orders - 1,
        total_amount = total_amount - OLD.amount
    WHERE customer_id = OLD.customer_id;
    
    -- If order count reaches 0, delete the summary record
    DELETE FROM order_summary 
    WHERE customer_id = OLD.customer_id AND total_orders <= 0;
END;

-- SQLite version: auto-calculate order total
CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Auto-set creation time
    SELECT NEW.created_at IS NULL THEN
        SET NEW.created_at = datetime('now', 'localtime');
    END IF;
    
    -- Validate that amount must be positive
    IF NEW.amount < 0 THEN
        SELECT RAISE(ABORT, 'Order amount cannot be negative');
    END IF;
END;