plugin-voting/database/schema.sql

-- Voting Plugin Database Schema
-- Plugin naming convention: pg_vt_tableName
-- pg_ = plugin, vt_ = voting plugin

-- Campaign Types: 'board_election', 'decision', 'survey'
-- Campaign Status: 'draft', 'scheduled', 'active', 'completed', 'cancelled'
-- Result Sharing: 'detailed', 'anonymous', 'none'

-- Voting Campaigns table
CREATE TABLE IF NOT EXISTS pg_vt_campaigns (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  campaign_type VARCHAR(50) NOT NULL, -- board_election, decision, survey
  form_id UUID, -- Foreign key to pg_vt_forms table
  status VARCHAR(50) DEFAULT 'draft', -- draft, scheduled, active, completed, cancelled
  overall_sharing_results VARCHAR(50) DEFAULT 'detailed', -- detailed, anonymous, none
  public_access VARCHAR(50) DEFAULT 'restrict', -- restrict, allow
  
  -- Campaign dates
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  
  -- Recipients
  recipient_group_id UUID, -- Reference to a group or null for all owners
  recipient_type VARCHAR(50) DEFAULT 'all_owners', -- all_owners, board_members, authorized_voters, custom_group
  
  -- Delivery methods
  send_email BOOLEAN DEFAULT false,
  send_text BOOLEAN DEFAULT false,
  send_owner_app BOOLEAN DEFAULT false,
  
  -- Metadata
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for campaigns
CREATE INDEX IF NOT EXISTS idx_pg_vt_campaigns_site_id ON pg_vt_campaigns(site_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_campaigns_type ON pg_vt_campaigns(campaign_type);
CREATE INDEX IF NOT EXISTS idx_pg_vt_campaigns_status ON pg_vt_campaigns(status);
CREATE INDEX IF NOT EXISTS idx_pg_vt_campaigns_dates ON pg_vt_campaigns(start_date, end_date);

-- Voting Forms table
-- Placeholder for form builder - will store form structure as JSON
CREATE TABLE IF NOT EXISTS pg_vt_forms (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  form_type VARCHAR(50) NOT NULL, -- board_election, decision, survey
  
  -- Form structure stored as JSON
  -- This is a placeholder until we build/embed a form builder
  -- Example structure:
  -- {
  --   "questions": [
  --     {
  --       "id": "q1",
  --       "type": "multiple_choice",
  --       "label": "Choose 4 Board Members *",
  --       "required": true,
  --       "multiple": true,
  --       "max_selections": 4,
  --       "options": [
  --         {"id": "opt1", "value": "bod 1"},
  --         {"id": "opt2", "value": "bod 2"}
  --       ]
  --     }
  --   ]
  -- }
  form_structure JSONB DEFAULT '{"questions": []}',
  
  -- Metadata
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_forms_site_id ON pg_vt_forms(site_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_forms_type ON pg_vt_forms(form_type);

-- Campaign Responses table (main response tracking)
CREATE TABLE IF NOT EXISTS pg_vt_responses (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  campaign_id UUID REFERENCES pg_vt_campaigns(id) ON DELETE CASCADE,
  person_id UUID REFERENCES people(id),
  user_id UUID REFERENCES users(id),
  
  -- Participation tracking
  answered BOOLEAN DEFAULT false,
  answered_date TIMESTAMP WITH TIME ZONE,
  last_modified_date TIMESTAMP WITH TIME ZONE,
  
  -- Communication tracking
  email_sent BOOLEAN DEFAULT false,
  text_sent BOOLEAN DEFAULT false,
  
  -- Metadata
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  
  UNIQUE(campaign_id, person_id)
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_responses_campaign_id ON pg_vt_responses(campaign_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_responses_person_id ON pg_vt_responses(person_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_responses_answered ON pg_vt_responses(answered);

-- Response Answers table (detailed answer data)
CREATE TABLE IF NOT EXISTS pg_vt_response_answers (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  response_id UUID REFERENCES pg_vt_responses(id) ON DELETE CASCADE,
  question_id VARCHAR(255) NOT NULL, -- Reference to question in form_structure
  answer_value TEXT, -- For text, numeric, date answers
  answer_options JSONB, -- For multiple choice answers: ["bod 1", "bod 3", "bod 5", "bod 4"]
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_response_answers_response_id ON pg_vt_response_answers(response_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_response_answers_question_id ON pg_vt_response_answers(question_id);

-- Change Logs table (audit trail for response changes)
CREATE TABLE IF NOT EXISTS pg_vt_response_change_logs (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  response_id UUID REFERENCES pg_vt_responses(id) ON DELETE CASCADE,
  campaign_id UUID REFERENCES pg_vt_campaigns(id) ON DELETE CASCADE,
  person_id UUID REFERENCES people(id),
  person_name VARCHAR(255),
  unit VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  answered_date TIMESTAMP WITH TIME ZONE,
  answered_change_date TIMESTAMP WITH TIME ZONE,
  who_changed VARCHAR(255), -- Person who made the change
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_change_logs_campaign_id ON pg_vt_response_change_logs(campaign_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_change_logs_response_id ON pg_vt_response_change_logs(response_id);

-- Groups table (for custom recipient groups)
CREATE TABLE IF NOT EXISTS pg_vt_groups (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  site_id UUID REFERENCES sites(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_groups_site_id ON pg_vt_groups(site_id);

-- Group Members junction table
CREATE TABLE IF NOT EXISTS pg_vt_group_members (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  group_id UUID REFERENCES pg_vt_groups(id) ON DELETE CASCADE,
  person_id UUID REFERENCES people(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(group_id, person_id)
);

CREATE INDEX IF NOT EXISTS idx_pg_vt_group_members_group_id ON pg_vt_group_members(group_id);
CREATE INDEX IF NOT EXISTS idx_pg_vt_group_members_person_id ON pg_vt_group_members(person_id);