-- Create initial admin user (you'll need to sign up with this email first)
-- This will be updated after the admin signs up
INSERT INTO public.profiles (id, email, full_name, role)
VALUES (
  '00000000-0000-0000-0000-000000000000', -- Placeholder ID, will be updated
  'admin@fanaka.edu.gh',
  'System Administrator',
  'admin'
) ON CONFLICT (id) DO NOTHING;

-- Create sample programs
CREATE TABLE IF NOT EXISTS public.programs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  code TEXT UNIQUE NOT NULL,
  faculty TEXT NOT NULL,
  duration_years INTEGER NOT NULL,
  degree_type TEXT NOT NULL CHECK (degree_type IN ('bachelor', 'master', 'phd', 'diploma', 'certificate')),
  requirements TEXT[],
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS for programs
ALTER TABLE public.programs ENABLE ROW LEVEL SECURITY;

-- Allow everyone to read programs
CREATE POLICY "Anyone can view active programs" ON public.programs
  FOR SELECT USING (is_active = TRUE);

-- Only admins can manage programs
CREATE POLICY "Admins can manage programs" ON public.programs
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.profiles 
      WHERE id = auth.uid() 
      AND role IN ('admin', 'rector', 'registrar')
    )
  );

-- Insert sample programs
INSERT INTO public.programs (name, code, faculty, duration_years, degree_type, requirements) VALUES
('Computer Science', 'CS101', 'Faculty of Computing', 4, 'bachelor', ARRAY['Mathematics', 'English', 'Physics']),
('Business Administration', 'BA101', 'Faculty of Business', 4, 'bachelor', ARRAY['Mathematics', 'English', 'Economics']),
('Nursing', 'NUR101', 'Faculty of Health Sciences', 4, 'bachelor', ARRAY['Biology', 'Chemistry', 'English']),
('Civil Engineering', 'CE101', 'Faculty of Engineering', 4, 'bachelor', ARRAY['Mathematics', 'Physics', 'Chemistry']),
('Medicine', 'MED101', 'Faculty of Medicine', 6, 'bachelor', ARRAY['Biology', 'Chemistry', 'Physics', 'Mathematics']),
('Law', 'LAW101', 'Faculty of Law', 4, 'bachelor', ARRAY['English', 'Government', 'History']),
('Education', 'EDU101', 'Faculty of Education', 4, 'bachelor', ARRAY['English', 'Mathematics', 'Social Studies']),
('Agriculture', 'AGR101', 'Faculty of Agriculture', 4, 'bachelor', ARRAY['Biology', 'Chemistry', 'Mathematics']);
