-- GVAS LEAF Platform Schema
-- Database: gvas
-- Purpose: Identification -> Readiness -> Placement -> Retention -> Learning -> MEL

CREATE DATABASE IF NOT EXISTS gvas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gvas;




CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_name VARCHAR(60) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE local_governments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  state_name VARCHAR(80) NOT NULL DEFAULT 'Lagos',
  lga_name VARCHAR(120) NOT NULL UNIQUE,
  lga_code VARCHAR(25) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE communities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lga_id BIGINT UNSIGNED NOT NULL,
  community_name VARCHAR(150) NOT NULL,
  ward_name VARCHAR(120) NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_communities_lga FOREIGN KEY (lga_id) REFERENCES local_governments(id)
);

CREATE TABLE community_households (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  community_id BIGINT UNSIGNED NOT NULL,
  household_code VARCHAR(40) NOT NULL UNIQUE,
  head_of_household VARCHAR(150) NOT NULL,
  guardian_name VARCHAR(150) NULL,
  guardian_relationship VARCHAR(80) NULL,
  address_line VARCHAR(255) NULL,
  phone VARCHAR(30) NULL,
  number_of_children INT UNSIGNED NOT NULL DEFAULT 0,
  children_in_school INT UNSIGNED NOT NULL DEFAULT 0,
  children_oosc INT UNSIGNED NOT NULL DEFAULT 0,
  attendance_status ENUM('all_in_school', 'mixed', 'mostly_out_of_school', 'all_out_of_school') NOT NULL DEFAULT 'mixed',
  gps_latitude DECIMAL(10,7) NULL,
  gps_longitude DECIMAL(10,7) NULL,
  gps_note VARCHAR(255) NULL,
  capture_mode ENUM('offline_mobile', 'online', 'supervisor_backfill') NOT NULL DEFAULT 'offline_mobile',
  sync_status ENUM('pending_sync', 'synced', 'conflict_review') NOT NULL DEFAULT 'pending_sync',
  assigned_enumerator_user_id BIGINT UNSIGNED NULL,
  last_synced_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_households_community FOREIGN KEY (community_id) REFERENCES communities(id),
  CONSTRAINT fk_households_enumerator FOREIGN KEY (assigned_enumerator_user_id) REFERENCES users(id)
);

CREATE TABLE schools (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lga_id BIGINT UNSIGNED NOT NULL,
  school_name VARCHAR(180) NOT NULL,
  school_code VARCHAR(40) NOT NULL UNIQUE,
  school_level ENUM('primary', 'junior_secondary', 'senior_secondary', 'alp_centre') NOT NULL,
  ownership_type ENUM('public', 'community', 'partner') NOT NULL DEFAULT 'public',
  address_line VARCHAR(255) NULL,
  contact_phone VARCHAR(30) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_schools_lga FOREIGN KEY (lga_id) REFERENCES local_governments(id)
);

CREATE TABLE classrooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  class_name VARCHAR(120) NOT NULL,
  grade_band VARCHAR(60) NOT NULL,
  capacity INT UNSIGNED NULL,
  current_teacher_name VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_classrooms_school FOREIGN KEY (school_id) REFERENCES schools(id)
);

CREATE TABLE sbmc_committees (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  committee_name VARCHAR(150) NOT NULL,
  chairperson_name VARCHAR(150) NULL,
  contact_phone VARCHAR(30) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sbmc_school FOREIGN KEY (school_id) REFERENCES schools(id)
);

CREATE TABLE sbmc_members (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  committee_id BIGINT UNSIGNED NOT NULL,
  member_name VARCHAR(150) NOT NULL,
  member_role VARCHAR(100) NOT NULL,
  phone VARCHAR(30) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sbmc_member_committee FOREIGN KEY (committee_id) REFERENCES sbmc_committees(id)
);

CREATE TABLE leaf_programmes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  programme_name VARCHAR(180) NOT NULL UNIQUE,
  lot_name VARCHAR(50) NOT NULL,
  donor_name VARCHAR(160) NULL,
  starts_on DATE NULL,
  ends_on DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE programme_cycles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  programme_id BIGINT UNSIGNED NOT NULL,
  cycle_name VARCHAR(120) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  reporting_frequency ENUM('monthly', 'quarterly', 'annual') NOT NULL DEFAULT 'monthly',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cycle_programme FOREIGN KEY (programme_id) REFERENCES leaf_programmes(id)
);

CREATE TABLE outreach_survey_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cycle_id BIGINT UNSIGNED NOT NULL,
  batch_name VARCHAR(120) NOT NULL,
  lga_id BIGINT UNSIGNED NOT NULL,
  batch_status ENUM('planned', 'active', 'completed', 'validated') NOT NULL DEFAULT 'planned',
  starts_on DATE NULL,
  ends_on DATE NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_batch_cycle FOREIGN KEY (cycle_id) REFERENCES programme_cycles(id),
  CONSTRAINT fk_batch_lga FOREIGN KEY (lga_id) REFERENCES local_governments(id),
  CONSTRAINT fk_batch_user FOREIGN KEY (created_by_user_id) REFERENCES users(id)
);

CREATE TABLE oosc_children (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  household_id BIGINT UNSIGNED NOT NULL,
  batch_id BIGINT UNSIGNED NOT NULL,
  child_code VARCHAR(40) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  middle_name VARCHAR(100) NULL,
  gender ENUM('female', 'male', 'other') NOT NULL,
  date_of_birth DATE NULL,
  age_years INT UNSIGNED NULL,
  age_verification_method ENUM('birth_certificate', 'nin_slip', 'school_record', 'guardian_declaration') NOT NULL DEFAULT 'guardian_declaration',
  birth_certificate_number VARCHAR(80) NULL,
  nin_number VARCHAR(40) NULL,
  primary_language VARCHAR(80) NULL,
  language_support_needed TINYINT(1) NOT NULL DEFAULT 0,
  disability_status ENUM('none', 'physical', 'visual', 'hearing', 'cognitive', 'multiple') NOT NULL DEFAULT 'none',
  schooling_status ENUM('never_enrolled', 'dropped_out_recently', 'dropped_out_long_term', 'in_school_but_at_risk') NOT NULL,
  previous_school_name VARCHAR(180) NULL,
  previous_class_level VARCHAR(80) NULL,
  literacy_level ENUM('non_reader', 'letter_level', 'emerging', 'grade_level') NULL,
  numeracy_level ENUM('beginner', 'number_sense', 'below_grade', 'grade_level') NULL,
  child_photo_path VARCHAR(255) NULL,
  duplicate_flag TINYINT(1) NOT NULL DEFAULT 0,
  duplicate_confidence_score DECIMAL(5,2) NULL,
  risk_classification ENUM('low', 'moderate', 'high', 'critical') NOT NULL DEFAULT 'moderate',
  tier_level ENUM('tier_1', 'tier_2', 'special_case') NOT NULL,
  profile_status ENUM('identified', 'verified', 'readiness', 'placed', 'retained', 'inactive') NOT NULL DEFAULT 'identified',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_child_household FOREIGN KEY (household_id) REFERENCES community_households(id),
  CONSTRAINT fk_child_batch FOREIGN KEY (batch_id) REFERENCES outreach_survey_batches(id)
);

CREATE TABLE child_guardians (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  guardian_name VARCHAR(150) NOT NULL,
  relationship_type VARCHAR(60) NOT NULL,
  phone VARCHAR(30) NULL,
  alternate_phone VARCHAR(30) NULL,
  occupation VARCHAR(120) NULL,
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 1,
  consent_status ENUM('pending', 'granted', 'declined') NOT NULL DEFAULT 'pending',
  consent_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guardian_child FOREIGN KEY (child_id) REFERENCES oosc_children(id)
);

CREATE TABLE child_barriers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  barrier_category ENUM('economic', 'mobility', 'language', 'gender', 'disability', 'safety', 'documentation', 'caregiving') NOT NULL,
  severity_level ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  barrier_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_barrier_child FOREIGN KEY (child_id) REFERENCES oosc_children(id)
);

CREATE TABLE referrals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  referral_type ENUM('health', 'social_support', 'documentation', 'psychosocial', 'transport', 'protection') NOT NULL,
  referred_to VARCHAR(180) NOT NULL,
  referred_by_user_id BIGINT UNSIGNED NULL,
  status ENUM('open', 'in_progress', 'closed') NOT NULL DEFAULT 'open',
  due_on DATE NULL,
  referral_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_referral_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_referral_user FOREIGN KEY (referred_by_user_id) REFERENCES users(id)
);

CREATE TABLE community_surveys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  household_id BIGINT UNSIGNED NOT NULL,
  child_id BIGINT UNSIGNED NULL,
  surveyed_by_user_id BIGINT UNSIGNED NULL,
  survey_date DATE NOT NULL,
  survey_channel ENUM('household', 'community_centre', 'school_outreach') NOT NULL DEFAULT 'household',
  offline_capture_ref VARCHAR(80) NULL,
  schooling_history TEXT NULL,
  child_interest_level ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_survey_household FOREIGN KEY (household_id) REFERENCES community_households(id),
  CONSTRAINT fk_survey_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_survey_user FOREIGN KEY (surveyed_by_user_id) REFERENCES users(id)
);

CREATE TABLE survey_followups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  survey_id BIGINT UNSIGNED NOT NULL,
  followup_date DATE NOT NULL,
  followup_status ENUM('scheduled', 'completed', 'missed') NOT NULL DEFAULT 'scheduled',
  followup_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_followup_survey FOREIGN KEY (survey_id) REFERENCES community_surveys(id)
);

CREATE TABLE household_media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  household_id BIGINT UNSIGNED NOT NULL,
  media_type ENUM('photo', 'map_snapshot', 'document') NOT NULL DEFAULT 'photo',
  file_path VARCHAR(255) NOT NULL,
  uploaded_by_user_id BIGINT UNSIGNED NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_household_media_household FOREIGN KEY (household_id) REFERENCES community_households(id),
  CONSTRAINT fk_household_media_user FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
);

CREATE TABLE child_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  document_type ENUM('passport_photo', 'birth_certificate', 'nin_slip', 'school_record', 'guardian_declaration') NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  verification_status ENUM('pending', 'verified', 'rejected') NOT NULL DEFAULT 'pending',
  uploaded_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_child_documents_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_child_documents_user FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
);

CREATE TABLE deduplication_checks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  matched_child_id BIGINT UNSIGNED NULL,
  check_method ENUM('biodata', 'facial', 'biometric', 'national_registry') NOT NULL DEFAULT 'biodata',
  confidence_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  check_status ENUM('clear', 'possible_duplicate', 'confirmed_duplicate') NOT NULL DEFAULT 'clear',
  checked_by_user_id BIGINT UNSIGNED NULL,
  checked_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dedup_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_dedup_matched_child FOREIGN KEY (matched_child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_dedup_user FOREIGN KEY (checked_by_user_id) REFERENCES users(id)
);

CREATE TABLE child_vulnerability_assessments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL UNIQUE,
  poverty_flag TINYINT(1) NOT NULL DEFAULT 0,
  distance_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  child_labour_flag TINYINT(1) NOT NULL DEFAULT 0,
  disability_barrier_flag TINYINT(1) NOT NULL DEFAULT 0,
  security_concern_flag TINYINT(1) NOT NULL DEFAULT 0,
  early_marriage_risk_flag TINYINT(1) NOT NULL DEFAULT 0,
  parental_resistance_flag TINYINT(1) NOT NULL DEFAULT 0,
  migration_displacement_flag TINYINT(1) NOT NULL DEFAULT 0,
  total_score INT UNSIGNED NOT NULL DEFAULT 0,
  vulnerability_index ENUM('low', 'moderate', 'high', 'critical') NOT NULL DEFAULT 'low',
  priority_classification ENUM('priority_1', 'priority_2', 'priority_3') NOT NULL DEFAULT 'priority_3',
  referral_recommendation VARCHAR(255) NULL,
  assessor_user_id BIGINT UNSIGNED NULL,
  assessed_on DATE NULL,
  assessment_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_vulnerability_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_vulnerability_user FOREIGN KEY (assessor_user_id) REFERENCES users(id)
);

CREATE TABLE verification_cases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL UNIQUE,
  case_code VARCHAR(50) NOT NULL UNIQUE,
  current_stage ENUM('enumerator', 'supervisor', 'programme_lead', 'placement_team') NOT NULL DEFAULT 'enumerator',
  verification_status ENUM('enumerator_submitted', 'barrier_scored', 'supervisor_approved', 'programme_review', 'placement_ready', 'duplicate_flagged', 'rejected_for_rework') NOT NULL DEFAULT 'enumerator_submitted',
  assigned_to_user_id BIGINT UNSIGNED NULL,
  next_actor_role VARCHAR(80) NULL,
  document_verification_status ENUM('missing', 'pending', 'partial', 'verified') NOT NULL DEFAULT 'missing',
  partner_referral_name VARCHAR(180) NULL,
  case_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_verification_case_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_verification_case_user FOREIGN KEY (assigned_to_user_id) REFERENCES users(id)
);

CREATE TABLE workflow_transitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  verification_case_id BIGINT UNSIGNED NOT NULL,
  acted_by_user_id BIGINT UNSIGNED NULL,
  from_stage VARCHAR(80) NULL,
  to_stage VARCHAR(80) NOT NULL,
  from_status VARCHAR(80) NULL,
  to_status VARCHAR(80) NOT NULL,
  action_note TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_workflow_transition_case FOREIGN KEY (verification_case_id) REFERENCES verification_cases(id),
  CONSTRAINT fk_workflow_transition_user FOREIGN KEY (acted_by_user_id) REFERENCES users(id)
);

CREATE TABLE readiness_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL UNIQUE,
  readiness_status ENUM('not_started', 'in_progress', 'ready', 'needs_support') NOT NULL DEFAULT 'not_started',
  literacy_baseline DECIMAL(5,2) NULL,
  numeracy_baseline DECIMAL(5,2) NULL,
  socio_emotional_note TEXT NULL,
  assessor_user_id BIGINT UNSIGNED NULL,
  assessed_on DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_readiness_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_readiness_user FOREIGN KEY (assessor_user_id) REFERENCES users(id)
);

CREATE TABLE alp_cohorts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cycle_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NULL,
  cohort_name VARCHAR(120) NOT NULL,
  focus_area ENUM('literacy', 'numeracy', 'blended_foundation') NOT NULL DEFAULT 'blended_foundation',
  facilitator_name VARCHAR(150) NULL,
  starts_on DATE NULL,
  ends_on DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_alp_cycle FOREIGN KEY (cycle_id) REFERENCES programme_cycles(id),
  CONSTRAINT fk_alp_school FOREIGN KEY (school_id) REFERENCES schools(id)
);

CREATE TABLE alp_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cohort_id BIGINT UNSIGNED NOT NULL,
  session_title VARCHAR(150) NOT NULL,
  session_date DATE NOT NULL,
  learning_focus VARCHAR(150) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_session_cohort FOREIGN KEY (cohort_id) REFERENCES alp_cohorts(id)
);

CREATE TABLE school_placements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL UNIQUE,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NULL,
  placement_status ENUM('recommended', 'approved', 'enrolled', 'deferred') NOT NULL DEFAULT 'recommended',
  placement_reason TEXT NULL,
  placed_on DATE NULL,
  placed_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_placement_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_placement_school FOREIGN KEY (school_id) REFERENCES schools(id),
  CONSTRAINT fk_placement_classroom FOREIGN KEY (classroom_id) REFERENCES classrooms(id),
  CONSTRAINT fk_placement_user FOREIGN KEY (placed_by_user_id) REFERENCES users(id)
);

CREATE TABLE child_enrolments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  placement_id BIGINT UNSIGNED NOT NULL UNIQUE,
  admission_number VARCHAR(60) NULL,
  enrolment_date DATE NOT NULL,
  enrolment_status ENUM('active', 'pending_documents', 'paused', 'withdrawn') NOT NULL DEFAULT 'active',
  evidence_document VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_enrolment_placement FOREIGN KEY (placement_id) REFERENCES school_placements(id)
);

CREATE TABLE daily_attendance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  enrolment_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  attendance_status ENUM('present', 'late', 'absent', 'excused') NOT NULL DEFAULT 'present',
  recorded_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_enrolment_attendance (enrolment_id, attendance_date),
  CONSTRAINT fk_attendance_enrolment FOREIGN KEY (enrolment_id) REFERENCES child_enrolments(id),
  CONSTRAINT fk_attendance_user FOREIGN KEY (recorded_by_user_id) REFERENCES users(id)
);

CREATE TABLE retention_alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  enrolment_id BIGINT UNSIGNED NOT NULL,
  alert_level ENUM('watch', 'moderate', 'critical') NOT NULL DEFAULT 'watch',
  alert_reason VARCHAR(180) NOT NULL,
  risk_score DECIMAL(5,2) NOT NULL,
  alert_status ENUM('open', 'in_progress', 'resolved') NOT NULL DEFAULT 'open',
  opened_on DATE NOT NULL,
  resolved_on DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_alert_enrolment FOREIGN KEY (enrolment_id) REFERENCES child_enrolments(id)
);

CREATE TABLE alert_interventions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  alert_id BIGINT UNSIGNED NOT NULL,
  intervention_type ENUM('caregiver_visit', 'school_meeting', 'transport_support', 'psychosocial_support', 'financial_support', 'referral') NOT NULL,
  intervention_date DATE NOT NULL,
  outcome_note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_intervention_alert FOREIGN KEY (alert_id) REFERENCES retention_alerts(id),
  CONSTRAINT fk_intervention_user FOREIGN KEY (created_by_user_id) REFERENCES users(id)
);

CREATE TABLE learning_assessments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cycle_id BIGINT UNSIGNED NOT NULL,
  assessment_name VARCHAR(160) NOT NULL,
  assessment_stage ENUM('baseline', 'progress', 'endline') NOT NULL,
  subject_focus ENUM('literacy', 'numeracy', 'blended') NOT NULL DEFAULT 'blended',
  assessment_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_learning_assessment_cycle FOREIGN KEY (cycle_id) REFERENCES programme_cycles(id)
);

CREATE TABLE assessment_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assessment_id BIGINT UNSIGNED NOT NULL,
  competency_domain VARCHAR(120) NOT NULL,
  item_text TEXT NOT NULL,
  max_score DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  difficulty_level ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'medium',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assessment_item_assessment FOREIGN KEY (assessment_id) REFERENCES learning_assessments(id)
);

CREATE TABLE child_assessment_scores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  assessment_id BIGINT UNSIGNED NOT NULL,
  literacy_score DECIMAL(6,2) NULL,
  numeracy_score DECIMAL(6,2) NULL,
  overall_score DECIMAL(6,2) NULL,
  assessor_user_id BIGINT UNSIGNED NULL,
  recorded_on DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_child_assessment (child_id, assessment_id),
  CONSTRAINT fk_child_score_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_child_score_assessment FOREIGN KEY (assessment_id) REFERENCES learning_assessments(id),
  CONSTRAINT fk_child_score_user FOREIGN KEY (assessor_user_id) REFERENCES users(id)
);

CREATE TABLE teacher_support_visits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  visit_date DATE NOT NULL,
  support_focus VARCHAR(180) NOT NULL,
  visit_note TEXT NULL,
  conducted_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_teacher_support_school FOREIGN KEY (school_id) REFERENCES schools(id),
  CONSTRAINT fk_teacher_support_user FOREIGN KEY (conducted_by_user_id) REFERENCES users(id)
);

CREATE TABLE psychosocial_case_notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id BIGINT UNSIGNED NOT NULL,
  note_date DATE NOT NULL,
  case_status ENUM('open', 'monitoring', 'closed') NOT NULL DEFAULT 'open',
  note_text TEXT NOT NULL,
  entered_by_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_case_note_child FOREIGN KEY (child_id) REFERENCES oosc_children(id),
  CONSTRAINT fk_case_note_user FOREIGN KEY (entered_by_user_id) REFERENCES users(id)
);

CREATE TABLE mel_indicators (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  programme_id BIGINT UNSIGNED NOT NULL,
  indicator_code VARCHAR(40) NOT NULL UNIQUE,
  indicator_name VARCHAR(180) NOT NULL,
  outcome_area ENUM('placement', 'retention_year_1', 'retention_year_2', 'learning_gains', 'data_quality') NOT NULL,
  measurement_tool VARCHAR(180) NOT NULL,
  reporting_frequency ENUM('continuous', 'monthly', 'quarterly', 'baseline_endline') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_indicator_programme FOREIGN KEY (programme_id) REFERENCES leaf_programmes(id)
);

CREATE TABLE mel_indicator_values (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  indicator_id BIGINT UNSIGNED NOT NULL,
  cycle_id BIGINT UNSIGNED NULL,
  value_date DATE NOT NULL,
  value_number DECIMAL(12,2) NOT NULL,
  numerator DECIMAL(12,2) NULL,
  denominator DECIMAL(12,2) NULL,
  data_quality_status ENUM('draft', 'validated', 'published') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_indicator_value_indicator FOREIGN KEY (indicator_id) REFERENCES mel_indicators(id),
  CONSTRAINT fk_indicator_value_cycle FOREIGN KEY (cycle_id) REFERENCES programme_cycles(id)
);

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  entity_name VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action_name VARCHAR(80) NOT NULL,
  action_note VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO roles (role_name, description) VALUES
('super_admin', 'Full platform administrator'),
('programme_lead', 'Programme delivery lead'),
('mel_lead', 'Monitoring and evaluation lead'),
('field_officer', 'Community survey and follow-up officer'),
('school_focal_person', 'School-based implementation focal person'),
('guardian', 'Parent or guardian account'),
('learner', 'Learner account');

INSERT INTO local_governments (state_name, lga_name, lga_code) VALUES
('Lagos', 'Alimosho', 'LAG-ALI'),
('Lagos', 'Agege', 'LAG-AGE'),
('Lagos', 'Kosofe', 'LAG-KOS'),
('Lagos', 'Eti-Osa', 'LAG-ETI'),
('Lagos', 'Ifako-Ijaiye', 'LAG-IFI'),
('Lagos', 'Ikorodu', 'LAG-IKO');

INSERT INTO leaf_programmes (programme_name, lot_name, donor_name, starts_on, ends_on) VALUES
('Lagos Education Access Fund', 'Lot 2', 'LEAF', '2026-04-01', '2030-03-31');

INSERT INTO programme_cycles (programme_id, cycle_name, starts_on, ends_on, reporting_frequency) VALUES
(1, '2026 Inception Cycle', '2026-04-01', '2027-03-31', 'monthly');

INSERT INTO communities (lga_id, community_name, ward_name, latitude, longitude) VALUES
(4, 'Ilasan Waterside', 'Ilasan Ward', 6.4373810, 3.4841280),
(2, 'Dopemu Extension', 'Dopemu Ward', 6.6173410, 3.3186400),
(1, 'Ijegun Alasia', 'Alasia Ward', 6.5602140, 3.2368210);

INSERT INTO outreach_survey_batches (cycle_id, batch_name, lga_id, batch_status, starts_on, ends_on, created_by_user_id) VALUES
(1, 'Phase 1 Enumeration Batch', 4, 'active', '2026-05-01', '2026-06-30', 1);

INSERT INTO community_households (
  community_id, household_code, head_of_household, guardian_name, guardian_relationship, address_line, phone,
  number_of_children, children_in_school, children_oosc, attendance_status, gps_latitude, gps_longitude, gps_note,
  capture_mode, sync_status, assigned_enumerator_user_id, last_synced_at
) VALUES
(1, 'HH-ETI-2026-0001', 'Adewale Banjoko', 'Tosin Banjoko', 'Mother', '9 Ajibade Street, Ilasan', '08030124567', 4, 2, 2, 'mixed', 6.4373810, 3.4841280, 'Cluster B shoreline homes', 'offline_mobile', 'synced', 2, '2026-05-18 09:18:00'),
(2, 'HH-AGE-2026-0002', 'Rashidat Ganiu', 'Rashidat Ganiu', 'Guardian', '14 Kilo Lane, Dopemu', '08071230981', 3, 1, 2, 'mostly_out_of_school', 6.6173410, 3.3186400, 'Near canal footbridge', 'online', 'synced', 3, '2026-05-18 13:04:00'),
(3, 'HH-ALI-2026-0003', 'Ngozi Okafor', 'Ngozi Okafor', 'Aunt', '22 Unity Close, Alasia', '08144033219', 5, 3, 2, 'mixed', 6.5602140, 3.2368210, 'Shared compound behind the market', 'offline_mobile', 'pending_sync', 4, NULL);

INSERT INTO household_media (household_id, media_type, file_path, uploaded_by_user_id) VALUES
(1, 'photo', 'sample-household-1.jpg', 2),
(3, 'photo', 'sample-household-2.jpg', 4);

INSERT INTO oosc_children (
  household_id, batch_id, child_code, first_name, last_name, middle_name, gender, date_of_birth, age_years,
  age_verification_method, primary_language, disability_status, schooling_status, previous_school_name, previous_class_level,
  literacy_level, numeracy_level, child_photo_path, duplicate_flag, duplicate_confidence_score, risk_classification,
  tier_level, profile_status
) VALUES
(1, 1, 'CH-ETI-2026-0001', 'Temiloluwa', 'Banjoko', NULL, 'female', '2015-01-14', 11, 'birth_certificate', 'Yoruba', 'none', 'dropped_out_recently', 'Ilasan Primary School', 'Primary 4', 'emerging', 'below_grade', NULL, 0, 0.00, 'high', 'tier_1', 'identified'),
(2, 1, 'CH-AGE-2026-0002', 'Sodiq', 'Ganiu', NULL, 'male', '2013-03-08', 13, 'nin_slip', 'Pidgin', 'none', 'never_enrolled', NULL, NULL, 'non_reader', 'beginner', NULL, 0, 0.00, 'critical', 'tier_2', 'identified'),
(3, 1, 'CH-ALI-2026-0003', 'Chisom', 'Okafor', NULL, 'female', '2017-07-22', 9, 'guardian_declaration', 'Igbo', 'hearing', 'dropped_out_long_term', 'Community Basic Centre', 'Basic 1', 'letter_level', 'beginner', NULL, 1, 92.50, 'critical', 'tier_2', 'verified');

INSERT INTO child_guardians (
  child_id, guardian_name, relationship_type, phone, occupation, is_primary_contact, consent_status, consent_note
) VALUES
(1, 'Tosin Banjoko', 'Mother', '08030124567', 'Trader', 1, 'pending', NULL),
(2, 'Rashidat Ganiu', 'Guardian', '08071230981', 'Petty trader', 1, 'pending', NULL),
(3, 'Ngozi Okafor', 'Aunt', '08144033219', 'Seamstress', 1, 'pending', NULL);

INSERT INTO community_surveys (
  household_id, child_id, surveyed_by_user_id, survey_date, survey_channel, offline_capture_ref, schooling_history, child_interest_level
) VALUES
(1, 1, 2, '2026-05-18', 'household', 'SYNC-ETI-001', 'Dropped out after Primary 4 due to relocation and unpaid levies.', 'medium'),
(2, 2, 3, '2026-05-18', 'household', NULL, 'No formal school history; supports household trading.', 'medium'),
(3, 3, 4, '2026-05-19', 'household', 'SYNC-ALI-003', 'Was enrolled for one year before migration-related interruption.', 'medium');

INSERT INTO child_documents (child_id, document_type, file_path, verification_status, uploaded_by_user_id) VALUES
(1, 'birth_certificate', 'temiloluwa_birth_certificate.pdf', 'verified', 2),
(2, 'nin_slip', 'sodiq_nin_slip.pdf', 'verified', 3);

INSERT INTO deduplication_checks (child_id, matched_child_id, check_method, confidence_score, check_status, checked_by_user_id, checked_at) VALUES
(3, 1, 'biodata', 92.50, 'possible_duplicate', 4, '2026-05-19 09:13:00');

INSERT INTO child_vulnerability_assessments (
  child_id, poverty_flag, distance_score, child_labour_flag, disability_barrier_flag, security_concern_flag,
  early_marriage_risk_flag, parental_resistance_flag, migration_displacement_flag, total_score, vulnerability_index,
  priority_classification, referral_recommendation, assessor_user_id, assessed_on, assessment_note
) VALUES
(1, 1, 3, 0, 0, 0, 0, 1, 1, 13, 'high', 'priority_1', 'Household economic support and enrolment mediation', 5, '2026-05-18', 'Family relocation disrupted schooling and there is uncertainty about school costs.'),
(2, 1, 2, 1, 0, 0, 0, 1, 0, 15, 'critical', 'priority_1', 'Social protection referral and caregiver counselling', 7, '2026-05-18', 'Household relies on the child for petty trading support on market days.'),
(3, 1, 4, 0, 1, 0, 0, 0, 1, 18, 'critical', 'priority_1', 'Disability support, assistive screening, and school matching', 6, '2026-05-19', 'Requires hearing support and school placement close to home.');

INSERT INTO verification_cases (
  child_id, case_code, current_stage, verification_status, assigned_to_user_id, next_actor_role, document_verification_status,
  partner_referral_name, case_note
) VALUES
(1, 'CASE-2026-001', 'supervisor', 'enumerator_submitted', 5, 'Supervisor', 'partial', 'LGA Social Welfare Desk', 'Awaiting supervisor sign-off on migration evidence.'),
(2, 'CASE-2026-002', 'programme_lead', 'programme_review', 1, 'Placement Team', 'verified', 'Child Protection Network', 'Recommend fast-track to placement after caregiver counselling.'),
(3, 'CASE-2026-003', 'supervisor', 'duplicate_flagged', 6, 'Programme Lead', 'pending', 'Inclusive Education Support Partner', 'Duplicate check and hearing support verification required.');

INSERT INTO workflow_transitions (
  verification_case_id, acted_by_user_id, from_stage, to_stage, from_status, to_status, action_note
) VALUES
(1, 2, 'enumerator', 'supervisor', 'draft', 'enumerator_submitted', 'Enumerator submission completed'),
(2, 7, 'supervisor', 'programme_lead', 'supervisor_approved', 'programme_review', 'Supervisor approved and escalated to programme lead'),
(3, 4, 'enumerator', 'supervisor', 'draft', 'duplicate_flagged', 'Duplicate flag raised during child profiling');

INSERT INTO mel_indicators (programme_id, indicator_code, indicator_name, outcome_area, measurement_tool, reporting_frequency) VALUES
(1, 'LEAF-PLC-001', 'Number of OOSC enrolled', 'placement', 'Digital platform', 'continuous'),
(1, 'LEAF-RET-001', 'Percentage retained after 1 year', 'retention_year_1', 'Attendance tracking', 'monthly'),
(1, 'LEAF-RET-002', 'Percentage retained after 2 years', 'retention_year_2', 'Attendance tracking', 'quarterly'),
(1, 'LEAF-LRN-001', 'Literacy and numeracy improvement', 'learning_gains', 'Assessment toolkit', 'baseline_endline'),
(1, 'LEAF-DQ-001', 'Data completeness and accuracy', 'data_quality', 'MEL dashboard', 'continuous');

INSERT INTO audit_logs (user_id, entity_name, entity_id, action_name, action_note) VALUES
(2, 'verification_case', 1, 'created', 'Enumerator submission completed'),
(7, 'verification_case', 2, 'workflow_updated', 'Supervisor approved and escalated to programme lead'),
(4, 'verification_case', 3, 'created', 'Duplicate flag raised during child profiling');
