-- mstfyldz Journal PostgreSQL Schema for psql

-- Create Custom Enum Types (if not existing)
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'post_type') THEN
        CREATE TYPE post_type AS ENUM ('notebook', 'sticky', 'polaroid', 'code', 'audio');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'mood_type') THEN
        CREATE TYPE mood_type AS ENUM ('focus', 'night', 'spark', 'calm', 'visual');
    END IF;
END $$;

-- Create Posts Table
CREATE TABLE IF NOT EXISTS posts (
    id VARCHAR(255) PRIMARY KEY,
    type post_type NOT NULL,
    date VARCHAR(50) NOT NULL,
    timestamp VARCHAR(50) NOT NULL,
    mood mood_type NOT NULL,
    mood_label VARCHAR(100) NOT NULL,
    title TEXT,
    content TEXT NOT NULL,
    margin_notes TEXT[] DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    likes INT DEFAULT 0 NOT NULL,
    highlight_words TEXT[] DEFAULT '{}',
    author_note TEXT,
    image_url TEXT,
    image_caption TEXT,
    code_snippet TEXT,
    code_language VARCHAR(50),
    audio_duration VARCHAR(20),
    audio_title VARCHAR(255),
    audio_url TEXT,
    stamped_text VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Performance Indexes
CREATE INDEX IF NOT EXISTS idx_posts_mood ON posts(mood);
CREATE INDEX IF NOT EXISTS idx_posts_type ON posts(type);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);