Settings

-- Barbados Voters App - Database Setup Script

-- Table 1: List Versions (tracks each upload)
CREATE TABLE list_versions (
  id SERIAL PRIMARY KEY,
  uploaded_at TIMESTAMP DEFAULT NOW(),
  filename TEXT NOT NULL,
  total_voters INTEGER NOT NULL DEFAULT 0,
  additions INTEGER NOT NULL DEFAULT 0,
  deletions INTEGER NOT NULL DEFAULT 0
);

-- Table 2: Voters (main voter registry)
CREATE TABLE voters (
  id SERIAL PRIMARY KEY,
  cc TEXT,
  nrn TEXT,
  first_name TEXT,
  middle_names TEXT,
  last_name TEXT,
  gender TEXT,
  dob TEXT,
  house_number TEXT,
  house_name TEXT,
  address1 TEXT,
  address2 TEXT,
  parish TEXT,
  constituency TEXT,
  pd TEXT,
  composite_key TEXT UNIQUE NOT NULL,
  list_version_id INTEGER REFERENCES list_versions(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for voters table
CREATE INDEX idx_voters_composite_key ON voters(composite_key);
CREATE INDEX idx_voters_constituency ON voters(constituency);
CREATE INDEX idx_voters_parish ON voters(parish);
CREATE INDEX idx_voters_pd ON voters(pd);
CREATE INDEX idx_voters_nrn ON voters(nrn);
CREATE INDEX idx_voters_name ON voters(last_name, first_name);

-- Table 3: Voter Changes (tracks additions/removals per version)
CREATE TABLE voter_changes (
  id SERIAL PRIMARY KEY,
  version_id INTEGER REFERENCES list_versions(id) ON DELETE CASCADE,
  change_type TEXT CHECK (change_type IN ('added', 'removed')),
  composite_key TEXT NOT NULL,
  voter_data JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Table 4: Canvassing (canvass records)
CREATE TABLE canvassing (
  id SERIAL PRIMARY KEY,
  voter_id INTEGER REFERENCES voters(id) ON DELETE SET NULL,
  voter_composite_key TEXT,
  voter_name TEXT,
  voter_address TEXT,
  contact_status TEXT CHECK (contact_status IN ('not_visited', 'visited', 'no_answer')) DEFAULT 'not_visited',
  support_level TEXT CHECK (support_level IN ('yes', 'no', 'undecided')),
  canvassed_by TEXT,
  notes TEXT,
  concerns TEXT,
  requests TEXT,
  suggestions TEXT,
  canvassed_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);