-- VAZO Foundation Complete Database Schema
-- This script creates all tables for the website forms with proper indexing and automation

-- =====================================================
-- 1. VOLUNTEER APPLICATIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS volunteer_applications (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Personal Information
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    date_of_birth DATE,
    gender VARCHAR(20),
    
    -- Contact Details
    address TEXT,
    city VARCHAR(100),
    postal_code VARCHAR(10),
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    
    -- Volunteer Preferences
    volunteer_areas TEXT[] NOT NULL,
    availability TEXT[] NOT NULL,
    skills TEXT[],
    experience_level VARCHAR(50),
    previous_volunteer_experience TEXT,
    
    -- Background Information
    education_level VARCHAR(100),
    current_occupation VARCHAR(255),
    employer VARCHAR(255),
    languages_spoken TEXT[],
    
    -- Motivation and Commitment
    motivation TEXT NOT NULL,
    time_commitment VARCHAR(100),
    start_date DATE,
    special_requirements TEXT,
    
    -- References
    reference1_name VARCHAR(255),
    reference1_contact VARCHAR(255),
    reference1_relationship VARCHAR(100),
    reference2_name VARCHAR(255),
    reference2_contact VARCHAR(255),
    reference2_relationship VARCHAR(100),
    
    -- Consent and Agreements
    background_check_consent BOOLEAN DEFAULT FALSE,
    data_processing_consent BOOLEAN DEFAULT FALSE,
    terms_accepted BOOLEAN DEFAULT FALSE,
    
    -- Application Status
    status VARCHAR(20) DEFAULT 'pending',
    assigned_coordinator VARCHAR(255),
    interview_scheduled BOOLEAN DEFAULT FALSE,
    interview_date TIMESTAMP,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- 2. PARTNER INQUIRIES TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS partner_inquiries (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Organization Information
    organization_name VARCHAR(255) NOT NULL,
    organization_type VARCHAR(100) NOT NULL,
    registration_number VARCHAR(100),
    website VARCHAR(255),
    
    -- Contact Person Details
    contact_person VARCHAR(255) NOT NULL,
    position VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    
    -- Organization Details
    organization_size VARCHAR(50),
    annual_budget_range VARCHAR(100),
    geographic_focus TEXT[],
    target_beneficiaries TEXT[],
    
    -- Partnership Interest
    partnership_type VARCHAR(100) NOT NULL,
    collaboration_areas TEXT[] NOT NULL,
    proposed_contribution TEXT,
    expected_outcomes TEXT,
    
    -- Project Details
    project_description TEXT,
    project_timeline VARCHAR(100),
    project_budget_range VARCHAR(100),
    funding_available BOOLEAN DEFAULT FALSE,
    funding_amount VARCHAR(100),
    
    -- Organization Capacity
    staff_count INTEGER,
    volunteer_count INTEGER,
    previous_partnerships TEXT,
    relevant_experience TEXT,
    
    -- Alignment and Values
    mission_alignment TEXT,
    shared_values TEXT[],
    sustainability_approach TEXT,
    
    -- Additional Information
    additional_info TEXT,
    attachments_provided BOOLEAN DEFAULT FALSE,
    
    -- Follow-up Preferences
    preferred_contact_method VARCHAR(50),
    best_contact_time VARCHAR(100),
    meeting_preference VARCHAR(50),
    
    -- Inquiry Status
    status VARCHAR(20) DEFAULT 'pending',
    assigned_coordinator VARCHAR(255),
    priority_level VARCHAR(20) DEFAULT 'medium',
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- 3. PROGRAM APPLICATIONS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS program_applications (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Program Information
    program_type VARCHAR(100) NOT NULL,
    program_name VARCHAR(255),
    
    -- Applicant Information
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    date_of_birth DATE,
    gender VARCHAR(20),
    id_number VARCHAR(20),
    
    -- Contact Details
    address TEXT,
    city VARCHAR(100),
    postal_code VARCHAR(10),
    
    -- Emergency Contact
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    emergency_contact_relationship VARCHAR(100),
    
    -- Background Information
    education_level VARCHAR(100),
    current_status VARCHAR(100),
    employment_status VARCHAR(100),
    monthly_income_range VARCHAR(50),
    
    -- Program-Specific Information
    relevant_experience TEXT,
    skills_interests TEXT[],
    goals_expectations TEXT,
    challenges_barriers TEXT,
    
    -- Availability and Commitment
    availability TEXT[],
    time_commitment VARCHAR(100),
    transport_arrangements VARCHAR(100),
    special_requirements TEXT,
    
    -- Support System
    family_support BOOLEAN DEFAULT FALSE,
    support_system_description TEXT,
    previous_program_participation TEXT,
    
    -- Motivation and Goals
    motivation TEXT NOT NULL,
    career_goals TEXT,
    personal_goals TEXT,
    community_impact_goals TEXT,
    
    -- Consent and Agreements
    data_processing_consent BOOLEAN DEFAULT FALSE,
    program_terms_accepted BOOLEAN DEFAULT FALSE,
    media_consent BOOLEAN DEFAULT FALSE,
    
    -- Application Status
    status VARCHAR(20) DEFAULT 'pending',
    assigned_coordinator VARCHAR(255),
    assessment_score INTEGER,
    interview_required BOOLEAN DEFAULT FALSE,
    interview_date TIMESTAMP,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- 4. PARENT SUPPORT REQUESTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS parent_support_requests (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Contact Information
    parent_name VARCHAR(255) NOT NULL,
    contact_phone VARCHAR(20) NOT NULL,
    contact_email VARCHAR(255),
    preferred_contact VARCHAR(50) NOT NULL,
    
    -- Family Information
    family_size VARCHAR(10) NOT NULL,
    children_ages TEXT NOT NULL,
    
    -- Support Details
    primary_challenges TEXT[] NOT NULL,
    support_needed TEXT[] NOT NULL,
    urgency_level VARCHAR(20) NOT NULL,
    previous_support VARCHAR(50),
    
    -- Availability and Preferences
    available_times TEXT[],
    language_preference VARCHAR(50),
    transport_needs VARCHAR(50),
    
    -- Additional Information
    additional_info TEXT,
    
    -- Consent
    consent_to_contact BOOLEAN NOT NULL DEFAULT FALSE,
    consent_to_visit BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Metadata
    status VARCHAR(20) DEFAULT 'pending',
    assigned_coordinator VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- 5. INCIDENT REPORTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS incident_reports (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Incident Details
    incident_type VARCHAR(100) NOT NULL,
    incident_description TEXT NOT NULL,
    incident_date DATE,
    incident_time TIME,
    incident_location TEXT,
    
    -- Parties Involved
    victim_age_group VARCHAR(50),
    victim_gender VARCHAR(20),
    perpetrator_description TEXT,
    witnesses_present BOOLEAN DEFAULT FALSE,
    
    -- Reporting Details
    reporter_relationship VARCHAR(100),
    reporter_contact_method VARCHAR(50),
    reporter_phone VARCHAR(20),
    reporter_email VARCHAR(255),
    anonymous_report BOOLEAN DEFAULT TRUE,
    
    -- Urgency and Follow-up
    urgency_level VARCHAR(20) NOT NULL,
    immediate_danger BOOLEAN DEFAULT FALSE,
    authorities_contacted BOOLEAN DEFAULT FALSE,
    which_authorities TEXT,
    
    -- Additional Information
    additional_details TEXT,
    supporting_evidence TEXT,
    
    -- Consent and Privacy
    consent_to_contact BOOLEAN DEFAULT FALSE,
    consent_to_investigate BOOLEAN DEFAULT TRUE,
    
    -- Case Management
    status VARCHAR(20) DEFAULT 'pending',
    assigned_officer VARCHAR(255),
    priority_level VARCHAR(20) DEFAULT 'medium',
    follow_up_required BOOLEAN DEFAULT TRUE,
    case_notes TEXT,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP
);

-- =====================================================
-- 6. WHISTLEBLOWING REPORTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS whistleblowing_reports (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Report Details
    concern_type VARCHAR(100) NOT NULL,
    concern_category VARCHAR(100) NOT NULL,
    concern_description TEXT NOT NULL,
    incident_date DATE,
    ongoing_issue BOOLEAN DEFAULT FALSE,
    
    -- Parties Involved
    individuals_involved TEXT,
    departments_involved TEXT,
    external_parties_involved TEXT,
    
    -- Evidence and Documentation
    evidence_available BOOLEAN DEFAULT FALSE,
    evidence_description TEXT,
    documents_attached BOOLEAN DEFAULT FALSE,
    witnesses_available BOOLEAN DEFAULT FALSE,
    witness_details TEXT,
    
    -- Reporter Information (Optional)
    reporter_name VARCHAR(255),
    reporter_position VARCHAR(255),
    reporter_department VARCHAR(255),
    reporter_contact_phone VARCHAR(20),
    reporter_contact_email VARCHAR(255),
    anonymous_report BOOLEAN DEFAULT TRUE,
    
    -- Previous Reporting
    previously_reported BOOLEAN DEFAULT FALSE,
    previous_report_details TEXT,
    previous_report_outcome TEXT,
    
    -- Urgency and Risk Assessment
    urgency_level VARCHAR(20) NOT NULL,
    risk_level VARCHAR(20) DEFAULT 'medium',
    immediate_action_required BOOLEAN DEFAULT FALSE,
    potential_harm_description TEXT,
    
    -- Investigation Preferences
    preferred_investigation_method VARCHAR(100),
    external_investigation_preferred BOOLEAN DEFAULT FALSE,
    confidentiality_level VARCHAR(50) DEFAULT 'high',
    
    -- Case Management
    status VARCHAR(20) DEFAULT 'received',
    assigned_investigator VARCHAR(255),
    investigation_start_date DATE,
    target_completion_date DATE,
    actual_completion_date DATE,
    
    -- Follow-up and Communication
    reporter_feedback_requested BOOLEAN DEFAULT FALSE,
    communication_method VARCHAR(50),
    updates_provided INTEGER DEFAULT 0,
    case_notes TEXT,
    
    -- Outcomes and Actions
    investigation_outcome TEXT,
    corrective_actions_taken TEXT,
    preventive_measures_implemented TEXT,
    policy_changes_recommended TEXT,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at TIMESTAMP,
    closed_at TIMESTAMP
);

-- =====================================================
-- CREATE ALL INDEXES FOR PERFORMANCE
-- =====================================================

-- Volunteer Applications Indexes
CREATE INDEX IF NOT EXISTS idx_volunteer_reference_number ON volunteer_applications (reference_number);
CREATE INDEX IF NOT EXISTS idx_volunteer_status ON volunteer_applications (status);
CREATE INDEX IF NOT EXISTS idx_volunteer_areas ON volunteer_applications USING GIN (volunteer_areas);
CREATE INDEX IF NOT EXISTS idx_volunteer_created_at ON volunteer_applications (created_at);

-- Partner Inquiries Indexes
CREATE INDEX IF NOT EXISTS idx_partner_reference_number ON partner_inquiries (reference_number);
CREATE INDEX IF NOT EXISTS idx_partner_organization_type ON partner_inquiries (organization_type);
CREATE INDEX IF NOT EXISTS idx_partner_status ON partner_inquiries (status);
CREATE INDEX IF NOT EXISTS idx_partner_created_at ON partner_inquiries (created_at);

-- Program Applications Indexes
CREATE INDEX IF NOT EXISTS idx_program_reference_number ON program_applications (reference_number);
CREATE INDEX IF NOT EXISTS idx_program_type ON program_applications (program_type);
CREATE INDEX IF NOT EXISTS idx_program_status ON program_applications (status);
CREATE INDEX IF NOT EXISTS idx_program_created_at ON program_applications (created_at);

-- Parent Support Requests Indexes
CREATE INDEX IF NOT EXISTS idx_parent_support_reference_number ON parent_support_requests (reference_number);
CREATE INDEX IF NOT EXISTS idx_parent_support_urgency_level ON parent_support_requests (urgency_level);
CREATE INDEX IF NOT EXISTS idx_parent_support_status ON parent_support_requests (status);
CREATE INDEX IF NOT EXISTS idx_parent_support_created_at ON parent_support_requests (created_at);

-- Incident Reports Indexes
CREATE INDEX IF NOT EXISTS idx_incident_reference_number ON incident_reports (reference_number);
CREATE INDEX IF NOT EXISTS idx_incident_type ON incident_reports (incident_type);
CREATE INDEX IF NOT EXISTS idx_incident_urgency ON incident_reports (urgency_level);
CREATE INDEX IF NOT EXISTS idx_incident_status ON incident_reports (status);
CREATE INDEX IF NOT EXISTS idx_incident_date ON incident_reports (incident_date);
CREATE INDEX IF NOT EXISTS idx_incident_created_at ON incident_reports (created_at);
CREATE INDEX IF NOT EXISTS idx_incident_immediate_danger ON incident_reports (immediate_danger);

-- Whistleblowing Reports Indexes
CREATE INDEX IF NOT EXISTS idx_whistleblowing_reference_number ON whistleblowing_reports (reference_number);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_concern_type ON whistleblowing_reports (concern_type);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_urgency ON whistleblowing_reports (urgency_level);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_status ON whistleblowing_reports (status);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_created_at ON whistleblowing_reports (created_at);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_anonymous ON whistleblowing_reports (anonymous_report);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_risk_level ON whistleblowing_reports (risk_level);

-- =====================================================
-- CREATE SEQUENCES FOR REFERENCE NUMBERS
-- =====================================================
CREATE SEQUENCE IF NOT EXISTS volunteer_ref_seq START 1;
CREATE SEQUENCE IF NOT EXISTS partner_ref_seq START 1;
CREATE SEQUENCE IF NOT EXISTS program_ref_seq START 1;
CREATE SEQUENCE IF NOT EXISTS parent_support_ref_seq START 1;
CREATE SEQUENCE IF NOT EXISTS incident_ref_seq START 1;
CREATE SEQUENCE IF NOT EXISTS whistleblowing_ref_seq START 1;

-- =====================================================
-- CREATE FUNCTIONS FOR REFERENCE NUMBER GENERATION
-- =====================================================

-- Volunteer Applications Reference Function
CREATE OR REPLACE FUNCTION generate_volunteer_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'VOL-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('volunteer_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Partner Inquiries Reference Function
CREATE OR REPLACE FUNCTION generate_partner_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'PART-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('partner_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Program Applications Reference Function
CREATE OR REPLACE FUNCTION generate_program_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'PROG-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('program_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Parent Support Reference Function
CREATE OR REPLACE FUNCTION generate_parent_support_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'PS-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('parent_support_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Incident Reports Reference Function
CREATE OR REPLACE FUNCTION generate_incident_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'VF-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('incident_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Whistleblowing Reference Function
CREATE OR REPLACE FUNCTION generate_whistleblowing_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'WB-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('whistleblowing_ref_seq')::TEXT, 4, '0');
    NEW.acknowledged_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- CREATE TRIGGERS FOR AUTO-GENERATION
-- =====================================================

-- Volunteer Applications Trigger
DROP TRIGGER IF EXISTS volunteer_reference_trigger ON volunteer_applications;
CREATE TRIGGER volunteer_reference_trigger
    BEFORE INSERT ON volunteer_applications
    FOR EACH ROW
    EXECUTE FUNCTION generate_volunteer_reference();

-- Partner Inquiries Trigger
DROP TRIGGER IF EXISTS partner_reference_trigger ON partner_inquiries;
CREATE TRIGGER partner_reference_trigger
    BEFORE INSERT ON partner_inquiries
    FOR EACH ROW
    EXECUTE FUNCTION generate_partner_reference();

-- Program Applications Trigger
DROP TRIGGER IF EXISTS program_reference_trigger ON program_applications;
CREATE TRIGGER program_reference_trigger
    BEFORE INSERT ON program_applications
    FOR EACH ROW
    EXECUTE FUNCTION generate_program_reference();

-- Parent Support Trigger
DROP TRIGGER IF EXISTS parent_support_reference_trigger ON parent_support_requests;
CREATE TRIGGER parent_support_reference_trigger
    BEFORE INSERT ON parent_support_requests
    FOR EACH ROW
    EXECUTE FUNCTION generate_parent_support_reference();

-- Incident Reports Trigger
DROP TRIGGER IF EXISTS incident_reference_trigger ON incident_reports;
CREATE TRIGGER incident_reference_trigger
    BEFORE INSERT ON incident_reports
    FOR EACH ROW
    EXECUTE FUNCTION generate_incident_reference();

-- Whistleblowing Trigger
DROP TRIGGER IF EXISTS whistleblowing_reference_trigger ON whistleblowing_reports;
CREATE TRIGGER whistleblowing_reference_trigger
    BEFORE INSERT ON whistleblowing_reports
    FOR EACH ROW
    EXECUTE FUNCTION generate_whistleblowing_reference();

-- =====================================================
-- CREATE UPDATE TIMESTAMP FUNCTIONS AND TRIGGERS
-- =====================================================

-- Generic update timestamp function
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Whistleblowing specific update function
CREATE OR REPLACE FUNCTION update_whistleblowing_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    
    -- Set target completion date if investigation starts
    IF OLD.investigation_start_date IS NULL AND NEW.investigation_start_date IS NOT NULL THEN
        NEW.target_completion_date := NEW.investigation_start_date + INTERVAL '30 days';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create update triggers for all tables
DROP TRIGGER IF EXISTS volunteer_update_trigger ON volunteer_applications;
CREATE TRIGGER volunteer_update_trigger
    BEFORE UPDATE ON volunteer_applications
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

DROP TRIGGER IF EXISTS partner_update_trigger ON partner_inquiries;
CREATE TRIGGER partner_update_trigger
    BEFORE UPDATE ON partner_inquiries
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

DROP TRIGGER IF EXISTS program_update_trigger ON program_applications;
CREATE TRIGGER program_update_trigger
    BEFORE UPDATE ON program_applications
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

DROP TRIGGER IF EXISTS parent_support_update_trigger ON parent_support_requests;
CREATE TRIGGER parent_support_update_trigger
    BEFORE UPDATE ON parent_support_requests
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

DROP TRIGGER IF EXISTS incident_update_trigger ON incident_reports;
CREATE TRIGGER incident_update_trigger
    BEFORE UPDATE ON incident_reports
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

DROP TRIGGER IF EXISTS whistleblowing_update_trigger ON whistleblowing_reports;
CREATE TRIGGER whistleblowing_update_trigger
    BEFORE UPDATE ON whistleblowing_reports
    FOR EACH ROW
    EXECUTE FUNCTION update_whistleblowing_timestamp();

-- =====================================================
-- ADD TABLE COMMENTS FOR DOCUMENTATION
-- =====================================================
COMMENT ON TABLE volunteer_applications IS 'Stores volunteer applications from the Volunteer page form';
COMMENT ON TABLE partner_inquiries IS 'Stores partnership inquiries from the Partner page form';
COMMENT ON TABLE program_applications IS 'Stores program applications from various program application forms';
COMMENT ON TABLE parent_support_requests IS 'Stores parent support requests from the Parent Support page form';
COMMENT ON TABLE incident_reports IS 'Stores anonymous incident reports from the Report Incident page';
COMMENT ON TABLE whistleblowing_reports IS 'Stores whistleblowing reports from the Whistleblowing Policy page';

-- =====================================================
-- SCHEMA CREATION COMPLETE
-- =====================================================
-- All tables, indexes, sequences, functions, and triggers have been created
-- Reference number formats:
-- - Volunteer Applications: VOL-YYYY-NNNN
-- - Partner Inquiries: PART-YYYY-NNNN  
-- - Program Applications: PROG-YYYY-NNNN
-- - Parent Support Requests: PS-YYYY-NNNN
-- - Incident Reports: VF-YYYY-NNNN
-- - Whistleblowing Reports: WB-YYYY-NNNN
