/**
 * Course Session Persistence Service
 * 
 * This service handles persistent v2 certification sessions.
 * Features:
 * - Save and restore complete certification flow state
 * - Track course progress across sessions
 * - Manage assessment question generation and regeneration
 * - Integration with My Courses dashboard
 * - Window focus detection for question regeneration
 */

import { supabase } from './supabase';
import type { CertificationFlowState } from '../types';
import { logger } from '../utils/secureLogger';

const lastCourseSessionFingerprintByKey = new Map<string, string>();
const lastMyCourseFingerprintByKey = new Map<string, string>();
const profileSyncInFlightByUserId = new Map<string, Promise<void>>();

function buildPersistenceKey(userId: string, courseName: string): string {
  return `${userId}:${courseName.trim().toLowerCase()}`;
}

function buildSessionFingerprint(payload: {
  flowState: CertificationFlowState;
  learningModules?: any;
  generatedQuestions?: any;
  assessmentAnswers?: string;
  projectData?: any;
  currentStep: string;
  progressPercentage: number;
}): string {
  return JSON.stringify({
    flowState: payload.flowState,
    learningModules: payload.learningModules,
    generatedQuestions: payload.generatedQuestions,
    assessmentAnswers: payload.assessmentAnswers,
    projectData: payload.projectData,
    currentStep: payload.currentStep,
    progressPercentage: payload.progressPercentage,
  });
}

function buildMyCourseFingerprint(updates: {
  status?: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
  currentStep?: string;
  progressPercentage?: number;
  assessmentScore?: number | null;
  hasGeneratedQuestions?: boolean;
  questionsGeneratedAt?: string | null;
  completedAt?: string | null;
}): string {
  return JSON.stringify(updates);
}

async function ensurePublicUserProfile(userId: string): Promise<void> {
  const existingPromise = profileSyncInFlightByUserId.get(userId);
  if (existingPromise) {
    try {
      await existingPromise;
    } catch {
      // Safe no-op
    }
    return;
  }

  const syncPromise = (async () => {
    try {
      const { data: authResult, error: authError } = await supabase.auth.getUser();
      if (authError || !authResult?.user) {
        return;
      }

      const authUser = authResult.user;
      if (authUser.id !== userId) return;

      const { data: profile, error: profileError } = await supabase
        .from('users')
        .select('id')
        .eq('id', authUser.id)
        .maybeSingle();

      if (profileError) {
        logger.warn(' Could not verify public user profile existence:', profileError.message);
      }

      if (!profile) {
        logger.warn(` User profile for ${userId} not yet found in public.users.`);
      }
    } catch (err) {
      logger.warn(' Non-blocking warning verifying user profile:', err);
    }
  })();

  profileSyncInFlightByUserId.set(userId, syncPromise);

  try {
    await syncPromise;
  } finally {
    profileSyncInFlightByUserId.delete(userId);
  }
}

export interface CourseSession {
  id: string;
  user_id: string;
  course_name: string;
  flow_state: CertificationFlowState;
  learning_modules: any;
  generated_questions: any;
  assessment_answers: string | null;
  project_data: any;
  current_step: string;
  progress_percentage: number;
  last_resumed_at: string | null;
  created_at: string | null;
  updated_at: string | null;
}

export interface MyCourse {
  id: string;
  user_id: string;
  course_name: string;
  status: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
  current_step: string;
  progress_percentage: number;
  assessment_score: number | null;
  has_generated_questions: boolean;
  questions_generated_at: string | null;
  last_activity: string | null;
  started_at: string | null;
  completed_at: string | null;
  created_at: string | null;
  updated_at: string | null;
  certification_track: string | null;
}

export interface AssessmentQuestion {
  title: string;
  question: string;
}

export interface AssessmentQuestionHistory {
  id: string;
  user_id: string;
  course_name: string;
  questions: AssessmentQuestion[];
  generation_reason: 'initial' | 'window_focus_lost' | 'manual_refresh';
  generated_at: string | null;
  created_at: string | null;
}

/**
 * Save or update a course session
 */
export async function saveCourseSession(
  userId: string,
  courseName: string,
  sessionData: {
    flowState: CertificationFlowState;
    learningModules?: any;
    generatedQuestions?: any;
    assessmentAnswers?: string;
    projectData?: any;
    currentStep: string;
    progressPercentage: number;
  }
): Promise<{ success: boolean; error?: string }> {
  try {
    logger.log(` Saving course session for user ${userId}, course: ${courseName}`);

    await ensurePublicUserProfile(userId);

    const persistenceKey = buildPersistenceKey(userId, courseName);
    const fingerprint = buildSessionFingerprint(sessionData);

    if (lastCourseSessionFingerprintByKey.get(persistenceKey) === fingerprint) {
      logger.log(` Skipping duplicate course session save for ${courseName}`);
      return { success: true };
    }

    lastCourseSessionFingerprintByKey.set(persistenceKey, fingerprint);

    // Use limit(1) instead of maybeSingle so duplicate historical rows
    // do not crash the save path while we self-heal via updates.
    const { data: existingSessionRows, error: existingLookupError } = await supabase
      .from('course_sessions')
      .select('id')
      .eq('user_id', userId)
      .eq('course_name', courseName)
      .order('updated_at', { ascending: false })
      .limit(1);

    if (existingLookupError) {
      logger.error(' Error looking up existing course session:', existingLookupError);
      return { success: false, error: existingLookupError.message };
    }

    const existingSessionId = existingSessionRows?.[0]?.id;

    let error;
    const payload = {
      user_id: userId,
      course_name: courseName,
      flow_state: sessionData.flowState,
      learning_modules: sessionData.learningModules,
      generated_questions: sessionData.generatedQuestions,
      assessment_answers: sessionData.assessmentAnswers,
      project_data: sessionData.projectData,
      current_step: sessionData.currentStep,
      progress_percentage: sessionData.progressPercentage
    };

    if (existingSessionId) {
      const { error: updateError } = await supabase
        .from('course_sessions')
        .update(payload)
        .eq('id', existingSessionId);
      error = updateError;
    } else {
      const { error: insertError } = await supabase
        .from('course_sessions')
        .insert(payload);

      if (insertError?.code === '23505') {
        // Another in-flight request inserted the row first.
        const { error: retryUpdateError } = await supabase
          .from('course_sessions')
          .update(payload)
          .eq('user_id', userId)
          .eq('course_name', courseName);
        error = retryUpdateError;
      } else {
        error = insertError;
      }
    }

    if (error) {
      logger.error(' Error saving course session:', error);
      return { success: false, error: error.message };
    }

    logger.log(` Course session saved successfully for ${courseName}`);
    return { success: true };

  } catch (error) {
    logger.error(' Unexpected error saving course session:', error);
    return { success: false, error: 'Unexpected error occurred' };
  }
}

/**
 * Load a course session for resuming
 */
export async function loadCourseSession(
  userId: string,
  courseName: string
): Promise<{ session: CourseSession | null; error?: string }> {
  try {
    logger.log(` Loading course session for user ${userId}, course: ${courseName}`);

    const { data, error } = await supabase
      .from('course_sessions')
      .select('*')
      .eq('user_id', userId)
      .eq('course_name', courseName)
      .maybeSingle();

    if (error && error.code !== 'PGRST116') { // PGRST116 = no rows found
      logger.error(' Error loading course session:', error);
      return { session: null, error: error.message };
    }

    if (!data) {
      logger.log(`No existing session found for ${courseName}`);
      return { session: null };
    }

    logger.log(` Course session loaded for ${courseName}, step: ${data.current_step}`);
    return { session: data };

  } catch (error) {
    logger.error(' Unexpected error loading course session:', error);
    return { session: null, error: 'Unexpected error occurred' };
  }
}

/**
 * Update or create My Courses entry
 */
export async function updateMyCourse(
  userId: string,
  courseName: string,
  updates: {
    status?: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
    currentStep?: string;
    progressPercentage?: number;
    assessmentScore?: number | null;
    hasGeneratedQuestions?: boolean;
    questionsGeneratedAt?: string | null;
    completedAt?: string | null;
    certificationTrack?: string | null;
  }
): Promise<{ success: boolean; error?: string }> {
  try {
    logger.log(` Updating My Courses for user ${userId}, course: ${courseName}`);

    await ensurePublicUserProfile(userId);

    const persistenceKey = buildPersistenceKey(userId, courseName);
    const fingerprint = buildMyCourseFingerprint(updates);

    if (lastMyCourseFingerprintByKey.get(persistenceKey) === fingerprint) {
      logger.log(` Skipping duplicate My Courses update for ${courseName}`);
      return { success: true };
    }

    const nowIso = new Date().toISOString();
    const updateData: any = {
      user_id: userId,
      course_name: courseName,
      last_activity: nowIso,
      updated_at: nowIso,
    };

    if (updates.status !== undefined) updateData.status = updates.status;
    if (updates.currentStep !== undefined) updateData.current_step = updates.currentStep;
    if (updates.progressPercentage !== undefined) updateData.progress_percentage = updates.progressPercentage;
    if (updates.assessmentScore !== undefined) updateData.assessment_score = updates.assessmentScore;
    if (updates.hasGeneratedQuestions !== undefined) updateData.has_generated_questions = updates.hasGeneratedQuestions;
    if (updates.questionsGeneratedAt !== undefined) updateData.questions_generated_at = updates.questionsGeneratedAt;
    if (updates.completedAt !== undefined) updateData.completed_at = updates.completedAt;
    if (updates.certificationTrack !== undefined) updateData.certification_track = updates.certificationTrack;

    // Use limit(1) instead of maybeSingle so duplicate historical rows
    // do not crash the save path while we self-heal via updates.
    const { data: existingCourseRows, error: existingLookupError } = await supabase
      .from('my_courses')
      .select('id')
      .eq('user_id', userId)
      .eq('course_name', courseName)
      .limit(1);

    if (existingLookupError) {
      logger.error(' Error looking up existing My Courses row:', existingLookupError);
    }

    const existingCourseId = existingCourseRows?.[0]?.id;

    let error;
    if (existingCourseId) {
      const { error: updateError } = await supabase
        .from('my_courses')
        .update(updateData)
        .eq('id', existingCourseId);
      error = updateError;
    } else {
      const { error: insertError } = await supabase
        .from('my_courses')
        .insert(updateData);

      if (insertError?.code === '23505') {
        // Another in-flight request inserted the row first.
        const { error: retryUpdateError } = await supabase
          .from('my_courses')
          .update(updateData)
          .eq('user_id', userId)
          .eq('course_name', courseName);
        error = retryUpdateError;
      } else {
        error = insertError;
      }
    }

    if (error) {
      logger.error(' Error updating My Courses:', error);
      return { success: false, error: error.message };
    }

    // Only cache fingerprint on SUCCESS
    lastMyCourseFingerprintByKey.set(persistenceKey, fingerprint);

    logger.log(` My Courses updated for ${courseName}`);
    if (typeof window !== 'undefined') {
      window.dispatchEvent(new CustomEvent('getcert:course_updated', { detail: { courseName } }));
    }
    return { success: true };

  } catch (error) {
    logger.error(' Unexpected error updating My Courses:', error);
    return { success: false, error: 'Unexpected error occurred' };
  }
}

/**
 * Get all courses for a user (for My Courses page)
 * Cross-references both my_courses and certificates table to guarantee zero lost progress.
 */
export async function getMyCourses(userId: string, customClient?: any): Promise<{ courses: MyCourse[]; error?: string }> {
  try {
    logger.log(` Loading My Courses for user ${userId}`);
    const client = customClient || supabase;

    // Query 1: Courses from my_courses table
    const coursesQuery = client
      .from('my_courses')
      .select('*')
      .eq('user_id', userId)
      .order('last_activity', { ascending: false });

    // Query 2: Earned certificates from certificates table
    const certsQuery = client
      .from('certificates')
      .select('id, user_id, topic, project_title, issued_at, metadata')
      .eq('user_id', userId)
      .order('issued_at', { ascending: false });

    const [coursesRes, certsRes] = await Promise.all([coursesQuery, certsQuery]);

    if (coursesRes.error) {
      logger.error(' Error loading My Courses:', coursesRes.error);
    }
    if (certsRes.error) {
      logger.error(' Error loading Certificates:', certsRes.error);
    }

    const dbCourses: MyCourse[] = coursesRes.data || [];
    const dbCerts = certsRes.data || [];

    // Synthesize courses from certificates if missing in my_courses
    const existingCourseNames = new Set(
      dbCourses.map((c) => c.course_name.toLowerCase().trim())
    );

    const syntheticCourses: MyCourse[] = [];
    for (const cert of dbCerts) {
      const meta = (cert.metadata as Record<string, any>) || {};
      const courseName = cert.project_title || `${cert.topic} (Foundation Course)`;
      const normalizedName = courseName.toLowerCase().trim();
      const topicNormalized = (cert.topic || '').toLowerCase().trim();

      // Check if an existing enrolled course in my_courses matches this certificate
      const matchingCourse = dbCourses.find((c) => {
        const cNorm = c.course_name.toLowerCase().trim();
        return (
          cNorm === normalizedName ||
          (topicNormalized && (cNorm.includes(topicNormalized) || topicNormalized.includes(cNorm)))
        );
      });

      let track: 'quickcert' | 'verifiedcert' | 'free_completion' = 'quickcert';
      if (
        meta.certification_track === 'free_completion' ||
        meta.track === 'free_completion' ||
        meta.free_course ||
        meta.completion_type === 'free_starter_course' ||
        String(meta.certificate_id || cert.id).startsWith('FC')
      ) {
        track = 'free_completion';
      } else if (
        meta.certification_track === 'verifiedcert' ||
        meta.track === 'verifiedcert' ||
        String(meta.certificate_id || cert.id).startsWith('VC')
      ) {
        track = 'verifiedcert';
      }

      if (matchingCourse) {
        // Upgrade existing course to 100% certified immediately
        if (matchingCourse.status !== 'certified' || matchingCourse.progress_percentage !== 100) {
          matchingCourse.status = 'certified';
          matchingCourse.current_step = 'certified';
          matchingCourse.progress_percentage = 100;
          matchingCourse.completed_at = cert.issued_at || matchingCourse.completed_at || new Date().toISOString();
          matchingCourse.last_activity = cert.issued_at || matchingCourse.last_activity || new Date().toISOString();
          if (meta.final_score) {
            matchingCourse.assessment_score = meta.final_score;
          }

          // Asynchronously self-heal database row
          void client
            .from('my_courses')
            .update({
              status: 'certified',
              current_step: 'certified',
              progress_percentage: 100,
              completed_at: matchingCourse.completed_at,
              last_activity: matchingCourse.last_activity,
              updated_at: new Date().toISOString(),
            })
            .eq('id', matchingCourse.id);
        }
      } else {
        // Synthesize course from certificate if no course row existed
        const synthetic: MyCourse = {
          id: cert.id,
          user_id: cert.user_id,
          course_name: courseName,
          status: 'certified',
          current_step: 'certified',
          progress_percentage: 100,
          assessment_score: meta.final_score ?? 100,
          has_generated_questions: true,
          questions_generated_at: cert.issued_at,
          last_activity: cert.issued_at || new Date().toISOString(),
          started_at: cert.issued_at || new Date().toISOString(),
          completed_at: cert.issued_at || new Date().toISOString(),
          created_at: cert.issued_at || new Date().toISOString(),
          updated_at: cert.issued_at || new Date().toISOString(),
          certification_track: track,
        };
        syntheticCourses.push(synthetic);
        existingCourseNames.add(normalizedName);

        // Self-heal: backfill into my_courses table asynchronously
        void client
          .from('my_courses')
          .upsert({
            user_id: cert.user_id,
            course_name: courseName,
            status: 'certified',
            current_step: 'certified',
            progress_percentage: 100,
            assessment_score: meta.final_score ?? 100,
            certification_track: track,
            completed_at: cert.issued_at,
            last_activity: cert.issued_at,
          }, { onConflict: 'user_id,course_name' });
      }
    }

    const allCourses = [...dbCourses, ...syntheticCourses].sort((a, b) => {
      const tA = new Date(a.last_activity || a.updated_at || a.started_at).getTime();
      const tB = new Date(b.last_activity || b.updated_at || b.started_at).getTime();
      return tB - tA;
    });

    logger.log(` Loaded ${allCourses.length} courses (${dbCourses.length} directly from DB, ${syntheticCourses.length} synthesized from certificates)`);
    return { courses: allCourses };

  } catch (error) {
    logger.error(' Unexpected error loading My Courses:', error);
    return { courses: [], error: 'Unexpected error occurred' };
  }
}

/**
 * Save assessment questions to history
 */
export async function saveAssessmentQuestions(
  userId: string,
  courseName: string,
  questions: AssessmentQuestion[],
  reason: 'initial' | 'window_focus_lost' | 'manual_refresh' = 'initial'
): Promise<{ success: boolean; error?: string }> {
  try {
    logger.log(` Saving assessment questions for user ${userId}, course: ${courseName}, reason: ${reason}`);

    const { error } = await supabase
      .from('assessment_questions_history')
      .insert({
        user_id: userId,
        course_name: courseName,
        questions: questions,
        generation_reason: reason,
        generated_at: new Date().toISOString()
      });

    if (error) {
      logger.error(' Error saving assessment questions:', error);
      return { success: false, error: error.message };
    }

    // Also update the course session with the new questions
    await supabase
      .from('course_sessions')
      .update({
        generated_questions: questions
      })
      .eq('user_id', userId)
      .eq('course_name', courseName);

    logger.log(` Assessment questions saved for ${courseName}`);
    return { success: true };

  } catch (error) {
    logger.error(' Unexpected error saving assessment questions:', error);
    return { success: false, error: 'Unexpected error occurred' };
  }
}

/**
 * Get the latest assessment questions for a course
 */
export async function getLatestAssessmentQuestions(
  userId: string,
  courseName: string
): Promise<{ questions: AssessmentQuestion[] | null; error?: string }> {
  try {
    logger.log(` Getting latest assessment questions for user ${userId}, course: ${courseName}`);

    // First try to get from course session
    const { data: sessionData } = await supabase
      .from('course_sessions')
      .select('generated_questions')
      .eq('user_id', userId)
      .eq('course_name', courseName)
      .maybeSingle();

    if (sessionData?.generated_questions) {
      logger.log(` Found questions in course session for ${courseName}`);
      return { questions: sessionData.generated_questions };
    }

    // If not in session, get from history
    const { data: historyData, error } = await supabase
      .from('assessment_questions_history')
      .select('questions')
      .eq('user_id', userId)
      .eq('course_name', courseName)
      .order('generated_at', { ascending: false })
      .limit(1)
      .maybeSingle();

    if (error && error.code !== 'PGRST116') {
      logger.error(' Error getting assessment questions:', error);
      return { questions: null, error: error.message };
    }

    if (!historyData) {
      logger.log(`No assessment questions found for ${courseName}`);
      return { questions: null };
    }

    logger.log(` Found questions in history for ${courseName}`);
    return { questions: historyData.questions };

  } catch (error) {
    logger.error(' Unexpected error getting assessment questions:', error);
    return { questions: null, error: 'Unexpected error occurred' };
  }
}

/**
 * Delete a course session (when user wants to restart)
 */
export async function deleteCourseSession(
  userId: string,
  courseName: string
): Promise<{ success: boolean; error?: string }> {
  try {
    logger.log(` Deleting course session for user ${userId}, course: ${courseName}`);

    // Delete from course_sessions
    const { error: sessionError } = await supabase
      .from('course_sessions')
      .delete()
      .eq('user_id', userId)
      .eq('course_name', courseName);

    if (sessionError) {
      logger.error(' Error deleting course session:', sessionError);
      return { success: false, error: sessionError.message };
    }

    // Update my_courses status to indicate restart
    await updateMyCourse(userId, courseName, {
      status: 'in_progress',
      currentStep: 'selection',
      progressPercentage: 0,
      assessmentScore: null,
      hasGeneratedQuestions: false,
      questionsGeneratedAt: null,
      completedAt: null,
    });

    const persistenceKey = buildPersistenceKey(userId, courseName);
    lastCourseSessionFingerprintByKey.delete(persistenceKey);

    logger.log(` Course session deleted for ${courseName}`);
    if (typeof window !== 'undefined') {
      window.dispatchEvent(new CustomEvent('getcert:course_updated', { detail: { courseName } }));
    }
    return { success: true };

  } catch (error) {
    logger.error(' Unexpected error deleting course session:', error);
    return { success: false, error: 'Unexpected error occurred' };
  }
}

/**
 * Calculate progress percentage based on current step
 */
export function calculateProgressPercentage(currentStep: string): number {
  const stepProgress = {
    'selection': 0,
    'loading': 10,
    'learning': 40,
    'assessment': 70,
    'results': 85,
    'project': 100
  };

  return stepProgress[currentStep as keyof typeof stepProgress] || 0;
}

/**
 * Auto-save course session (to be called on state changes)
 */
export async function autoSaveCourseSession(
  userId: string,
  courseName: string,
  flowState: CertificationFlowState,
  additionalData: {
    learningModules?: any;
    generatedQuestions?: any;
    assessmentAnswers?: string;
    projectData?: any;
  } = {}
): Promise<void> {
  const currentStep = flowState.currentStep;
  let progressPercentage = calculateProgressPercentage(currentStep);
  let status: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused' = 'in_progress';

  if (currentStep === 'results') {
    const assessmentScore = flowState.assessmentScore || 0;
    const assessmentPassed = assessmentScore >= 60;
    progressPercentage = assessmentPassed ? 85 : 60;
    status = assessmentPassed ? 'assessment_completed' : 'in_progress';
  } else if (currentStep === 'project') {
    if (flowState.issuedCertificate) {
      progressPercentage = 100;
      status = 'certified';
    } else {
      progressPercentage = 95;
      status = 'project_submitted';
    }
  } else if (currentStep === 'assessment') {
    progressPercentage = 70;
    status = 'in_progress';
  } else if (currentStep === 'learning') {
    progressPercentage = 40;
    status = 'in_progress';
  } else if (currentStep === 'loading') {
    progressPercentage = 10;
    status = 'in_progress';
  }
  const hasPersistableLearningState = Boolean(
    flowState.courseData ||
    additionalData.learningModules ||
    flowState.assessmentPack ||
    flowState.projectData ||
    additionalData.generatedQuestions ||
    additionalData.projectData
  );

  if (currentStep === 'loading' && !hasPersistableLearningState) {
    logger.log(` Skipping transient loading session save for ${courseName}`);
  } else {
    // Save the full session
    await saveCourseSession(userId, courseName, {
      flowState,
      currentStep,
      progressPercentage,
      ...additionalData
    });
  }

    const certificationTrack = flowState.certificationTrack || 'verifiedcert';

    // Update My Courses dashboard
  await updateMyCourse(userId, courseName, {
    status,
    currentStep,
    progressPercentage,
    certificationTrack,
  });
}