-- Added composite indexes for better query performance
CREATE INDEX idx_courses_teacher_status ON courses(teacher_id, status);
CREATE INDEX idx_sessions_course_scheduled ON sessions(course_id, scheduled_at);
CREATE INDEX idx_enrollments_student_course ON course_enrollments(student_id, course_id);
CREATE INDEX idx_attendance_student_session ON session_attendance(student_id, session_id);

-- Added partial indexes for active records
CREATE INDEX idx_active_courses ON courses(teacher_id) WHERE status = 'published';
CREATE INDEX idx_active_sessions ON sessions(course_id) WHERE status IN ('scheduled', 'ongoing');
CREATE INDEX idx_pending_orders ON orders(user_id) WHERE status = 'pending';

-- Added check constraints for data validation
ALTER TABLE courses ADD CONSTRAINT check_positive_price CHECK (price >= 0);
ALTER TABLE sessions ADD CONSTRAINT check_positive_duration CHECK (duration_minutes > 0);
ALTER TABLE quiz_questions ADD CONSTRAINT check_positive_points CHECK (points > 0);
ALTER TABLE mochi_scores ADD CONSTRAINT check_score_range CHECK (overall_score >= 0 AND overall_score <= 100);
ALTER TABLE mochi_scores ADD CONSTRAINT check_level_range CHECK (level >= 1 AND level <= 5);

-- Added missing foreign key constraints
ALTER TABLE course_enrollments ADD CONSTRAINT fk_course_enrollments_course 
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE;

ALTER TABLE session_participants ADD CONSTRAINT fk_session_participants_session 
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;

ALTER TABLE quiz_attempts ADD CONSTRAINT fk_quiz_attempts_quiz 
  FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE;

-- Create notifications table
CREATE TABLE notifications (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  type TEXT NOT NULL, -- 'info', 'success', 'warning', 'error'
  is_read BOOLEAN DEFAULT false,
  action_url TEXT,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create notification preferences table
CREATE TABLE notification_preferences (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  email_notifications BOOLEAN DEFAULT true,
  push_notifications BOOLEAN DEFAULT true,
  sms_notifications BOOLEAN DEFAULT false,
  notification_types JSONB DEFAULT '{}',
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id)
);

-- Create file_uploads table
CREATE TABLE file_uploads (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  filename TEXT NOT NULL,
  original_filename TEXT NOT NULL,
  file_size INTEGER NOT NULL,
  mime_type TEXT NOT NULL,
  file_url TEXT NOT NULL,
  upload_type TEXT NOT NULL, -- 'assignment', 'profile_image', 'course_thumbnail', etc.
  reference_id UUID, -- ID of the related record
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create file_sharing table
CREATE TABLE file_sharing (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  file_id UUID REFERENCES file_uploads(id) ON DELETE CASCADE,
  shared_with UUID REFERENCES profiles(id) ON DELETE CASCADE,
  permission_level TEXT DEFAULT 'read', -- 'read', 'write', 'admin'
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(file_id, shared_with)
);

-- Create user_activity_logs table
CREATE TABLE user_activity_logs (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  activity_type TEXT NOT NULL, -- 'login', 'course_view', 'session_join', etc.
  activity_data JSONB,
  ip_address INET,
  user_agent TEXT,
  session_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create system_metrics table
CREATE TABLE system_metrics (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  metric_name TEXT NOT NULL,
  metric_value NUMERIC NOT NULL,
  metric_unit TEXT,
  tags JSONB DEFAULT '{}',
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Split large tables into smaller, focused tables
-- Example: Separating course content from course metadata

-- Create course_content table
CREATE TABLE course_content (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  content_type TEXT NOT NULL, -- 'text', 'video', 'document', 'quiz'
  title TEXT NOT NULL,
  content_data JSONB NOT NULL,
  order_index INTEGER NOT NULL,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Migrate existing data
INSERT INTO course_content (course_id, content_type, title, content_data, order_index, is_published)
SELECT 
  id as course_id,
  'text' as content_type,
  title,
  jsonb_build_object('description', description) as content_data,
  1 as order_index,
  (status = 'published') as is_published
FROM courses
WHERE description IS NOT NULL;

-- Add new columns to existing tables
ALTER TABLE profiles ADD COLUMN timezone TEXT DEFAULT 'UTC';
ALTER TABLE profiles ADD COLUMN language_preference TEXT DEFAULT 'en';
ALTER TABLE profiles ADD COLUMN notification_settings JSONB DEFAULT '{}';

-- Modify existing columns
ALTER TABLE courses ALTER COLUMN description TYPE TEXT;
ALTER TABLE sessions ALTER COLUMN duration_minutes SET DEFAULT 60;

-- Add constraints to existing columns
ALTER TABLE profiles ADD CONSTRAINT check_valid_email 
  CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Migrate user preferences to new structure
INSERT INTO notification_preferences (user_id, email_notifications, push_notifications)
SELECT 
  id as user_id,
  COALESCE((notification_settings->>'email')::boolean, true) as email_notifications,
  COALESCE((notification_settings->>'push')::boolean, true) as push_notifications
FROM profiles
WHERE notification_settings IS NOT NULL;

-- Migrate course modules to new content structure
INSERT INTO course_content (course_id, content_type, title, content_data, order_index, is_published)
SELECT 
  cm.course_id,
  'module' as content_type,
  cm.title,
  jsonb_build_object(
    'description', cm.description,
    'lessons', jsonb_agg(
      jsonb_build_object(
        'id', cl.id,
        'title', cl.title,
        'content', cl.content,
        'video_url', cl.video_url,
        'duration_minutes', cl.duration_minutes,
        'is_free', cl.is_free
      ) ORDER BY cl.order_index
    )
  ) as content_data,
  cm.order_index,
  true as is_published
FROM course_modules cm
LEFT JOIN course_lessons cl ON cm.id = cl.module_id
GROUP BY cm.id, cm.course_id, cm.title, cm.description, cm.order_index;

-- Create view for slow query monitoring
CREATE VIEW slow_queries AS
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
WHERE mean_time > 1000  -- Queries taking more than 1 second on average
ORDER BY mean_time DESC;

-- Create view for table size monitoring
CREATE VIEW table_sizes AS
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY size_bytes DESC;

-- Create view for index usage statistics
CREATE VIEW index_usage_stats AS
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_tup_read,
  idx_tup_fetch,
  idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Create backup metadata table
CREATE TABLE backup_metadata (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  backup_type TEXT NOT NULL, -- 'full', 'incremental', 'schema_only'
  backup_size_bytes BIGINT,
  backup_location TEXT NOT NULL,
  backup_status TEXT DEFAULT 'completed', -- 'in_progress', 'completed', 'failed'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE
);

-- Create backup verification table
CREATE TABLE backup_verification (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  backup_id UUID REFERENCES backup_metadata(id) ON DELETE CASCADE,
  table_name TEXT NOT NULL,
  row_count BIGINT,
  checksum TEXT,
  verified_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create function to validate data integrity
CREATE OR REPLACE FUNCTION validate_data_integrity()
RETURNS TABLE(
  table_name TEXT,
  issue_type TEXT,
  issue_description TEXT,
  affected_rows BIGINT
) AS $$
BEGIN
  -- Check for orphaned records
  RETURN QUERY
  SELECT 
    'course_enrollments'::TEXT,
    'orphaned_record'::TEXT,
    'Enrollment without valid course'::TEXT,
    COUNT(*)::BIGINT
  FROM course_enrollments ce
  LEFT JOIN courses c ON ce.course_id = c.id
  WHERE c.id IS NULL;
  
  -- Check for invalid scores
  RETURN QUERY
  SELECT 
    'mochi_scores'::TEXT,
    'invalid_data'::TEXT,
    'Score out of valid range'::TEXT,
    COUNT(*)::BIGINT
  FROM mochi_scores
  WHERE overall_score < 0 OR overall_score > 100;
  
  -- Check for future dates
  RETURN QUERY
  SELECT 
    'sessions'::TEXT,
    'invalid_date'::TEXT,
    'Session scheduled in the past'::TEXT,
    COUNT(*)::BIGINT
  FROM sessions
  WHERE scheduled_at < NOW();
END;
$$ LANGUAGE plpgsql;