supabase-setup.sql

-- =========================================
-- Supabase Certificates Table Setup
-- =========================================
-- Run this in your Supabase SQL Editor
-- Go to: https://app.supabase.com/project/osphfkqjojmqbrzyocad/sql/new
-- =========================================

-- Drop table if exists (CAUTION: This will delete all data!)
-- Uncomment the next line only if you want to recreate from scratch
-- DROP TABLE IF EXISTS certificates CASCADE;

-- Create certificates table
CREATE TABLE IF NOT EXISTS certificates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Institution Information (foreign key to institutions table)
  institution_id TEXT REFERENCES institutions(id),
  institution TEXT,
  
  -- Student Information
  student_id UUID,
  student_name TEXT,
  roll_number TEXT,
  
  -- Uploader Information (foreign key to profiles table)
  uploaded_by UUID REFERENCES auth.users(id),
  uploader_profile_id TEXT REFERENCES profiles(user_id),
  
  -- Certificate Details
  certificate_type TEXT,
  file_name TEXT,
  file_hash TEXT UNIQUE,
  
  -- IPFS Storage
  cid TEXT UNIQUE,
  ipfs_url TEXT,
  
  -- Blockchain Anchoring
  anchor_tx TEXT,
  anchor_url TEXT,
  
  -- OCR Data
  ocr_data JSONB,
  marks JSONB,
  program TEXT,
  batch TEXT,
  
  -- Authentication Results
  authentication_result JSONB,
  
  -- Timestamps
  upload_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_certificates_student_name ON certificates(student_name);
CREATE INDEX IF NOT EXISTS idx_certificates_roll_number ON certificates(roll_number);
CREATE INDEX IF NOT EXISTS idx_certificates_institution ON certificates(institution);
CREATE INDEX IF NOT EXISTS idx_certificates_file_hash ON certificates(file_hash);
CREATE INDEX IF NOT EXISTS idx_certificates_cid ON certificates(cid);
CREATE INDEX IF NOT EXISTS idx_certificates_upload_date ON certificates(upload_date DESC);

-- DISABLE Row Level Security (RLS) for testing
-- WARNING: This allows anyone to insert/read data
-- In production, you should configure proper RLS policies
ALTER TABLE certificates DISABLE ROW LEVEL SECURITY;

-- OR if you want to keep RLS enabled, add permissive policies:
-- (Comment out the DISABLE RLS line above and uncomment these)

-- Enable RLS
-- ALTER TABLE certificates ENABLE ROW LEVEL SECURITY;

-- Allow anyone to insert certificates
-- CREATE POLICY "Enable insert for all users" 
-- ON certificates FOR INSERT 
-- WITH CHECK (true);

-- Allow anyone to read certificates
-- CREATE POLICY "Enable read for all users" 
-- ON certificates FOR SELECT 
-- USING (true);

-- Allow anyone to update certificates
-- CREATE POLICY "Enable update for all users" 
-- ON certificates FOR UPDATE 
-- USING (true);

-- Allow anyone to delete certificates
-- CREATE POLICY "Enable delete for all users" 
-- ON certificates FOR DELETE 
-- USING (true);

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to automatically update updated_at
DROP TRIGGER IF EXISTS update_certificates_updated_at ON certificates;
CREATE TRIGGER update_certificates_updated_at
    BEFORE UPDATE ON certificates
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Verify table creation
SELECT 
  'certificates' as table_name,
  COUNT(*) as row_count,
  pg_size_pretty(pg_total_relation_size('certificates')) as total_size
FROM certificates;

-- Show table structure
SELECT 
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_name = 'certificates'
ORDER BY ordinal_position;

-- =========================================
-- Success! Your table is ready.
-- =========================================
-- Now test by running this in your SQL editor:
-- INSERT INTO certificates (student_name, roll_number, institution, file_hash, cid)
-- VALUES ('Test Student', 'TEST001', 'Test University', 'test_hash_123', 'QmTestCID123');
-- 
-- SELECT * FROM certificates WHERE student_name = 'Test Student';
-- =========================================