TODO_list_system/mysql/init/01-init.sql

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS todo_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE todo_system;

-- Table: todo_item
CREATE TABLE IF NOT EXISTS todo_item (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('NEW', 'DOING', 'BLOCKED', 'DONE') DEFAULT 'NEW',
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT') DEFAULT 'MEDIUM',
    due_date DATE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    creator_ad VARCHAR(128) NOT NULL,
    creator_display_name VARCHAR(128),
    creator_email VARCHAR(256),
    starred TINYINT(1) DEFAULT 0,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_due_date (due_date),
    INDEX idx_creator_ad (creator_ad),
    INDEX idx_starred (starred),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_item_responsible
CREATE TABLE IF NOT EXISTS todo_item_responsible (
    todo_id CHAR(36) NOT NULL,
    ad_account VARCHAR(128) NOT NULL,
    added_by VARCHAR(128),
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (todo_id, ad_account),
    FOREIGN KEY (todo_id) REFERENCES todo_item(id) ON DELETE CASCADE,
    INDEX idx_ad_account (ad_account)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_item_follower
CREATE TABLE IF NOT EXISTS todo_item_follower (
    todo_id CHAR(36) NOT NULL,
    ad_account VARCHAR(128) NOT NULL,
    added_by VARCHAR(128),
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (todo_id, ad_account),
    FOREIGN KEY (todo_id) REFERENCES todo_item(id) ON DELETE CASCADE,
    INDEX idx_ad_account (ad_account)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_mail_log
CREATE TABLE IF NOT EXISTS todo_mail_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    todo_id CHAR(36),
    type ENUM('SCHEDULED', 'FIRE') NOT NULL,
    triggered_by_ad VARCHAR(128),
    recipients TEXT,
    subject VARCHAR(255),
    status ENUM('QUEUED', 'SENT', 'FAILED') DEFAULT 'QUEUED',
    provider_msg_id VARCHAR(128),
    error_text TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME,
    FOREIGN KEY (todo_id) REFERENCES todo_item(id) ON DELETE CASCADE,
    INDEX idx_todo_id (todo_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_triggered_by (triggered_by_ad),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_audit_log
CREATE TABLE IF NOT EXISTS todo_audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    actor_ad VARCHAR(128) NOT NULL,
    todo_id CHAR(36),
    action ENUM('CREATE', 'UPDATE', 'DELETE', 'COMPLETE', 'IMPORT', 'MAIL_SENT', 'MAIL_FAIL') NOT NULL,
    detail JSON,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (todo_id) REFERENCES todo_item(id) ON DELETE SET NULL,
    INDEX idx_actor_ad (actor_ad),
    INDEX idx_todo_id (todo_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_user_pref
CREATE TABLE IF NOT EXISTS todo_user_pref (
    ad_account VARCHAR(128) PRIMARY KEY,
    email VARCHAR(256),
    display_name VARCHAR(128),
    theme ENUM('light', 'dark', 'auto') DEFAULT 'auto',
    language VARCHAR(10) DEFAULT 'zh-TW',
    timezone VARCHAR(50) DEFAULT 'Asia/Taipei',
    notification_enabled TINYINT(1) DEFAULT 1,
    email_reminder_enabled TINYINT(1) DEFAULT 1,
    weekly_summary_enabled TINYINT(1) DEFAULT 1,
    fire_email_today_count INT DEFAULT 0,
    fire_email_last_reset DATE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_import_job
CREATE TABLE IF NOT EXISTS todo_import_job (
    id CHAR(36) PRIMARY KEY,
    actor_ad VARCHAR(128) NOT NULL,
    filename VARCHAR(255),
    total_rows INT DEFAULT 0,
    success_rows INT DEFAULT 0,
    failed_rows INT DEFAULT 0,
    status ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED') DEFAULT 'PENDING',
    error_file_path VARCHAR(500),
    error_details JSON,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    INDEX idx_actor_ad (actor_ad),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: todo_fire_email_log
CREATE TABLE IF NOT EXISTS todo_fire_email_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    todo_id CHAR(36) NOT NULL,
    sender_ad VARCHAR(128) NOT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (todo_id) REFERENCES todo_item(id) ON DELETE CASCADE,
    INDEX idx_todo_sender_time (todo_id, sender_ad, sent_at),
    INDEX idx_sender_time (sender_ad, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;