-- ============================================================
-- Migration: 001_message_rich_content.sql
-- Tables:  message_attachment, message_embed, message_embed_field,
--          message_poll, message_poll_option, message_poll_vote
-- ============================================================
-- These tables extend the existing `message` table (from appks 001_init.sql)
-- with Discord-style rich content: file attachments, link preview embeds,
-- and interactive polls.

BEGIN;

-- models/message_attachment.rs  →  message_attachment
CREATE TABLE IF NOT EXISTS message_attachment (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id    UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    filename      TEXT NOT NULL,
    content_type  TEXT NULL,
    size          BIGINT NOT NULL,
    url           TEXT NOT NULL,
    storage_key   TEXT NULL,
    width         INTEGER NULL,
    height        INTEGER NULL,
    duration_secs DOUBLE PRECISION NULL,
    blurhash      TEXT NULL,
    spoiler       BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_message_attachment_message_id
    ON message_attachment (message_id);

-- models/message_embed.rs  →  message_embed
CREATE TABLE IF NOT EXISTS message_embed (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id       UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    embed_type       TEXT NOT NULL,
    title            TEXT NULL,
    description      TEXT NULL,
    url              TEXT NULL,
    color            INTEGER NULL,
    image_url        TEXT NULL,
    image_width      INTEGER NULL,
    image_height     INTEGER NULL,
    thumbnail_url    TEXT NULL,
    thumbnail_width  INTEGER NULL,
    thumbnail_height INTEGER NULL,
    video_url        TEXT NULL,
    video_width      INTEGER NULL,
    video_height     INTEGER NULL,
    author_name      TEXT NULL,
    author_url       TEXT NULL,
    author_icon_url  TEXT NULL,
    footer_text      TEXT NULL,
    footer_icon_url  TEXT NULL,
    provider_name    TEXT NULL,
    provider_url     TEXT NULL,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_message_embed_message_id
    ON message_embed (message_id);

-- models/message_embed.rs  →  message_embed_field
CREATE TABLE IF NOT EXISTS message_embed_field (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    embed_id UUID NOT NULL REFERENCES message_embed(id) ON DELETE CASCADE,
    name     TEXT NOT NULL,
    value    TEXT NOT NULL,
    inline   BOOLEAN NOT NULL DEFAULT FALSE,
    position INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_message_embed_field_embed_id
    ON message_embed_field (embed_id);

-- models/message_poll.rs  →  message_poll
CREATE TABLE IF NOT EXISTS message_poll (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id        UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
    question          TEXT NOT NULL,
    allow_multiselect BOOLEAN NOT NULL DEFAULT FALSE,
    max_selections    INTEGER NULL,
    expires_at        TIMESTAMPTZ NULL,
    total_votes       BIGINT NOT NULL DEFAULT 0,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_message_poll_message UNIQUE (message_id)
);
CREATE INDEX IF NOT EXISTS idx_message_poll_message_id
    ON message_poll (message_id);

-- models/message_poll.rs  →  message_poll_option
CREATE TABLE IF NOT EXISTS message_poll_option (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    poll_id    UUID NOT NULL REFERENCES message_poll(id) ON DELETE CASCADE,
    text       TEXT NOT NULL,
    emoji      TEXT NULL,
    vote_count BIGINT NOT NULL DEFAULT 0,
    position   INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_message_poll_option_poll_id
    ON message_poll_option (poll_id);

-- models/message_poll.rs  →  message_poll_vote
CREATE TABLE IF NOT EXISTS message_poll_vote (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    poll_id    UUID NOT NULL REFERENCES message_poll(id) ON DELETE CASCADE,
    option_id  UUID NOT NULL REFERENCES message_poll_option(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_message_poll_vote UNIQUE (poll_id, user_id, option_id)
);
CREATE INDEX IF NOT EXISTS idx_message_poll_vote_poll_id
    ON message_poll_vote (poll_id);
CREATE INDEX IF NOT EXISTS idx_message_poll_vote_user_id
    ON message_poll_vote (user_id);

COMMIT;