likwid/backend/migrations/20260126130000_deliberation_system.sql

-- Deliberation System
-- Implements structured deliberative democracy: inform → discuss → decide

-- Deliberation phases
CREATE TYPE deliberation_phase AS ENUM ('drafting', 'informing', 'discussing', 'voting', 'concluded');

-- Add deliberation fields to proposals
ALTER TABLE proposals ADD COLUMN deliberation_phase deliberation_phase DEFAULT 'drafting';
ALTER TABLE proposals ADD COLUMN inform_starts_at TIMESTAMPTZ;
ALTER TABLE proposals ADD COLUMN inform_ends_at TIMESTAMPTZ;
ALTER TABLE proposals ADD COLUMN discuss_starts_at TIMESTAMPTZ;
ALTER TABLE proposals ADD COLUMN discuss_ends_at TIMESTAMPTZ;
ALTER TABLE proposals ADD COLUMN min_read_time_seconds INT DEFAULT 60;
ALTER TABLE proposals ADD COLUMN facilitator_id UUID REFERENCES users(id);

-- Resources for inform phase (documents, expert opinions, data)
CREATE TABLE proposal_resources (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    resource_type VARCHAR(50) NOT NULL DEFAULT 'document', -- document, video, link, expert_opinion
    content TEXT,
    url VARCHAR(500),
    author_name VARCHAR(255),
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES users(id)
);

-- Track who has read the resources (for "read before discuss" feature)
CREATE TABLE proposal_resource_reads (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    resource_id UUID NOT NULL REFERENCES proposal_resources(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    read_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    time_spent_seconds INT DEFAULT 0,
    UNIQUE(resource_id, user_id)
);

-- Discussion groups (small group discussions with facilitators)
CREATE TABLE discussion_groups (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    facilitator_id UUID REFERENCES users(id),
    max_members INT DEFAULT 10,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Discussion group members
CREATE TABLE discussion_group_members (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    group_id UUID NOT NULL REFERENCES discussion_groups(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(group_id, user_id)
);

-- Comment quality scoring (for constructive visibility)
ALTER TABLE comments ADD COLUMN IF NOT EXISTS quality_score INT DEFAULT 0;
ALTER TABLE comments ADD COLUMN IF NOT EXISTS is_constructive BOOLEAN DEFAULT TRUE;
ALTER TABLE comments ADD COLUMN IF NOT EXISTS upvotes INT DEFAULT 0;
ALTER TABLE comments ADD COLUMN IF NOT EXISTS downvotes INT DEFAULT 0;

-- Comment reactions (agree/disagree/insightful/off-topic)
CREATE TABLE comment_reactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    comment_id UUID NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reaction_type VARCHAR(20) NOT NULL, -- agree, disagree, insightful, off_topic, constructive
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(comment_id, user_id, reaction_type)
);

-- Proposal sentiment/position tracking (for agreement visualization)
CREATE TABLE proposal_positions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    position VARCHAR(20) NOT NULL, -- strongly_support, support, neutral, oppose, strongly_oppose
    reasoning TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id, user_id)
);

-- Add facilitator role to community_members
DO $$
BEGIN
    -- Check if role column needs updating (add facilitator if not exists in check constraint)
    -- For now we'll just allow any role value
    NULL;
END $$;

-- Indexes
CREATE INDEX idx_proposal_resources_proposal ON proposal_resources(proposal_id);
CREATE INDEX idx_proposal_resource_reads_resource ON proposal_resource_reads(resource_id);
CREATE INDEX idx_proposal_resource_reads_user ON proposal_resource_reads(user_id);
CREATE INDEX idx_discussion_groups_proposal ON discussion_groups(proposal_id);
CREATE INDEX idx_discussion_group_members_group ON discussion_group_members(group_id);
CREATE INDEX idx_comment_reactions_comment ON comment_reactions(comment_id);
CREATE INDEX idx_proposal_positions_proposal ON proposal_positions(proposal_id);
CREATE INDEX idx_proposals_deliberation_phase ON proposals(deliberation_phase);

-- Trigger for proposal_positions updated_at
CREATE TRIGGER proposal_positions_updated_at BEFORE UPDATE ON proposal_positions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();