-- Primed Volunteer Register — D1 Database Schema (Stage 2)
-- Deploy: wrangler d1 execute primed-db --file=schema.sql --remote
-- Tables: volunteers, attendance, volunteer_feedback

-- Core volunteer record (imported from Sheets Master tab)
CREATE TABLE IF NOT EXISTS volunteers (
  id TEXT PRIMARY KEY,                          -- UUID
  volunteer_number INTEGER UNIQUE,              -- Sequential (#Voluntario)
  pipeline TEXT NOT NULL CHECK (pipeline IN (
    'IVHQ', 'EDB', 'United Planet', 'Storytellers', 'Directo', 'Otro'
  )),
  status TEXT DEFAULT 'confirmado' CHECK (status IN (
    'confirmado', 'activo', 'completado', 'cancelado', 'no_show'
  )),
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  gender TEXT CHECK (gender IN ('M', 'F', 'NB', 'Otro')),
  country_origin TEXT,
  country_residence TEXT,
  date_of_birth TEXT,                           -- ISO 8601
  duration_weeks INTEGER,
  start_date TEXT,                              -- ISO 8601
  end_date TEXT,                                -- ISO 8601
  program TEXT,                                 -- Program / Placement location
  volunteer_role TEXT CHECK (volunteer_role IN (
    'asistente_ensenanza', 'facilitador_eventos', 'creador_contenido',
    'intercambio_buddy', 'traduccion', 'otro'
  )),
  languages TEXT,
  skills TEXT,
  medical_history TEXT,
  dietary_restrictions TEXT,
  additional_notes TEXT,
  emergency_contact_1_name TEXT,
  emergency_contact_1_phone TEXT,
  emergency_contact_2_name TEXT,
  emergency_contact_2_phone TEXT,
  metadata TEXT,                                -- JSON for partner-specific extensions
                                                -- (passport, insurance, school info, etc.)
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

-- Per-session attendance records
CREATE TABLE IF NOT EXISTS attendance (
  id TEXT PRIMARY KEY,                          -- UUID
  volunteer_id TEXT NOT NULL,
  date TEXT NOT NULL,                           -- ISO 8601 date
  activity_type TEXT NOT NULL CHECK (activity_type IN (
    'clases', 'evento', 'jardin', 'turismo', 'admin', 'taller', 'otro'
  )),
  location TEXT CHECK (location IN (
    'manrique', 'comuna13', 'moravia', 'santo_domingo', 'virtual', 'otro'
  )),
  hours REAL DEFAULT 4.0 CHECK (hours > 0 AND hours <= 24),
  recorded_by TEXT,                             -- Email of person recording
  notes TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (volunteer_id) REFERENCES volunteers(id)
);

-- Exit feedback (maps to KPIs E-VO1, E-VO3)
CREATE TABLE IF NOT EXISTS volunteer_feedback (
  id TEXT PRIMARY KEY,                          -- UUID
  volunteer_id TEXT NOT NULL,
  satisfaction INTEGER CHECK (satisfaction BETWEEN 1 AND 5),
  skill_match INTEGER CHECK (skill_match IN (0, 1, 2)),
                                                -- 0=poor, 1=moderate, 2=good
  tasks_committed INTEGER CHECK (tasks_committed >= 0),
  tasks_completed INTEGER CHECK (tasks_completed >= 0),
  recommend_score INTEGER CHECK (recommend_score BETWEEN 1 AND 5),
  highlight TEXT,                               -- "Momento destacado"
  testimonial_consent INTEGER DEFAULT 0,        -- 0=no, 1=yes
  photo_url TEXT,                               -- R2 object URL if uploaded
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (volunteer_id) REFERENCES volunteers(id)
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_attendance_volunteer ON attendance(volunteer_id);
CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance(date);
CREATE INDEX IF NOT EXISTS idx_attendance_location ON attendance(location);
CREATE INDEX IF NOT EXISTS idx_volunteers_status ON volunteers(status);
CREATE INDEX IF NOT EXISTS idx_volunteers_pipeline ON volunteers(pipeline);
CREATE INDEX IF NOT EXISTS idx_feedback_volunteer ON volunteer_feedback(volunteer_id);

-- =================================================================
-- KPI Queries (for reference — used in portal and reporting)
-- =================================================================

-- A1: Volunteers Engaged (distinct volunteers with attendance in period)
-- SELECT COUNT(DISTINCT volunteer_id) FROM attendance
--   WHERE date BETWEEN ? AND ?;

-- A2: Total Volunteer Hours
-- SELECT SUM(hours) FROM attendance
--   WHERE date BETWEEN ? AND ?;

-- E-VO1: Task Completion Rate
-- SELECT AVG(tasks_completed * 100.0 / tasks_committed)
--   FROM volunteer_feedback WHERE tasks_committed > 0;

-- E-VO3: Skill Match (% Good)
-- SELECT COUNT(*) FILTER (WHERE skill_match = 2) * 100.0 / COUNT(*)
--   FROM volunteer_feedback;

-- Weekly hours by volunteer
-- SELECT v.first_name || ' ' || v.last_name AS name,
--        SUM(a.hours) AS total_hours, COUNT(a.id) AS sessions
--   FROM attendance a JOIN volunteers v ON a.volunteer_id = v.id
--   WHERE a.date BETWEEN ? AND ?
--   GROUP BY v.id ORDER BY total_hours DESC;

-- Hours by activity type
-- SELECT activity_type, SUM(hours) AS total,
--        COUNT(DISTINCT volunteer_id) AS participants
--   FROM attendance WHERE date BETWEEN ? AND ?
--   GROUP BY activity_type ORDER BY total DESC;

-- Hours by location
-- SELECT location, SUM(hours) AS total,
--        COUNT(DISTINCT volunteer_id) AS participants
--   FROM attendance WHERE date BETWEEN ? AND ?
--   GROUP BY location ORDER BY total DESC;
