-- Migration: Forum Categories, Statuses, and Attachments
-- Date: 2026-01-10
-- Description: Extends forum with categories, status tracking, and file attachments

-- ============================================
-- PHASE 1: Categories and Statuses for Topics
-- ============================================

-- Add category column (default: question)
ALTER TABLE forum_topics
ADD COLUMN IF NOT EXISTS category VARCHAR(50) DEFAULT 'question';

-- Add status column (default: new)
ALTER TABLE forum_topics
ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'new';

-- Add admin tracking for status changes
ALTER TABLE forum_topics
ADD COLUMN IF NOT EXISTS status_changed_by INTEGER REFERENCES users(id);

ALTER TABLE forum_topics
ADD COLUMN IF NOT EXISTS status_changed_at TIMESTAMP;

ALTER TABLE forum_topics
ADD COLUMN IF NOT EXISTS status_note TEXT;

-- Create indexes for filtering
CREATE INDEX IF NOT EXISTS idx_forum_topics_category ON forum_topics(category);
CREATE INDEX IF NOT EXISTS idx_forum_topics_status ON forum_topics(status);

-- ============================================
-- PHASE 2: File Attachments
-- ============================================

-- Create forum_attachments table
CREATE TABLE IF NOT EXISTS forum_attachments (
    id SERIAL PRIMARY KEY,

    -- Polymorphic relationship (topic or reply)
    attachment_type VARCHAR(20) NOT NULL,  -- 'topic' or 'reply'
    topic_id INTEGER REFERENCES forum_topics(id) ON DELETE CASCADE,
    reply_id INTEGER REFERENCES forum_replies(id) ON DELETE CASCADE,

    -- File metadata
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL UNIQUE,
    file_extension VARCHAR(10) NOT NULL,
    file_size INTEGER NOT NULL,  -- in bytes
    mime_type VARCHAR(100) NOT NULL,

    -- Uploader
    uploaded_by INTEGER REFERENCES users(id) NOT NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),

    -- Constraints
    CONSTRAINT chk_attachment_type CHECK (attachment_type IN ('topic', 'reply')),
    CONSTRAINT chk_file_size CHECK (file_size <= 5242880),  -- 5MB max
    CONSTRAINT chk_attachment_target CHECK (
        (attachment_type = 'topic' AND topic_id IS NOT NULL AND reply_id IS NULL) OR
        (attachment_type = 'reply' AND reply_id IS NOT NULL AND topic_id IS NULL)
    )
);

-- Indexes for attachments
CREATE INDEX IF NOT EXISTS idx_forum_attachments_topic ON forum_attachments(topic_id) WHERE topic_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_attachments_reply ON forum_attachments(reply_id) WHERE reply_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_attachments_uploaded_by ON forum_attachments(uploaded_by);

-- ============================================
-- PERMISSIONS
-- ============================================

-- Grant permissions to app user
GRANT ALL ON TABLE forum_attachments TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE forum_attachments_id_seq TO nordabiz_app;

-- ============================================
-- VERIFICATION
-- ============================================

-- Check columns were added
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'forum_topics'
AND column_name IN ('category', 'status', 'status_changed_by', 'status_changed_at', 'status_note');

-- Check attachments table exists
SELECT table_name FROM information_schema.tables WHERE table_name = 'forum_attachments';
