-- =============================================================================
-- Migration 024: Add 'apartment' to posts.target_type
-- Purpose: Allow posts to target apartments in addition to existing types
-- =============================================================================

BEGIN;

-- Drop any existing CHECK constraint(s) on posts.target_type that match the old definition
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT conname
        FROM pg_constraint
        WHERE conrelid = 'posts'::regclass
          AND contype = 'c'
          AND pg_get_constraintdef(oid) LIKE '%target_type IN (%'
    LOOP
        EXECUTE format('ALTER TABLE posts DROP CONSTRAINT IF EXISTS %I', r.conname);
    END LOOP;
END
$$;

-- Add new named CHECK constraint including 'apartment'
-- Ensure any existing named constraint is removed first to make migration idempotent
ALTER TABLE posts DROP CONSTRAINT IF EXISTS posts_target_type_check;

ALTER TABLE posts
    ADD CONSTRAINT posts_target_type_check CHECK (target_type IN ('all', 'building', 'apartment', 'floor', 'room'));

COMMIT;
