-- SQLiteの例：NEWとOLDにアクセス
-- NEW.column_name：新しいデータを参照
-- OLD.column_name：古いデータを参照

-- MySQLイベントスケジューラの例
-- イベントスケジューラを有効化
SET GLOBAL event_scheduler = ON;

-- スケジュールイベントを作成：毎日深夜に期限切れデータをクリーンアップ
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 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());

-- すべてのイベントを表示
SHOW EVENTS;

-- イベントを無効化
ALTER EVENT cleanup_expired_orders DISABLE;

-- イベントを削除
DROP EVENT IF EXISTS cleanup_expired_orders;

-- 基本的なトリガー作成構文
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- トリガーのロジック
END;

-- MySQLトリガー構文
DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- トリガーのロジック
    -- NEWとOLDを使ってデータを参照
END //
DELIMITER ;

-- PostgreSQLではまずトリガー関数を作成する必要がある
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- トリガーのロジック
    RETURN NEW; -- または RETURN OLD
END;
$$ LANGUAGE plpgsql;

-- その後トリガーを作成
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function_name();

-- SQLite版
-- まず監査ログテーブルを作成
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 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',  -- 実際には現在のユーザーを使用可能
        'UPDATE'
    );
END;

-- トリガーをテスト
UPDATE employees SET salary = 12000 WHERE id = 1;

-- 監査ログを表示
SELECT * FROM salary_audit_log;

-- ordersテーブルを作成
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
);

-- BEFORE INSERTトリガーを作成
CREATE TRIGGER set_order_defaults
BEFORE INSERT ON orders_new
FOR EACH ROW
BEGIN
    -- 注文日を自動的に現在の日に設定
    SET NEW.order_date = COALESCE(NEW.order_date, date('now', 'localtime'));
    -- 作成日時を自動的に設定
    SET NEW.created_at = COALESCE(NEW.created_at, datetime('now', 'localtime'));
    -- 数量がnullまたは0の場合、デフォルトで1に設定
    SET NEW.quantity = CASE WHEN NEW.quantity IS NULL OR NEW.quantity <= 0 THEN 1 ELSE NEW.quantity END;
END;

-- テスト：order_dateとstatusを指定せずに挿入
INSERT INTO orders_new (customer_name, product_id, quantity) VALUES ('John Doe', 1, 3);

-- 自動入力された結果を検証
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版
-- 注文サマリーテーブルを作成
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 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 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;
    
    -- 注文数が0になった場合、サマリーレコードを削除
    DELETE FROM order_summary 
    WHERE customer_id = OLD.customer_id AND total_orders <= 0;
END;

-- SQLite版：注文合計を自動計算
CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- 作成日時を自動設定
    SELECT NEW.created_at IS NULL THEN
        SET NEW.created_at = datetime('now', 'localtime');
    END IF;
    
    -- 金額が正の数であることを検証
    IF NEW.amount < 0 THEN
        SELECT RAISE(ABORT, '注文金額は負の数にできません');
    END IF;
END;