docs/01_database_ddl.sql

-- PostgreSQL DDL for Afool Group Bot (v1)
-- Generated on 2026-01-31

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 1) Admins and 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 and 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);

-- 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,
    media_id uuid,
    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
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);

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()
);

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,
    media_id uuid REFERENCES media_assets(id),
    buttons_json jsonb,
    target_chat_id bigint,
    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);
-- Message events include chat_id