-- =====================================================================
-- Migration 006: Comments Table and Photo Support
-- =====================================================================


-- ---------------------------------------------------------------------
-- Block 1: Creates Table "comments"
-- Stores Comments on Contributions. Comments is linked to
-- Contributions and identified by browser_id.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS comments (
    comment_id      SERIAL PRIMARY KEY,
    contribution_id INTEGER NOT NULL REFERENCES contributions(contribution_id) ON DELETE CASCADE,
    author_name     VARCHAR(100) NOT NULL,
    browser_id      VARCHAR(36) DEFAULT NULL,
    content         TEXT NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);


-- ---------------------------------------------------------------------
-- Block 2: Indexes for fast Comment Queries
-- ---------------------------------------------------------------------
CREATE INDEX idx_comments_contribution ON comments(contribution_id);
CREATE INDEX idx_comments_browser ON comments(browser_id);


-- ---------------------------------------------------------------------
-- Block 3: Adds Photo Path Column to Contributions
-- Stores relative Path to uploaded Photo File.
-- ---------------------------------------------------------------------
ALTER TABLE contributions
    ADD COLUMN photo_path VARCHAR(255) DEFAULT NULL;

COMMENT ON COLUMN contributions.photo_path IS 'Relative Path to uploaded Photo. NULL = no Photo.';