schema.sql

-- Enable the vector extension for embeddings
CREATE EXTENSION IF NOT EXISTS vector;

-- Create schools table
CREATE TABLE schools (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES auth.users(id),
    name TEXT NOT NULL,
    address_line1 TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    school_type TEXT CHECK (school_type IN ('Public', 'Private', 'Charter', 'Nonprofit', 'Other')),
    educational_level TEXT CHECK (educational_level IN ('K-12', 'Higher Education', 'Both')),
    enrollment INTEGER,
    title_i_status BOOLEAN DEFAULT FALSE,
    free_reduced_lunch_percentage FLOAT,
    contact_email TEXT,
    additional_info TEXT,
    embedding vector(1536),  -- For OpenAI embeddings
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW())
);

-- Create grants table
CREATE TABLE grants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT NOT NULL,
    url TEXT,
    provider TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    due_date TIMESTAMP WITH TIME ZONE,
    funding_amount DECIMAL,
    contact_info TEXT,
    description TEXT,
    embedding vector(1536),  -- For OpenAI embeddings
    requirements TEXT,       -- Specific requirements for the grant
    eligibility TEXT,       -- Eligibility criteria
    category TEXT[]         -- Array of categories (e.g., ['STEM', 'Education', 'Technology'])
);

-- Create applications table to track grant applications
CREATE TABLE applications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    school_id UUID REFERENCES schools(id),
    grant_id UUID REFERENCES grants(id),
    status TEXT CHECK (status IN ('Draft', 'Submitted', 'Under Review', 'Approved', 'Rejected')),
    submission_date TIMESTAMP WITH TIME ZONE,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    UNIQUE(school_id, grant_id)
);

-- Create application_chat_history table to store chat interactions
CREATE TABLE application_chat_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    application_id UUID REFERENCES applications(id),
    message_type TEXT CHECK (message_type IN ('user', 'assistant')),
    content TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW())
);

-- Create saved_responses table to store drafted responses
CREATE TABLE saved_responses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    application_id UUID REFERENCES applications(id),
    question_key TEXT,      -- Identifier for the question being answered
    response_text TEXT,     -- The drafted response
    last_edited TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW())
);

-- Create application_documents table for uploaded files
CREATE TABLE application_documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    application_id UUID REFERENCES applications(id),
    document_type TEXT,     -- e.g., 'Budget', 'Timeline', 'Letter of Support'
    file_name TEXT,
    file_url TEXT,
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW())
);

-- Add RLS (Row Level Security) policies
ALTER TABLE schools ENABLE ROW LEVEL SECURITY;
ALTER TABLE applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE application_chat_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE saved_responses ENABLE ROW LEVEL SECURITY;
ALTER TABLE application_documents ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their own school profile"
    ON schools FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "Users can update their own school profile"
    ON schools FOR UPDATE
    USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own school profile"
    ON schools FOR INSERT
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view their own applications"
    ON applications FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM schools
        WHERE schools.id = applications.school_id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can create their own applications"
    ON applications FOR INSERT
    WITH CHECK (EXISTS (
        SELECT 1 FROM schools
        WHERE schools.id = applications.school_id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can view their own chat history"
    ON application_chat_history FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE application_chat_history.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can insert chat messages"
    ON application_chat_history FOR INSERT
    WITH CHECK (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE application_chat_history.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can view their saved responses"
    ON saved_responses FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE saved_responses.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can manage their saved responses"
    ON saved_responses FOR ALL
    USING (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE saved_responses.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can view their documents"
    ON application_documents FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE application_documents.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));

CREATE POLICY "Users can manage their documents"
    ON application_documents FOR ALL
    USING (EXISTS (
        SELECT 1 FROM applications
        JOIN schools ON schools.id = applications.school_id
        WHERE application_documents.application_id = applications.id
        AND schools.user_id = auth.uid()
    ));