-- =============================================================================
-- Migration 023: Add posts and post_targets tables
-- Tables: posts, post_targets
-- Purpose: Create a simple posts module and mapping table for target ids
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column)
-- =============================================================================

BEGIN;

-- =============================================================================
-- TABLE: posts
-- =============================================================================

CREATE TABLE IF NOT EXISTS posts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    title VARCHAR(255) NOT NULL,
    content TEXT,

    target_type VARCHAR(20) NOT NULL
        CHECK (target_type IN ('all', 'building', 'floor', 'room')),

    status VARCHAR(20) NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft', 'published', 'archived')),

    is_featured BOOLEAN NOT NULL DEFAULT FALSE,

    author_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,

    published_at TIMESTAMP WITH TIME ZONE,

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- =============================================================================
-- TABLE: post_targets (mapping table for posts -> target ids)
-- =============================================================================

CREATE TABLE IF NOT EXISTS post_targets (
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    target_id UUID NOT NULL,
    PRIMARY KEY (post_id, target_id)
);


-- Index to support queries from a target -> posts
CREATE INDEX IF NOT EXISTS idx_post_targets_target_id
    ON post_targets(target_id);


-- =============================================================================
-- TRIGGERS
-- Keep updated_at current on updates
-- =============================================================================

DROP TRIGGER IF EXISTS update_posts_updated_at ON posts;

CREATE TRIGGER update_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMIT;
