schema.sql

-- ============================================================
-- Robot-BoT Database Schema
-- SQLite 互換 / PostgreSQL 対応
-- 共通カラム: *_id, created_at, updated_at, source, history_ref
-- ソフトデリート対応テーブル: deleted_at, deleted_by も共通
-- source 値: home-bot / home-web / cf-web / d1 / turso
-- ============================================================


-- ============================================================
-- メンバー系
-- ============================================================

CREATE TABLE team_roles (
    team_role_id    TEXT        PRIMARY KEY,
    name            TEXT        NOT NULL,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

-- 初期データ
-- INSERT INTO team_roles VALUES ('<uuid>', 'キャプテン',               '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO team_roles VALUES ('<uuid>', 'プロジェクトマネージャー', '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO team_roles VALUES ('<uuid>', '安全管理',                 '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO team_roles VALUES ('<uuid>', 'PR担当',                   '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO team_roles VALUES ('<uuid>', '操縦手',                   '<ISO8601>', '<ISO8601>', 'home-web', NULL);

CREATE TABLE members (
    member_id       TEXT        PRIMARY KEY,
    name            TEXT        NOT NULL,
    discord_id      TEXT        NOT NULL UNIQUE,    -- 代表 Discord ID
    is_active       INTEGER     NOT NULL DEFAULT 1, -- 1=在籍中 0=脱退
    joined_at       TEXT        NOT NULL,
    left_at         TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE member_team_roles (
    member_team_role_id TEXT    PRIMARY KEY,
    member_id           TEXT    NOT NULL REFERENCES members(member_id),
    team_role_id        TEXT    NOT NULL REFERENCES team_roles(team_role_id),
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

-- member_auth は廃止・member_discord_accounts に統合

CREATE TABLE member_roles (
    member_role_id  TEXT    PRIMARY KEY,
    member_id       TEXT    NOT NULL REFERENCES members(member_id),
    role            TEXT    NOT NULL,
                            -- admin / sub_admin / member
    granted_by      TEXT    REFERENCES members(member_id),
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    deleted_at      TEXT    DEFAULT NULL,
    deleted_by      TEXT    REFERENCES members(member_id),
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE member_discord_accounts (
    discord_account_id          TEXT    PRIMARY KEY,
    member_id                   TEXT    NOT NULL REFERENCES members(member_id),
    discord_id                  TEXT    NOT NULL UNIQUE,
    username                    TEXT    NOT NULL,
    email                       TEXT    NOT NULL UNIQUE,
    totp_secret                 TEXT,
    discord_access_token        TEXT,
    discord_refresh_token       TEXT,
    discord_token_expires_at    TEXT,
    created_at                  TEXT    NOT NULL,
    updated_at                  TEXT    NOT NULL,
    deleted_at                  TEXT    DEFAULT NULL,
    deleted_by                  TEXT    REFERENCES members(member_id),
    source                      TEXT    NOT NULL,
    history_ref                 TEXT
);

CREATE TABLE member_passkeys (
    passkey_id          TEXT    PRIMARY KEY,
    discord_account_id  TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    credential_id       TEXT    NOT NULL UNIQUE,
    public_key          TEXT    NOT NULL,
    device_name         TEXT,
    registered_at       TEXT    NOT NULL,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    deleted_at          TEXT    DEFAULT NULL,
    deleted_by          TEXT    REFERENCES members(member_id),
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE member_sessions (
    session_id          TEXT    PRIMARY KEY,
    discord_account_id  TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    session_token       TEXT    NOT NULL UNIQUE,
    device_info         TEXT,
    last_active_at      TEXT    NOT NULL,
    expires_at          TEXT    NOT NULL,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    deleted_at          TEXT    DEFAULT NULL,
    deleted_by          TEXT    REFERENCES members(member_id),
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE member_devices (
    device_id               TEXT    PRIMARY KEY,
    discord_account_id      TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    fingerprint_data        TEXT    NOT NULL,
    device_name             TEXT,
    last_seen_at            TEXT    NOT NULL,
    created_at              TEXT    NOT NULL,
    updated_at              TEXT    NOT NULL,
    deleted_at              TEXT    DEFAULT NULL,
    deleted_by              TEXT    REFERENCES members(member_id),
    source                  TEXT    NOT NULL,
    history_ref             TEXT
);

CREATE TABLE member_emergency_keys (
    key_id          TEXT    PRIMARY KEY,
    member_id       TEXT    NOT NULL REFERENCES members(member_id),
    issued_by       TEXT    REFERENCES members(member_id),
    key_hash        TEXT,
    status          TEXT    NOT NULL DEFAULT 'pending',
                            -- pending / issued / used / expired
    request_at      TEXT    NOT NULL,
    expires_at      TEXT,
    used_at         TEXT,
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    deleted_at      TEXT    DEFAULT NULL,
    deleted_by      TEXT    REFERENCES members(member_id),
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE member_login_notification_settings (
    setting_id                  TEXT    PRIMARY KEY,
    discord_account_id          TEXT    NOT NULL UNIQUE REFERENCES member_discord_accounts(discord_account_id),
    notify_on_session           INTEGER NOT NULL DEFAULT 0,
    notify_on_oauth             INTEGER NOT NULL DEFAULT 1,
    notify_on_new_device        INTEGER NOT NULL DEFAULT 1,
    notify_on_passkey           INTEGER NOT NULL DEFAULT 1,
    notify_on_totp              INTEGER NOT NULL DEFAULT 1,
    notify_on_emergency_key     INTEGER NOT NULL DEFAULT 1,
    created_at                  TEXT    NOT NULL,
    updated_at                  TEXT    NOT NULL,
    deleted_at                  TEXT    DEFAULT NULL,
    deleted_by                  TEXT    REFERENCES members(member_id),
    source                      TEXT    NOT NULL,
    history_ref                 TEXT
);

CREATE TABLE member_login_history (
    login_id                TEXT    PRIMARY KEY,
    discord_account_id      TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    login_method            TEXT    NOT NULL,
                                    -- session / oauth / passkey / totp / emergency_key
    os                      TEXT,
    browser                 TEXT,
    device_id               TEXT    REFERENCES member_devices(device_id),
    is_new_device           INTEGER NOT NULL DEFAULT 0,
    logged_in_at            TEXT    NOT NULL,
    created_at              TEXT    NOT NULL,
    updated_at              TEXT    NOT NULL,
    source                  TEXT    NOT NULL,
    history_ref             TEXT
);

CREATE TABLE permanent_delete_logs (
    log_id          TEXT    PRIMARY KEY,
    deleted_by      TEXT    NOT NULL REFERENCES members(member_id),
    target_table    TEXT    NOT NULL,
    target_id       TEXT    NOT NULL,
    snapshot        TEXT    NOT NULL,   -- 削除前データ JSON
    deleted_at      TEXT    NOT NULL,
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    source          TEXT    NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- 活動系
-- ============================================================

CREATE TABLE venues (
    venue_id        TEXT        PRIMARY KEY,
    name            TEXT        NOT NULL,
    location        TEXT,               -- 住所または URL
    memo            TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    deleted_at      TEXT        DEFAULT NULL,
    deleted_by      TEXT        REFERENCES members(member_id),
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE activity_sessions (
    session_id          TEXT    PRIMARY KEY,
    venue_id            TEXT    NOT NULL REFERENCES venues(venue_id),
    date                TEXT    NOT NULL,
    planned_start_at    TEXT    NOT NULL,
    planned_end_at      TEXT    NOT NULL,
    actual_start_at     TEXT,
    actual_end_at       TEXT,
    work_end_at         TEXT,
    cleanup_end_at      TEXT,
    is_cancelled        INTEGER NOT NULL DEFAULT 0,
    cancel_reason       TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE attendance_plans (
    plan_id                 TEXT    PRIMARY KEY,
    session_id              TEXT    NOT NULL REFERENCES activity_sessions(session_id),
    member_id               TEXT    NOT NULL REFERENCES members(member_id),
    planned_status          TEXT    NOT NULL DEFAULT 'unknown',
                                    -- venue / online / absent / unknown
    planned_late            INTEGER NOT NULL DEFAULT 0,
    planned_arrival_at      TEXT,
    planned_early_leave     INTEGER NOT NULL DEFAULT 0,
    planned_leave_at        TEXT,
    absence_reason          TEXT,
    created_at              TEXT    NOT NULL,
    updated_at              TEXT    NOT NULL,
    source                  TEXT    NOT NULL,
    history_ref             TEXT
);

CREATE TABLE attendance_records (
    record_id           TEXT    PRIMARY KEY,
    plan_id             TEXT    REFERENCES attendance_plans(plan_id),
    session_id          TEXT    NOT NULL REFERENCES activity_sessions(session_id),
    member_id           TEXT    NOT NULL REFERENCES members(member_id),
    actual_status       TEXT    NOT NULL DEFAULT 'unknown',
                                -- venue / online / absent / unknown
    is_late             INTEGER NOT NULL DEFAULT 0,
    arrival_at          TEXT,
    is_early_leave      INTEGER NOT NULL DEFAULT 0,
    leave_at            TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE attendance_notes (
    note_id                 TEXT    PRIMARY KEY,
    plan_id                 TEXT    NOT NULL REFERENCES attendance_plans(plan_id),
    discord_account_id      TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    content                 TEXT    NOT NULL,
    created_at              TEXT    NOT NULL,
    updated_at              TEXT    NOT NULL,
    deleted_at              TEXT    DEFAULT NULL,
    deleted_by              TEXT    REFERENCES members(member_id),
    source                  TEXT    NOT NULL,
    history_ref             TEXT
);

CREATE TABLE attendance_reminder_patterns (
    pattern_id      TEXT    PRIMARY KEY,
    name            TEXT    NOT NULL,
    created_by      TEXT    NOT NULL REFERENCES members(member_id),
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    deleted_at      TEXT    DEFAULT NULL,
    deleted_by      TEXT    REFERENCES members(member_id),
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE attendance_reminder_timings (
    timing_id       TEXT    PRIMARY KEY,
    pattern_id      TEXT    NOT NULL REFERENCES attendance_reminder_patterns(pattern_id),
    timing_type     TEXT    NOT NULL,
                            -- hours_before / days_before
    timing_value    INTEGER NOT NULL,
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    source          TEXT    NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- アンケート系
-- ============================================================

CREATE TABLE survey_templates (
    template_id     TEXT    PRIMARY KEY,
    title           TEXT    NOT NULL,
    created_by      TEXT    NOT NULL REFERENCES members(member_id),
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    deleted_at      TEXT    DEFAULT NULL,
    deleted_by      TEXT    REFERENCES members(member_id),
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE survey_template_items (
    item_id         TEXT    PRIMARY KEY,
    template_id     TEXT    NOT NULL REFERENCES survey_templates(template_id),
    order_index     INTEGER NOT NULL,
    content         TEXT    NOT NULL,
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE surveys (
    survey_id           TEXT    PRIMARY KEY,
    template_id         TEXT    REFERENCES survey_templates(template_id),
    title               TEXT    NOT NULL,
    created_by          TEXT    NOT NULL REFERENCES members(member_id),
    closes_at           TEXT,               -- NULL = 期限なし
    closed_at           TEXT,
    closed_by           TEXT    REFERENCES members(member_id),
    discord_message_id  TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    deleted_at          TEXT    DEFAULT NULL,
    deleted_by          TEXT    REFERENCES members(member_id),
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE survey_items (
    item_id         TEXT    PRIMARY KEY,
    survey_id       TEXT    NOT NULL REFERENCES surveys(survey_id),
    order_index     INTEGER NOT NULL,
    content         TEXT    NOT NULL,
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE survey_responses (
    response_id             TEXT    PRIMARY KEY,
    survey_id               TEXT    NOT NULL REFERENCES surveys(survey_id),
    item_id                 TEXT    NOT NULL REFERENCES survey_items(item_id),
    discord_account_id      TEXT    NOT NULL REFERENCES member_discord_accounts(discord_account_id),
    response                TEXT    NOT NULL,
                                    -- ok / unsure / ng
    note                    TEXT,
    created_at              TEXT    NOT NULL,
    updated_at              TEXT    NOT NULL,
    source                  TEXT    NOT NULL,
    history_ref             TEXT
);


-- ============================================================
-- 作業系
-- ============================================================

CREATE TABLE task_roles (
    task_role_id    TEXT        PRIMARY KEY,
    name            TEXT        NOT NULL,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

-- 初期データ
-- INSERT INTO task_roles VALUES ('<uuid>', '機械系',           '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO task_roles VALUES ('<uuid>', 'プログラミング系', '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO task_roles VALUES ('<uuid>', '回路系',           '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO task_roles VALUES ('<uuid>', '連絡担当メイン',   '<ISO8601>', '<ISO8601>', 'home-web', NULL);
-- INSERT INTO task_roles VALUES ('<uuid>', '連絡担当サブ',     '<ISO8601>', '<ISO8601>', 'home-web', NULL);

CREATE TABLE tasks (
    task_id             TEXT    PRIMARY KEY,
    parent_task_id      TEXT    REFERENCES tasks(task_id),
    name                TEXT    NOT NULL,
    description         TEXT,
    goal                TEXT,
    status              TEXT    NOT NULL DEFAULT 'todo',
                                -- todo / in_progress / blocked / pending /
                                -- waiting / review / done / cancelled
    priority            INTEGER NOT NULL DEFAULT 50,    -- 1〜100 大きいほど高優先度
    planned_end_at      TEXT,
    estimated_hours     INTEGER,                        -- 予定所要時間（分単位）
    actual_end_at       TEXT,
    is_cancelled        INTEGER NOT NULL DEFAULT 0,
    cancel_reason       TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    deleted_at          TEXT    DEFAULT NULL,
    deleted_by          TEXT    REFERENCES members(member_id),
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE task_assignments (
    task_assignment_id  TEXT    PRIMARY KEY,
    task_id             TEXT    NOT NULL REFERENCES tasks(task_id),
    member_id           TEXT    NOT NULL REFERENCES members(member_id),
    task_role_id        TEXT    REFERENCES task_roles(task_role_id),
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE task_dependency_groups (
    group_id        TEXT    PRIMARY KEY,
    task_id         TEXT    NOT NULL REFERENCES tasks(task_id),
    created_at      TEXT    NOT NULL,
    updated_at      TEXT    NOT NULL,
    source          TEXT    NOT NULL,
    history_ref     TEXT
);

CREATE TABLE task_dependencies (
    dependency_id       TEXT    PRIMARY KEY,
    group_id            TEXT    NOT NULL REFERENCES task_dependency_groups(group_id),
    depends_on_task_id  TEXT    NOT NULL REFERENCES tasks(task_id),
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE task_progress_history (
    history_id          TEXT    PRIMARY KEY,
    task_id             TEXT    NOT NULL REFERENCES tasks(task_id),
    changed_by          TEXT    NOT NULL REFERENCES members(member_id),
    previous_status     TEXT,
    new_status          TEXT,
    previous_priority   INTEGER,
    new_priority        INTEGER,
    memo                TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE task_memos (
    memo_id         TEXT        PRIMARY KEY,
    task_id         TEXT        NOT NULL REFERENCES tasks(task_id),
    member_id       TEXT        NOT NULL REFERENCES members(member_id),
    content         TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    deleted_at      TEXT        DEFAULT NULL,
    deleted_by      TEXT        REFERENCES members(member_id),
    source          TEXT        NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- 手順系
-- ============================================================

CREATE TABLE procedure_templates (
    template_id     TEXT        PRIMARY KEY,
    task_id         TEXT        REFERENCES tasks(task_id),  -- NULL なら汎用テンプレート
    name            TEXT        NOT NULL,
    description     TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    deleted_at      TEXT        DEFAULT NULL,
    deleted_by      TEXT        REFERENCES members(member_id),
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE procedure_result_definitions (
    result_id       TEXT        PRIMARY KEY,
    template_id     TEXT        NOT NULL REFERENCES procedure_templates(template_id),
    value           TEXT        NOT NULL,   -- ok / ng / skip / timeout など
    label           TEXT        NOT NULL,   -- 「正常」「異常」など表示名
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE procedure_template_steps (
    step_id         TEXT        PRIMARY KEY,
    template_id     TEXT        NOT NULL REFERENCES procedure_templates(template_id),
    order_index     INTEGER     NOT NULL,
    name            TEXT        NOT NULL,
    description     TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE procedure_step_transitions (
    transition_id       TEXT    PRIMARY KEY,
    from_step_id        TEXT    NOT NULL REFERENCES procedure_template_steps(step_id),
    to_step_id          TEXT    NOT NULL REFERENCES procedure_template_steps(step_id),
    priority            INTEGER NOT NULL,
    condition_type      TEXT    NOT NULL,   -- result / always
    condition_value     TEXT,               -- condition_type=result のときの result value
    label               TEXT,               -- 「安全確認OK」など表示名
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE procedure_instances (
    instance_id     TEXT        PRIMARY KEY,
    task_id         TEXT        NOT NULL REFERENCES tasks(task_id),
    template_id     TEXT        NOT NULL REFERENCES procedure_templates(template_id),
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    deleted_at      TEXT        DEFAULT NULL,
    deleted_by      TEXT        REFERENCES members(member_id),
    source          TEXT        NOT NULL,
    history_ref     TEXT
);

CREATE TABLE procedure_instance_steps (
    instance_step_id    TEXT    PRIMARY KEY,
    instance_id         TEXT    NOT NULL REFERENCES procedure_instances(instance_id),
    step_id             TEXT    NOT NULL REFERENCES procedure_template_steps(step_id),
    order_index         INTEGER NOT NULL,
    name                TEXT    NOT NULL,
    result_value        TEXT,
    is_done             INTEGER NOT NULL DEFAULT 0,
    done_by             TEXT    REFERENCES members(member_id),
    done_at             TEXT,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);


-- ============================================================
-- 活動×作業紐付け
-- ============================================================

CREATE TABLE session_task_records (
    record_id       TEXT        PRIMARY KEY,
    session_id      TEXT        NOT NULL REFERENCES activity_sessions(session_id),
    task_id         TEXT        NOT NULL REFERENCES tasks(task_id),
    progress_note   TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- 通知系
-- ============================================================

CREATE TABLE notification_rules (
    rule_id             TEXT    PRIMARY KEY,
    type                TEXT    NOT NULL,
                                -- delay_pre / delay_progress / delay_over
                                -- session_remind / attendance_remind / procedure_done
    discord_channel_id  TEXT    NOT NULL,
    is_active           INTEGER NOT NULL DEFAULT 1,
    created_at          TEXT    NOT NULL,
    updated_at          TEXT    NOT NULL,
    source              TEXT    NOT NULL,
    history_ref         TEXT
);

CREATE TABLE notification_timings (
    timing_id       TEXT        PRIMARY KEY,
    rule_id         TEXT        NOT NULL REFERENCES notification_rules(rule_id),
    timing_type     TEXT        NOT NULL,   -- days_before / hours_before
    timing_value    INTEGER     NOT NULL,
                                -- days_before:  正=期限前 0=当日 負=期限後
                                -- hours_before: 正=前 負=後
    time_of_day     TEXT,                   -- HH:MM hours_before の場合 NULL
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    source          TEXT        NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- 共通 URL 管理
-- ============================================================

CREATE TABLE urls (
    url_id          TEXT        PRIMARY KEY,
    target_type     TEXT        NOT NULL,
                                -- task / task_memo / procedure_step など
    target_id       TEXT        NOT NULL,
    url             TEXT        NOT NULL,
    label           TEXT,
    created_at      TEXT        NOT NULL,
    updated_at      TEXT        NOT NULL,
    deleted_at      TEXT        DEFAULT NULL,
    deleted_by      TEXT        REFERENCES members(member_id),
    source          TEXT        NOT NULL,
    history_ref     TEXT
);


-- ============================================================
-- インデックス
-- ============================================================

-- members
CREATE INDEX idx_members_discord_id ON members(discord_id);
CREATE INDEX idx_members_is_active ON members(is_active);

-- member_discord_accounts
CREATE INDEX idx_member_discord_accounts_member_id ON member_discord_accounts(member_id);
CREATE INDEX idx_member_discord_accounts_discord_id ON member_discord_accounts(discord_id);

-- member_devices
CREATE INDEX idx_member_devices_discord_account_id ON member_devices(discord_account_id);

-- member_login_history
CREATE INDEX idx_member_login_history_discord_account_id ON member_login_history(discord_account_id);
CREATE INDEX idx_member_login_history_logged_in_at ON member_login_history(logged_in_at);

-- activity_sessions
CREATE INDEX idx_activity_sessions_date ON activity_sessions(date);
CREATE INDEX idx_activity_sessions_venue_id ON activity_sessions(venue_id);

-- attendance
CREATE INDEX idx_attendance_plans_session_id ON attendance_plans(session_id);
CREATE INDEX idx_attendance_plans_member_id ON attendance_plans(member_id);
CREATE INDEX idx_attendance_records_session_id ON attendance_records(session_id);
CREATE INDEX idx_attendance_records_member_id ON attendance_records(member_id);
CREATE INDEX idx_attendance_notes_plan_id ON attendance_notes(plan_id);

-- surveys
CREATE INDEX idx_surveys_created_by ON surveys(created_by);
CREATE INDEX idx_survey_responses_survey_id ON survey_responses(survey_id);
CREATE INDEX idx_survey_responses_discord_account_id ON survey_responses(discord_account_id);

-- tasks
CREATE INDEX idx_tasks_parent_task_id ON tasks(parent_task_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_planned_end_at ON tasks(planned_end_at);

-- task_assignments
CREATE INDEX idx_task_assignments_task_id ON task_assignments(task_id);
CREATE INDEX idx_task_assignments_member_id ON task_assignments(member_id);

-- procedure
CREATE INDEX idx_procedure_instances_task_id ON procedure_instances(task_id);
CREATE INDEX idx_procedure_instance_steps_instance_id ON procedure_instance_steps(instance_id);

-- urls
CREATE INDEX idx_urls_target ON urls(target_type, target_id);

-- notifications
CREATE INDEX idx_notification_timings_rule_id ON notification_timings(rule_id);