-- Create parent_support_requests table for Parent Support page form submissions
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
);

-- Create indexes for better performance
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);

-- Create sequence for reference number generation
CREATE SEQUENCE IF NOT EXISTS parent_support_ref_seq START 1;

-- Create function to generate reference numbers
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;

-- Create trigger to auto-generate reference numbers
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();

-- Add comments for documentation
COMMENT ON TABLE parent_support_requests IS 'Stores parent support requests from the Parent Support page form';
COMMENT ON COLUMN parent_support_requests.reference_number IS 'Auto-generated reference number in format PS-YYYY-NNNN';
COMMENT ON COLUMN parent_support_requests.primary_challenges IS 'Array of primary challenges selected by parent';
COMMENT ON COLUMN parent_support_requests.support_needed IS 'Array of support types requested';
COMMENT ON COLUMN parent_support_requests.urgency_level IS 'Urgency level: low, medium, high, emergency';
