-- Rockview University Staff Support Conversation schema (PostgreSQL)
-- Safe to run more than once. PHP endpoints also create these structures.

CREATE TABLE IF NOT EXISTS staff_support_threads (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL,
    title VARCHAR(180) NOT NULL,
    category VARCHAR(80) NOT NULL DEFAULT 'General Support',
    priority VARCHAR(20) NOT NULL DEFAULT 'Normal',
    campus VARCHAR(180),
    status VARCHAR(30) NOT NULL DEFAULT 'waiting_admin',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMPTZ,
    staff_last_read_at TIMESTAMPTZ,
    admin_last_read_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS staff_support_messages (
    id BIGSERIAL PRIMARY KEY,
    thread_id BIGINT NOT NULL REFERENCES staff_support_threads(id) ON DELETE CASCADE,
    sender_user_id BIGINT NOT NULL,
    sender_role VARCHAR(80),
    body TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS staff_support_media (
    id BIGSERIAL PRIMARY KEY,
    message_id BIGINT NOT NULL REFERENCES staff_support_messages(id) ON DELETE CASCADE,
    media_type VARCHAR(20) NOT NULL DEFAULT 'file',
    file_path TEXT NOT NULL,
    thumbnail_path TEXT,
    original_name VARCHAR(255),
    mime_type VARCHAR(160),
    size_bytes BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_staff_support_threads_author
    ON staff_support_threads(author_id, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_staff_support_threads_status
    ON staff_support_threads(status, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_staff_support_messages_thread
    ON staff_support_messages(thread_id, created_at, id);
CREATE INDEX IF NOT EXISTS idx_staff_support_media_message
    ON staff_support_media(message_id, id);
