docs/00_init.sql

-- ============================================================
-- Afool Group Bot — 完整初始化 DDL (合并版)
-- 将 01_database_ddl.sql + 02/03 迁移脚本 + models.py 对齐
-- 生成时间: 2026-03-24
-- 用法: psql -U <user> -d <db> -f 00_init.sql
-- 所有语句均使用 IF NOT EXISTS，可安全重复执行
-- ============================================================

CREATE SCHEMA IF NOT EXISTS tgbot;
SET search_path TO tgbot, public;

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================
-- 1) Admins & sessions
-- ============================================================
CREATE TABLE IF NOT EXISTS admins (
    id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text        UNIQUE NOT NULL,
    password_hash text        NOT NULL,
    telegram_user_id bigint,
    role          text        NOT NULL DEFAULT 'admin',
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_admins_telegram_user_id
    ON admins (telegram_user_id);

CREATE TABLE IF NOT EXISTS admin_sessions (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_id   uuid        NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
    token      text        UNIQUE NOT NULL,
    expires_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_admin_sessions_admin_id
    ON admin_sessions (admin_id);
CREATE INDEX IF NOT EXISTS idx_admin_sessions_expires_at
    ON admin_sessions (expires_at);

-- ============================================================
-- 2) Groups & members
-- ============================================================
CREATE TABLE IF NOT EXISTS groups (
    id                uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    telegram_group_id bigint      UNIQUE NOT NULL,
    title             text        NOT NULL,
    type              text        NOT NULL,
    owner_user_id     bigint,
    created_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_groups_owner_user_id
    ON groups (owner_user_id);

CREATE TABLE IF NOT EXISTS group_members (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    username         text,
    display_name     text,
    role             text        NOT NULL DEFAULT 'member',
    joined_at        timestamptz,
    updated_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_group_members_group_user
    ON group_members (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_group_members_group_id
    ON group_members (group_id);
CREATE INDEX IF NOT EXISTS idx_group_members_group_role
    ON group_members (group_id, role);
CREATE INDEX IF NOT EXISTS idx_group_members_group_updated_at
    ON group_members (group_id, updated_at);

CREATE TABLE IF NOT EXISTS group_admins (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    role             text        NOT NULL DEFAULT 'admin',
    synced_at        timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_group_admins_group_user
    ON group_admins (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_group_admins_group_id
    ON group_admins (group_id);

-- ============================================================
-- 3) Group config
-- ============================================================
CREATE TABLE IF NOT EXISTS group_configs (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id    uuid        NOT NULL UNIQUE REFERENCES groups(id) ON DELETE CASCADE,
    config_json jsonb       NOT NULL DEFAULT '{}'::jsonb,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_group_configs_config_json
    ON group_configs USING gin (config_json);

-- ============================================================
-- 4) Verification
-- ============================================================
CREATE TABLE IF NOT EXISTS verification_sessions (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    verify_mode      text        NOT NULL,
    status           text        NOT NULL DEFAULT 'pending',
    challenge_json   jsonb,
    expires_at       timestamptz NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_verification_sessions_group_user
    ON verification_sessions (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_verification_sessions_group_status
    ON verification_sessions (group_id, status);
CREATE INDEX IF NOT EXISTS idx_verification_sessions_expires_at
    ON verification_sessions (expires_at);

CREATE TABLE IF NOT EXISTS verification_attempts (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id uuid        NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE,
    result     text        NOT NULL,
    reason     text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_verification_attempts_session_id
    ON verification_attempts (session_id);
CREATE INDEX IF NOT EXISTS idx_verification_attempts_created_at
    ON verification_attempts (created_at);

CREATE TABLE IF NOT EXISTS verification_cooldowns (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    cooldown_until   timestamptz NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_verification_cooldowns_group_user
    ON verification_cooldowns (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_verification_cooldowns_until
    ON verification_cooldowns (cooldown_until);

-- ============================================================
-- 5) Rules (generic)
-- ============================================================
CREATE TABLE IF NOT EXISTS rules (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id   uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    type       text        NOT NULL,
    name       text        NOT NULL,
    enabled    bool        NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rules_group_type
    ON rules (group_id, type);
CREATE INDEX IF NOT EXISTS idx_rules_group_enabled
    ON rules (group_id, enabled);

CREATE TABLE IF NOT EXISTS rule_conditions (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id     uuid NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    match_type  text NOT NULL,
    match_value text NOT NULL,
    flags       text
);

CREATE INDEX IF NOT EXISTS idx_rule_conditions_rule_id
    ON rule_conditions (rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_conditions_match_type
    ON rule_conditions (match_type);

CREATE TABLE IF NOT EXISTS rule_actions (
    id          uuid  PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id     uuid  NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
    action_type text  NOT NULL,
    payload     jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_rule_actions_rule_id
    ON rule_actions (rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_actions_payload
    ON rule_actions USING gin (payload);

-- ============================================================
-- Media assets (created early — referenced by auto_reply_items,
--   message_events, scheduled_messages, broadcasts, posts)
-- ============================================================
CREATE TABLE IF NOT EXISTS media_assets (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    telegram_file_id text UNIQUE NOT NULL,
    file_hash        text UNIQUE NOT NULL,
    file_type        text NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now()
);

-- ============================================================
-- 6) Auto replies
-- ============================================================
CREATE TABLE IF NOT EXISTS auto_replies (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id     uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    match_type   text        NOT NULL,
    match_value  text        NOT NULL,
    random_pick  bool        NOT NULL DEFAULT false,
    delete_mode  text        NOT NULL DEFAULT 'none',
    delete_after int,
    enabled      bool        NOT NULL DEFAULT true,
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_auto_replies_group_id
    ON auto_replies (group_id);
CREATE INDEX IF NOT EXISTS idx_auto_replies_group_enabled
    ON auto_replies (group_id, enabled);

CREATE TABLE IF NOT EXISTS auto_reply_items (
    id            uuid  PRIMARY KEY DEFAULT gen_random_uuid(),
    auto_reply_id uuid  NOT NULL REFERENCES auto_replies(id) ON DELETE CASCADE,
    content_type  text  NOT NULL,
    content       text,
    parse_mode    text,
    media_id      uuid  REFERENCES media_assets(id),
    buttons_json  jsonb
);

CREATE INDEX IF NOT EXISTS idx_auto_reply_items_reply_id
    ON auto_reply_items (auto_reply_id);

-- ============================================================
-- 7) Auto delete / auto ban rules
-- ============================================================
CREATE TABLE IF NOT EXISTS delete_rules (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id    uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    match_type  text        NOT NULL,
    match_value text        NOT NULL,
    enabled     bool        NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_delete_rules_group_enabled
    ON delete_rules (group_id, enabled);
CREATE INDEX IF NOT EXISTS idx_delete_rules_group_match_type
    ON delete_rules (group_id, match_type);

CREATE TABLE IF NOT EXISTS ban_rules (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id    uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    match_type  text        NOT NULL,
    match_value text        NOT NULL,
    action      text        NOT NULL,
    duration    int,
    enabled     bool        NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_ban_rules_group_enabled
    ON ban_rules (group_id, enabled);
CREATE INDEX IF NOT EXISTS idx_ban_rules_group_action
    ON ban_rules (group_id, action);

-- ============================================================
-- 8) AI moderation & media assets
-- ============================================================
CREATE TABLE IF NOT EXISTS ai_models (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    name       text        NOT NULL,
    version    text        NOT NULL,
    type       text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_ai_models_type
    ON ai_models (type);
CREATE INDEX IF NOT EXISTS idx_ai_models_name_version
    ON ai_models (name, version);

-- (media_assets already created above)

CREATE TABLE IF NOT EXISTS message_events (
    id                   uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id             uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    chat_id              bigint,
    telegram_message_id  bigint      NOT NULL,
    telegram_user_id     bigint      NOT NULL,
    content_type         text        NOT NULL,
    text                 text,
    media_id             uuid        REFERENCES media_assets(id),
    created_at           timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_message_events_group_message
    ON message_events (group_id, telegram_message_id);
CREATE INDEX IF NOT EXISTS idx_message_events_group_created
    ON message_events (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_message_events_group_user
    ON message_events (group_id, telegram_user_id);

CREATE TABLE IF NOT EXISTS ai_detections (
    id         uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id   uuid          NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    message_id uuid          NOT NULL REFERENCES message_events(id) ON DELETE CASCADE,
    type       text          NOT NULL,
    score      numeric(5,4)  NOT NULL,
    threshold  numeric(5,4)  NOT NULL,
    model_id   uuid          REFERENCES ai_models(id),
    result     text          NOT NULL,
    created_at timestamptz   NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_ai_detections_group_created
    ON ai_detections (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_ai_detections_group_result
    ON ai_detections (group_id, result);
CREATE INDEX IF NOT EXISTS idx_ai_detections_message_id
    ON ai_detections (message_id);

-- ============================================================
-- 9) Logs
-- ============================================================
CREATE TABLE IF NOT EXISTS moderation_actions (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    action           text        NOT NULL,
    reason           text,
    duration         int,
    source           text        NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_moderation_actions_group_created
    ON moderation_actions (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_moderation_actions_group_user
    ON moderation_actions (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_moderation_actions_group_action
    ON moderation_actions (group_id, action);

CREATE TABLE IF NOT EXISTS audit_logs (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id   uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    admin_id   uuid        NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
    action     text        NOT NULL,
    payload    jsonb       NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_audit_logs_group_created
    ON audit_logs (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_logs_admin_id
    ON audit_logs (admin_id);

-- ============================================================
-- 10) Scheduling
-- ============================================================
CREATE TABLE IF NOT EXISTS scheduled_messages (
    id           uuid  PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id     uuid  NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    content_type text  NOT NULL,
    content      text,
    media_id     uuid  REFERENCES media_assets(id),
    buttons_json jsonb
);

CREATE INDEX IF NOT EXISTS idx_scheduled_messages_group_id
    ON scheduled_messages (group_id);

CREATE TABLE IF NOT EXISTS schedules (
    id                   uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id             uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    scheduled_message_id uuid        NOT NULL REFERENCES scheduled_messages(id) ON DELETE CASCADE,
    cron_expr            text        NOT NULL,
    next_run_at          timestamptz NOT NULL,
    enabled              bool        NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_schedules_next_run_at
    ON schedules (next_run_at);
CREATE INDEX IF NOT EXISTS idx_schedules_group_enabled
    ON schedules (group_id, enabled);

-- ============================================================
-- 11) Spam counters
-- ============================================================
CREATE TABLE IF NOT EXISTS spam_counters (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    window_start     timestamptz NOT NULL,
    count            int         NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_spam_counters_group_user
    ON spam_counters (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_spam_counters_window_start
    ON spam_counters (window_start);

-- ============================================================
-- 12) Stats
-- ============================================================
CREATE TABLE IF NOT EXISTS stats_messages_daily (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id      uuid NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    date          date NOT NULL,
    message_count int  NOT NULL DEFAULT 0,
    active_users  int  NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_stats_messages_daily_group_date
    ON stats_messages_daily (group_id, date);
CREATE INDEX IF NOT EXISTS idx_stats_messages_daily_group_date
    ON stats_messages_daily (group_id, date);

CREATE TABLE IF NOT EXISTS stats_wordcloud_terms (
    id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id uuid NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    date     date NOT NULL,
    term     text NOT NULL,
    count    int  NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_wordcloud_terms_group_date
    ON stats_wordcloud_terms (group_id, date);
CREATE INDEX IF NOT EXISTS idx_wordcloud_terms_group_term
    ON stats_wordcloud_terms (group_id, term);

-- ============================================================
-- 13) Invite links
-- ============================================================
CREATE TABLE IF NOT EXISTS invite_links (
    id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id   uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    invite_link text       NOT NULL,
    created_by bigint      NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_invite_links_group_id
    ON invite_links (group_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_invite_links_link
    ON invite_links (invite_link);

CREATE TABLE IF NOT EXISTS invite_records (
    id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id        uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    inviter_user_id bigint      NOT NULL,
    invitee_user_id bigint      NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_invite_records_group_created
    ON invite_records (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_invite_records_group_inviter_created
    ON invite_records (group_id, inviter_user_id, created_at);

-- ============================================================
-- 14) Verified users
-- ============================================================
CREATE TABLE IF NOT EXISTS verified_users (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    status           text        NOT NULL DEFAULT 'pending',
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_verified_users_group_user
    ON verified_users (group_id, telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_verified_users_group_status
    ON verified_users (group_id, status);

CREATE TABLE IF NOT EXISTS verified_profiles (
    id               uuid  PRIMARY KEY DEFAULT gen_random_uuid(),
    verified_user_id uuid  NOT NULL REFERENCES verified_users(id) ON DELETE CASCADE,
    profile_json     jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_verified_profiles_user
    ON verified_profiles (verified_user_id);

CREATE TABLE IF NOT EXISTS verified_forms (
    id          uuid  PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id    uuid  NOT NULL UNIQUE REFERENCES groups(id) ON DELETE CASCADE,
    fields_json jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS verified_reviews (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    reviewer_user_id bigint      NOT NULL,
    target_user_id   bigint      NOT NULL,
    rating           int         NOT NULL,
    content          text,
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_verified_reviews_group_created
    ON verified_reviews (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_verified_reviews_group_target
    ON verified_reviews (group_id, target_user_id);

-- ============================================================
-- 15) Member events
-- ============================================================
CREATE TABLE IF NOT EXISTS member_events (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id         uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    telegram_user_id bigint      NOT NULL,
    event_type       text        NOT NULL,
    old_value        text,
    new_value        text,
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_member_events_group_created
    ON member_events (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_member_events_group_user
    ON member_events (group_id, telegram_user_id);

-- ============================================================
-- 16) Broadcasts
-- ============================================================
CREATE TABLE IF NOT EXISTS broadcasts (
    id                  uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id            uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    content_type        text        NOT NULL,
    content             text,
    media_id            uuid        REFERENCES media_assets(id),
    buttons_json        jsonb,
    pin                 bool        NOT NULL DEFAULT false,
    status              text        NOT NULL DEFAULT 'pending',
    telegram_message_id bigint,
    created_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_broadcasts_group_created
    ON broadcasts (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_broadcasts_group_status
    ON broadcasts (group_id, status);

-- ============================================================
-- 17) Posts (controllerbot-like)
-- ============================================================
CREATE TABLE IF NOT EXISTS posts (
    id                       uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id                 uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    title                    text,
    content_type             text        NOT NULL,
    content                  text,
    parse_mode               text,
    media_id                 uuid        REFERENCES media_assets(id),
    media_ids_json           jsonb,
    buttons_json             jsonb,
    target_chat_id           bigint,
    show_caption_above_media bool        NOT NULL DEFAULT false,
    pin                      bool        NOT NULL DEFAULT false,
    status                   text        NOT NULL DEFAULT 'draft',
    telegram_message_id      bigint,
    created_at               timestamptz NOT NULL DEFAULT now(),
    updated_at               timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_posts_group_created
    ON posts (group_id, created_at);
CREATE INDEX IF NOT EXISTS idx_posts_group_status
    ON posts (group_id, status);

-- ============================================================
-- 18) Heroes
-- ============================================================
CREATE TABLE IF NOT EXISTS heroes (
    id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id       uuid        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    name           text        NOT NULL,
    intro          text,
    parse_mode     text,
    media_ids_json jsonb,
    created_at     timestamptz NOT NULL DEFAULT now(),
    updated_at     timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_heroes_group_created
    ON heroes (group_id, created_at);

-- ============================================================
-- 19) Post schedules
-- ============================================================
CREATE TABLE IF NOT EXISTS post_schedules (
    id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id     uuid        NOT NULL UNIQUE REFERENCES posts(id) ON DELETE CASCADE,
    cron_expr   text        NOT NULL,
    next_run_at timestamptz NOT NULL,
    enabled     bool        NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_post_schedules_next_run
    ON post_schedules (next_run_at);
CREATE INDEX IF NOT EXISTS idx_post_schedules_enabled
    ON post_schedules (enabled);
COMMIT;