import { createClient } from '@supabase/supabase-js';
import { SUPABASE_URL, SUPABASE_ANON_KEY } from 'astro:env/client';

export type SpeakingRecord = Record<string, unknown> & {
  id: number | string;
  task_type: string;
  task_description?: string;
  rephrased_task?: string;
  vocabulary?: string[] | null;
  response?: string;
  response_alternative?: string;
  brainstorm?: Array<{title: string, description: string}> | null;
  brainstorm_alternative?: Array<{title: string, description: string}> | null;
  created_at?: string;
  updated_at?: string;
};

// Initialize Supabase client
let supabase: ReturnType<typeof createClient> | null = null;

function getSupabaseClient() {
  if (!supabase) {
    if (!SUPABASE_URL || !SUPABASE_ANON_KEY) {
      throw new Error('Missing Supabase configuration');
    }
    supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);
  }
  return supabase;
}

async function loadFromDatabase(
  tableName: 'speaking_tasks' | 'writing_tasks',
  taskType?: string
): Promise<SpeakingRecord[]> {
  try {
    const client = getSupabaseClient();
    let query = client.from(tableName).select('*');

    // Add task type filter if specified
    if (taskType) {
      query = query.eq('task_type', taskType);
    }

    const { data, error } = await query.order('id', { ascending: true });

    if (error) {
      // If table doesn't exist (PGRST205 error), return empty array for writing_tasks
      // but log the error for speaking_tasks since it should exist
      if (error.code === 'PGRST205' && tableName === 'writing_tasks') {
        console.log(`Writing tasks table not found - returning empty array (table not created yet)`);
        return [];
      }
      console.error(`Error loading data from ${tableName}:`, error);
      return [];
    }

    return data || [];
  } catch (error) {
    console.error(`Database connection error for ${tableName}:`, error);
    return [];
  }
}

export async function loadSpeakingData(taskType?: string): Promise<SpeakingRecord[]> {
  return loadFromDatabase('speaking_tasks', taskType);
}

export async function loadWritingData(taskType?: string): Promise<SpeakingRecord[]> {
  return loadFromDatabase('writing_tasks', taskType);
}

// Legacy functions for backward compatibility
export async function loadAllSpeakingData(): Promise<SpeakingRecord[]> {
  return loadSpeakingData();
}

export async function loadAllWritingData(): Promise<SpeakingRecord[]> {
  return loadWritingData();
}

// Utility function to get task by ID and type
export async function getTaskById(
  tableName: 'speaking_tasks' | 'writing_tasks',
  id: number | string,
  taskType: string
): Promise<SpeakingRecord | null> {
  try {
    const client = getSupabaseClient();
    const { data, error } = await client
      .from(tableName)
      .select('*')
      .eq('id', id)
      .eq('task_type', taskType)
      .single();

    if (error) {
      console.error(`Error fetching task ${id}-${taskType}:`, error);
      return null;
    }

    return data;
  } catch (error) {
    console.error(`Database connection error for task fetch:`, error);
    return null;
  }
}

// Utility function to count tasks
export async function getTaskCount(tableName: 'speaking_tasks' | 'writing_tasks'): Promise<number> {
  try {
    const client = getSupabaseClient();
    const { count, error } = await client
      .from(tableName)
      .select('*', { count: 'exact', head: true });

    if (error) {
      console.error(`Error counting tasks in ${tableName}:`, error);
      return 0;
    }

    return count || 0;
  } catch (error) {
    console.error(`Database connection error for task count:`, error);
    return 0;
  }
}