-- =====================================================================
-- Migration 004: Creates News Table for Municipality Announcements
-- =====================================================================


-- ---------------------------------------------------------------------
-- Block 1: Creates Table "news"
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS news (
    news_id         SERIAL PRIMARY KEY,
    municipality_id INTEGER NOT NULL REFERENCES municipalities(municipality_id) ON DELETE CASCADE,
    title           VARCHAR(255) NOT NULL,
    content         TEXT NOT NULL,
    author_name     VARCHAR(100) NOT NULL DEFAULT 'Stadtverwaltung',
    published_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);


-- ---------------------------------------------------------------------
-- Block 2: Trigger Functions
-- ---------------------------------------------------------------------

-- Automatically Refresh updated_at on every UPDATE.
CREATE TRIGGER set_news_updated_at
    BEFORE UPDATE ON news
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();


-- ---------------------------------------------------------------------
-- Block 3 Indexes for fast Queries
-- ---------------------------------------------------------------------
CREATE INDEX idx_news_municipality ON news(municipality_id);


-- ---------------------------------------------------------------------
-- Block 4: Seed Data — Initial News Article
-- ---------------------------------------------------------------------
INSERT INTO news (municipality_id, title, content)
SELECT municipality_id, 'Mitmachkarte gestartet', 
       'Die Mitmachkarte als Bürgerbeteiligungsportal der Stadt Lohne (Oldenburg) wird nun freigeschaltet. Wir freuen uns auf Ihre Hinweise und Vorschläge!'
FROM municipalities WHERE slug = 'lohne';