-- SQL to create missing tables in Supabase
-- Copy and paste this in Supabase SQL Editor and click RUN

-- Create certificate_subjects table
CREATE TABLE IF NOT EXISTS certificate_subjects (
    id BIGSERIAL PRIMARY KEY,
    reg_no TEXT NOT NULL,
    subject_code TEXT,
    subject_name TEXT,
    credits_registered INTEGER,
    credits_earned INTEGER,
    grade TEXT,
    grade_points INTEGER,
    semester TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(reg_no, subject_code, semester)
);

-- Create certificate_summary table
CREATE TABLE IF NOT EXISTS certificate_summary (
    id BIGSERIAL PRIMARY KEY,
    reg_no TEXT UNIQUE NOT NULL,
    total_credits_registered INTEGER,
    total_credits_earned INTEGER,
    sgpa REAL,
    cgpa REAL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_subjects_reg_no ON certificate_subjects(reg_no);
CREATE INDEX IF NOT EXISTS idx_summary_reg_no ON certificate_summary(reg_no);

-- Enable Row Level Security (RLS)
ALTER TABLE certificate_subjects ENABLE ROW LEVEL SECURITY;
ALTER TABLE certificate_summary ENABLE ROW LEVEL SECURITY;

-- Create policies for public access
CREATE POLICY "Public read subjects" ON certificate_subjects FOR SELECT TO public USING (true);
CREATE POLICY "Public insert subjects" ON certificate_subjects FOR INSERT TO public WITH CHECK (true);
CREATE POLICY "Public update subjects" ON certificate_subjects FOR UPDATE TO public USING (true) WITH CHECK (true);

CREATE POLICY "Public read summary" ON certificate_summary FOR SELECT TO public USING (true);
CREATE POLICY "Public insert summary" ON certificate_summary FOR INSERT TO public WITH CHECK (true);
CREATE POLICY "Public update summary" ON certificate_summary FOR UPDATE TO public USING (true) WITH CHECK (true);