scripts/init_db.sql

-- Lüt Petux Remover schema (idempotent — safe to re-run on existing DB)

CREATE TABLE IF NOT EXISTS chats (
    chat_id        BIGINT PRIMARY KEY,
    title          TEXT,
    added_by       BIGINT,
    added_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Gatekeeper: per-chat approval state.
ALTER TABLE chats ADD COLUMN IF NOT EXISTS status        TEXT NOT NULL DEFAULT 'pending';
ALTER TABLE chats ADD COLUMN IF NOT EXISTS status_at     TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE chats ADD COLUMN IF NOT EXISTS status_by     BIGINT;
ALTER TABLE chats ADD COLUMN IF NOT EXISTS member_count  INTEGER;
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.check_constraints
    WHERE constraint_name = 'chats_status_check'
  ) THEN
    ALTER TABLE chats ADD CONSTRAINT chats_status_check
      CHECK (status IN ('pending','approved','rejected','removed'));
  END IF;
END$$;

CREATE TABLE IF NOT EXISTS chat_settings (
    chat_id                  BIGINT PRIMARY KEY REFERENCES chats(chat_id) ON DELETE CASCADE,
    enabled                  BOOLEAN NOT NULL DEFAULT TRUE,
    check_admins             BOOLEAN NOT NULL DEFAULT TRUE,
    threshold                INTEGER NOT NULL DEFAULT 70,

    filter_photos            BOOLEAN NOT NULL DEFAULT TRUE,
    filter_stickers          BOOLEAN NOT NULL DEFAULT TRUE,
    filter_videos            BOOLEAN NOT NULL DEFAULT TRUE,
    filter_gifs              BOOLEAN NOT NULL DEFAULT TRUE,
    filter_video_notes       BOOLEAN NOT NULL DEFAULT TRUE,
    filter_web_preview       BOOLEAN NOT NULL DEFAULT TRUE,

    block_porn               BOOLEAN NOT NULL DEFAULT TRUE,
    block_hentai             BOOLEAN NOT NULL DEFAULT TRUE,
    block_sexy               BOOLEAN NOT NULL DEFAULT FALSE,

    antispam_enabled         BOOLEAN NOT NULL DEFAULT FALSE,
    warn_user                BOOLEAN NOT NULL DEFAULT TRUE,
    max_violations           INTEGER NOT NULL DEFAULT 5,
    violation_window_minutes INTEGER NOT NULL DEFAULT 60,
    ban_duration_minutes     INTEGER NOT NULL DEFAULT 0,
    warn_delete_seconds      INTEGER NOT NULL DEFAULT 30,

    delete_message_template  TEXT NOT NULL DEFAULT
        '{username}, your message was deleted: prohibited content detected',

    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS violations (
    id          BIGSERIAL PRIMARY KEY,
    chat_id     BIGINT NOT NULL,
    user_id     BIGINT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    category    TEXT,
    score       REAL,
    media_kind  TEXT
);

CREATE INDEX IF NOT EXISTS idx_violations_chat_user_time
    ON violations(chat_id, user_id, occurred_at DESC);

CREATE TABLE IF NOT EXISTS sticker_blocklist (
    chat_id     BIGINT NOT NULL,
    set_name    TEXT NOT NULL,
    added_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (chat_id, set_name)
);

-- Events: rich timeline backing the mini-app stats + log screens.
CREATE TABLE IF NOT EXISTS events (
    id          BIGSERIAL PRIMARY KEY,
    chat_id     BIGINT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    kind        TEXT NOT NULL,
    user_id     BIGINT,
    user_name   TEXT,
    category    TEXT,
    score       REAL,
    media_kind  TEXT,
    meta        JSONB
);

CREATE INDEX IF NOT EXISTS idx_events_chat_time
    ON events(chat_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_events_chat_kind_time
    ON events(chat_id, kind, occurred_at DESC);

-- Per-user UI language (RU/AZ/EN). Used by both bot and mini-app.
CREATE TABLE IF NOT EXISTS users (
    user_id     BIGINT PRIMARY KEY,
    lang        TEXT NOT NULL DEFAULT 'en'
                CHECK (lang IN ('ru','az','en')),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);