import { IStorage } from "./types";
import { db } from "./db";
import { eq, desc, asc, and } from "drizzle-orm";
import { users, events, sessions, speakers, sessionSpeakers, questions, news, media, transcriptions, polls, pollTypes, pollQuestions, pollResponses, notificationChannels, settings, userSessions } from "@shared/schema";
import type { User, Event, Session, Speaker, SessionSpeaker, Question, News, Media, InsertUser, InsertEvent, InsertSession, InsertSpeaker, InsertNews, InsertMedia, InsertQuestion, Transcription, InsertTranscription, Poll, InsertPoll, PollType, PollQuestion, InsertPollQuestion, PollResponse, InsertPollResponse, NotificationChannel, Settings, InsertSettings } from "@shared/schema";
import session from "express-session";
import connectPg from "connect-pg-simple";
import { pool } from "./db";
import { sql } from "drizzle-orm";

const PostgresSessionStore = connectPg(session);

export interface IStorage {
  // ... existing interface methods ...
  getTranscriptions(sessionId?: number, status?: string): Promise<Transcription[]>;
  createTranscription(data: InsertTranscription): Promise<Transcription>;
  updateTranscription(id: number, data: Partial<Transcription>): Promise<Transcription>;
  getPollResponsesByUser(userId: number): Promise<PollResponse[]>;
  removeUserFromAllSessions(userId: number): Promise<void>;
  assignUserToSession(userId: number, sessionId: number): Promise<void>;
  getUserSessions(userId: number): Promise<Session[]>;
  getUserQuestions(userId: number): Promise<Question[]>; // Added new method
  getLiveStreamSessions(): Promise<Session[]>; //added new method
}

export class DatabaseStorage implements IStorage {
  sessionStore: session.Store;

  constructor() {
    this.sessionStore = new PostgresSessionStore({
      pool,
      createTableIfMissing: true,
    });
  }

  async getSettings(): Promise<Settings | undefined> {
    try {
      console.log('Fetching settings');
      const [settingsRow] = await db.select().from(settings).limit(1);

      if (!settingsRow) {
        console.log('No settings found, creating defaults');
        return this.createDefaultSettings();
      }

      console.log('Found existing settings');
      return settingsRow;
    } catch (error) {
      console.error('Error fetching settings:', error);
      throw new Error('Failed to fetch settings. Please try again.');
    }
  }

  private async createDefaultSettings(): Promise<Settings> {
    try {
      // First check if any settings already exist
      const [existingSettings] = await db.select().from(settings).limit(1);
      if (existingSettings) {
        return existingSettings;
      }

      const defaultSettings = {
        siteTitle: 'Event Management Platform',
        siteTitleAr: 'منصة إدارة الفعاليات',
        logoText: 'Event Manager',
        logoTextAr: 'مدير الفعاليات',
        homeBannerTitle: 'Welcome to Event Management',
        homeBannerTitleAr: 'مرحباً بكم في إدارة الفعاليات',
        homeBannerDescription: 'Manage your events efficiently',
        homeBannerDescriptionAr: 'إدارة فعالياتك بكفاءة',
        authWelcomeTitle: 'Welcome Back',
        authWelcomeTitleAr: 'مرحباً بعودتك',
        authDescription: 'Sign in to manage your events',
        authDescriptionAr: 'قم بتسجيل الدخول لإدارة فعالياتك',
        openaiApiKey: process.env.OPENAI_API_KEY,
        firebaseApiKey: process.env.FIREBASE_API_KEY,
        firebaseProjectId: process.env.FIREBASE_PROJECT_ID,
        firebaseAppId: process.env.FIREBASE_APP_ID,
        firebaseMessagingSenderId: process.env.VITE_FIREBASE_MESSAGING_SENDER_ID,
        firebaseVapidKey: process.env.VITE_FIREBASE_VAPID_KEY,
        pusherAppId: process.env.PUSHER_APP_ID,
        pusherKey: process.env.PUSHER_KEY,
        pusherSecret: process.env.PUSHER_SECRET,
        pusherCluster: process.env.PUSHER_CLUSTER,
        deepgramApiKey: process.env.DEEPGRAM_API_KEY
      };

      console.log('Creating new settings with default values and environment variables');

      const [newSettings] = await db.insert(settings)
        .values(defaultSettings as InsertSettings)
        .returning();

      return newSettings;
    } catch (error) {
      console.error('Error creating default settings:', error);
      throw new Error('Failed to create default settings');
    }
  }

  async updateSettings(data: Partial<Settings>): Promise<Settings> {
    try {
      // Get the existing settings first
      const [existingSettings] = await db.select().from(settings).limit(1);

      if (!existingSettings) {
        // If no settings exist, create new ones with the provided data and environment variables
        const defaultSettings = {
          siteTitle: data.siteTitle || 'Event Management Platform',
          siteTitleAr: data.siteTitleAr || 'منصة إدارة الفعاليات',
          logoText: data.logoText || 'Event Manager',
          logoTextAr: data.logoTextAr || 'مدير الفعاليات',
          homeBannerTitle: data.homeBannerTitle || 'Welcome to Event Management',
          homeBannerTitleAr: data.homeBannerTitleAr || 'مرحباً بكم في إدارة الفعاليات',
          homeBannerDescription: data.homeBannerDescription || 'Manage your events efficiently',
          homeBannerDescriptionAr: data.homeBannerDescriptionAr || 'إدارة فعالياتك بكفاءة',
          authWelcomeTitle: data.authWelcomeTitle || 'Welcome Back',
          authWelcomeTitleAr: data.authWelcomeTitleAr || 'مرحباً بعودتك',
          authDescription: data.authDescription || 'Sign in to manage your events',
          authDescriptionAr: data.authDescriptionAr || 'قم بتسجيل الدخول لإدارة فعالياتك',
          openaiApiKey: process.env.OPENAI_API_KEY,
          firebaseApiKey: process.env.FIREBASE_API_KEY,
          firebaseProjectId: process.env.FIREBASE_PROJECT_ID,
          firebaseAppId: process.env.FIREBASE_APP_ID,
          firebaseMessagingSenderId: process.env.VITE_FIREBASE_MESSAGING_SENDER_ID,
          firebaseVapidKey: process.env.VITE_FIREBASE_VAPID_KEY,
          pusherAppId: process.env.PUSHER_APP_ID,
          pusherKey: process.env.PUSHER_KEY,
          pusherSecret: process.env.PUSHER_SECRET,
          pusherCluster: process.env.PUSHER_CLUSTER,
          deepgramApiKey: process.env.DEEPGRAM_API_KEY
        };

        const [newSettings] = await db.insert(settings)
          .values(defaultSettings as InsertSettings)
          .returning();
        return newSettings;
      }

      // Keep existing API keys unless new ones are provided
      const updatedData = { ...data };
      const apiKeys = [
        'openaiApiKey',
        'firebaseApiKey',
        'firebaseProjectId',
        'firebaseAppId',
        'firebaseMessagingSenderId',
        'firebaseVapidKey',
        'pusherAppId',
        'pusherKey',
        'pusherSecret',
        'pusherCluster',
        'deepgramApiKey'
      ];

      apiKeys.forEach(key => {
        if (!updatedData[key] && existingSettings[key]) {
          updatedData[key] = existingSettings[key];
        }
      });

      // Update existing settings
      const [updatedSettings] = await db
        .update(settings)
        .set({
          ...updatedData,
          updatedAt: new Date()
        })
        .where(eq(settings.id, existingSettings.id))
        .returning();

      return updatedSettings;
    } catch (error) {
      console.error('Error updating settings:', error);
      throw new Error('Failed to update settings. Please try again.');
    }
  }

  // User operations
  async getUser(id: number): Promise<User | undefined> {
    try {
      console.log(`Fetching user with ID: ${id}`);
      const [user] = await db.select().from(users).where(eq(users.id, id));
      console.log(`User found:`, !!user);
      return user;
    } catch (error) {
      console.error(`Error fetching user ${id}:`, error);
      throw error;
    }
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    try {
      console.log(`Fetching user with username: ${username}`);
      const [user] = await db.select().from(users).where(eq(users.username, username));
      console.log(`User found:`, !!user);
      return user;
    } catch (error) {
      console.error(`Error fetching user by username ${username}:`, error);
      throw error;
    }
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const [user] = await db.insert(users).values(insertUser).returning();
    return user;
  }

  async getUsers(): Promise<User[]> {
    try {
      console.log('Fetching users ordered by ID');
      const usersList = await db.select().from(users).orderBy(asc(users.id));
      console.log(`Found ${usersList.length} users`);
      return usersList;
    } catch (error) {
      console.error('Error fetching users:', error);
      throw error;
    }
  }

  async updateUser(id: number, data: Partial<User>): Promise<User> {
    try {
      console.log(`Updating user ${id} with data:`, data);
      const [user] = await db
        .update(users)
        .set({
          ...data,
          sessionId: data.sessionId === null ? null : data.sessionId,
          updatedAt: new Date()
        })
        .where(eq(users.id, id))
        .returning();
      console.log(`Updated user:`, user);
      return user;
    } catch (error) {
      console.error(`Error updating user ${id}:`, error);
      throw error;
    }
  }

  async deleteUser(id: number): Promise<void> {
    await db.delete(users).where(eq(users.id, id));
  }

  // Event operations
  async getEvents(): Promise<Event[]> {
    return await db.select().from(events);
  }

  async getEvent(id: number): Promise<Event | undefined> {
    const [event] = await db.select().from(events).where(eq(events.id, id));
    return event;
  }

  async createEvent(insertEvent: InsertEvent): Promise<Event> {
    const [event] = await db.insert(events).values(insertEvent).returning();
    return event;
  }

  async updateEvent(id: number, data: Partial<Event>): Promise<Event> {
    const [event] = await db
      .update(events)
      .set(data)
      .where(eq(events.id, id))
      .returning();
    return event;
  }

  async deleteEvent(id: number): Promise<void> {
    try {
      // First delete all media records associated with this event
      await db.delete(media).where(eq(media.eventId, id));

      // Then remove all sessions associated with this event
      const eventSessions = await this.getSessions(id);
      for (const session of eventSessions) {
        // Remove session speakers first
        await this.removeSessionSpeakers(session.id);
        // Then delete the session
        await db.delete(sessions).where(eq(sessions.id, session.id));
      }

      // Finally delete the event
      await db.delete(events).where(eq(events.id, id));
    } catch (error) {
      console.error('Error deleting event:', error);
      throw error;
    }
  }

  // Session operations
  async getSessions(eventId?: number): Promise<Session[]> {
    if (eventId) {
      return await db.select().from(sessions).where(eq(sessions.eventId, eventId));
    }
    return await db.select().from(sessions);
  }

  async createSession(insertSession: InsertSession): Promise<Session> {
    const [session] = await db.insert(sessions).values(insertSession).returning();
    return session;
  }

  async updateSession(id: number, data: Partial<Session>): Promise<Session> {
    const [session] = await db
      .update(sessions)
      .set(data)
      .where(eq(sessions.id, id))
      .returning();
    return session;
  }

  // Speaker operations
  async getSpeakers(): Promise<Speaker[]> {
    return await db.select().from(speakers);
  }

  async createSpeaker(insertSpeaker: InsertSpeaker): Promise<Speaker> {
    const [speaker] = await db.insert(speakers).values(insertSpeaker).returning();
    return speaker;
  }

  async updateSpeaker(id: number, data: Partial<Speaker>): Promise<Speaker> {
    const [speaker] = await db
      .update(speakers)
      .set(data)
      .where(eq(speakers.id, id))
      .returning();
    return speaker;
  }

  async deleteSpeaker(id: number): Promise<void> {
    // First remove speaker associations from sessions
    await db.delete(sessionSpeakers).where(eq(sessionSpeakers.speakerId, id));
    // Then delete the speaker
    await db.delete(speakers).where(eq(speakers.id, id));
  }

  // Session-Speaker operations
  async assignSpeakerToSession(sessionId: number, speakerId: number): Promise<SessionSpeaker> {
    const [assignment] = await db
      .insert(sessionSpeakers)
      .values({ sessionId, speakerId })
      .returning();
    return assignment;
  }

  async getSessionSpeakers(sessionId: number): Promise<Speaker[]> {
    const result = await db
      .select({
        id: speakers.id,
        name: speakers.name,
        nameAr: speakers.nameAr,
        bio: speakers.bio,
        bioAr: speakers.bioAr,
        imageUrl: speakers.imageUrl,
        title: speakers.title,
        titleAr: speakers.titleAr,
        organization: speakers.organization,
        organizationAr: speakers.organizationAr,
        position: speakers.position,
        positionAr: speakers.positionAr,
      })
      .from(sessionSpeakers)
      .innerJoin(speakers, eq(sessionSpeakers.speakerId, speakers.id))
      .where(eq(sessionSpeakers.sessionId, sessionId));

    return result;
  }

  // News operations
  async getNews(): Promise<News[]> {
    return await db.select().from(news);
  }

  async createNews(insertNews: InsertNews & { authorId: number }): Promise<News> {
    const [newsItem] = await db.insert(news).values(insertNews).returning();
    return newsItem;
  }

  async updateNews(id: number, data: Partial<News>): Promise<News> {
    const [newsItem] = await db
      .update(news)
      .set({
        ...data,
        updatedAt: new Date()
      })
      .where(eq(news.id, id))
      .returning();
    return newsItem;
  }

  async deleteNews(id: number): Promise<void> {
    await db.delete(news).where(eq(news.id, id));
  }

  // Media operations
  async getMedia(type?: string): Promise<Media[]> {
    if (type) {
      return await db.select().from(media).where(eq(media.type, type));
    }
    return await db.select().from(media);
  }

  async createMedia(insertMedia: InsertMedia & { uploaderId: number }): Promise<Media> {
    const [mediaItem] = await db.insert(media).values(insertMedia).returning();
    return mediaItem;
  }

  async updateMedia(id: number, data: Partial<Media>): Promise<Media> {
    const [mediaItem] = await db
      .update(media)
      .set(data)
      .where(eq(media.id, id))
      .returning();
    return mediaItem;
  }

  async deleteMedia(id: number): Promise<void> {
    await db.delete(media).where(eq(media.id, id));
  }

  // Question operations
  async createQuestion(data: InsertQuestion & { userId: number }): Promise<Question> {
    const [question] = await db.insert(questions).values({
      userId: data.userId,
      sessionId: data.sessionId,
      question: data.question,
      questionAr: data.questionAr,
      status: 'pending',
    }).returning();
    return question;
  }

  async getQuestions(sessionId?: number): Promise<Question[]> {
    try {
      let query = db
        .select({
          id: questions.id,
          userId: questions.userId,
          sessionId: questions.sessionId,
          question: questions.question,
          questionAr: questions.questionAr,
          status: questions.status,
          response: questions.response,
          responseAr: questions.responseAr,
          timestamp: questions.timestamp,
          user: users,
          session: sessions
        })
        .from(questions)
        .leftJoin(users, eq(questions.userId, users.id))
        .leftJoin(sessions, eq(questions.sessionId, sessions.id));

      if (sessionId) {
        query = query.where(eq(questions.sessionId, sessionId));
      }

      return query.orderBy(desc(questions.timestamp));
    } catch (error) {
      console.error('Error fetching questions:', error);
      throw error;
    }
  }

  async updateQuestion(id: number, data: Partial<Question>): Promise<Question> {
    const [question] = await db
      .update(questions)
      .set(data)
      .where(eq(questions.id, id))
      .returning();
    return question;
  }

  // Poll operations
  async getPoll(id: number): Promise<Poll | undefined> {
    try {
      console.log(`Fetching poll with ID: ${id}`);
      const [poll] = await db
        .select()
        .from(polls)
        .where(eq(polls.id, id));

      console.log(`Poll ${id} found:`, poll);
      return poll;
    } catch (error) {
      console.error(`Error fetching poll ${id}:`, error);
      throw error;
    }
  }

  async getPolls(eventId?: number, sessionId?: number): Promise<Poll[]> {
    let query = db.select().from(polls);

    if (eventId) {
      query = query.where(eq(polls.eventId, eventId));
    }
    if (sessionId) {
      query = query.where(eq(polls.sessionId, sessionId));
    }

    return await query.orderBy(polls.createdAt);
  }

  async createPoll(insertPoll: InsertPoll & { creatorId: number }): Promise<Poll> {
    const [poll] = await db.insert(polls)
      .values(insertPoll)
      .returning();
    return poll;
  }

  async updatePoll(id: number, data: Partial<Poll>): Promise<Poll> {
    const [poll] = await db.update(polls)
      .set(data)
      .where(eq(polls.id, id))
      .returning();
    return poll;
  }

  async deletePoll(id: number): Promise<void> {
    try {
      console.log(`Deleting poll ${id} and all related data`);

      // First delete all responses for this poll
      console.log('Deleting poll responses...');
      await db.delete(pollResponses)
        .where(eq(pollResponses.pollId, id));

      // Then delete all questions for this poll
      console.log('Deleting poll questions...');
      await db.delete(pollQuestions)
        .where(eq(pollQuestions.pollId, id));

      // Finally delete the poll itself
      console.log('Deleting poll...');
      await db.delete(polls)
        .where(eq(polls.id, id));

      console.log('Poll and related data deleted successfully');
    } catch (error) {
      console.error(`Error deleting poll ${id}:`, error);
      throw new Error('Failed to delete poll and related data');
    }
  }

  async getPollTypes(): Promise<PollType[]> {
    return await db.select().from(pollTypes);
  }

  async createPollQuestion(data: InsertPollQuestion): Promise<PollQuestion> {
    const [question] = await db.insert(pollQuestions)
      .values(data)
      .returning();
    return question;
  }

  async getPollQuestions(pollId: number): Promise<PollQuestion[]> {
    try {
      console.log(`Fetching questions for poll ${pollId}`);
      const questions = await db
        .select({
          id: pollQuestions.id,
          pollId: pollQuestions.pollId,
          pollTypeId: pollQuestions.pollTypeId,
          question: pollQuestions.question,
          questionAr: pollQuestions.questionAr,
          options: pollQuestions.options,
          optionsAr: pollQuestions.optionsAr,
          required: pollQuestions.required,
          order: pollQuestions.order
        })
        .from(pollQuestions)
        .where(eq(pollQuestions.pollId, pollId))
        .orderBy(pollQuestions.order);

      console.log(`Found ${questions.length} questions for poll ${pollId}:`, questions);
      return questions;
    } catch (error) {
      console.error(`Error fetching questions for poll ${pollId}:`, error);
      throw error;
    }
  }

  async createPollResponse(data: InsertPollResponse): Promise<PollResponse> {
    const [response] = await db.insert(pollResponses)
      .values(data)
      .returning();
    return response;
  }

  // Sessions with speakers
  async getSessionsWithSpeakers(eventId?: number): Promise<(Session & { speakers: Speaker[] })[]> {
    let sessionsQuery = db.select().from(sessions);
    if (eventId) {
      sessionsQuery = sessionsQuery.where(eq(sessions.eventId, eventId));
    }

    const allSessions = await sessionsQuery;
    const result = [];

    for (const session of allSessions) {
      const sessionSpeakersList = await this.getSessionSpeakers(session.id);
      result.push({
        ...session,
        speakers: sessionSpeakersList,
      });
    }

    return result;
  }

  async getSessionWithSpeakers(sessionId: number): Promise<(Session & { speakers: Speaker[] }) | undefined> {
    const [session] = await db.select().from(sessions).where(eq(sessions.id, sessionId));
    if (!session) return undefined;

    const speakers = await this.getSessionSpeakers(sessionId);
    return {
      ...session,
      speakers,
    };
  }

  async getPollResponses(pollId: number): Promise<PollResponse[]> {
    try {
      console.log(`Fetching responses for poll ${pollId}`);
      const responses = await db
        .select()
        .from(pollResponses)
        .where(eq(pollResponses.pollId, pollId));

      console.log(`Found ${responses.length} responses for poll ${pollId}:`, responses);
      return responses;
    } catch (error) {
      console.error(`Error fetching responses for poll ${pollId}:`, error);
      throw error;
    }
  }

  async getPollResponsesByUser(userId: number): Promise<PollResponse[]> {
    try {
      console.log(`Fetching poll responses for user ${userId}`);
      const responses = await db
        .select()
        .from(pollResponses)
        .where(eq(pollResponses.userId, userId))
        .orderBy(desc(pollResponses.createdAt));

      console.log(`Found ${responses.length} responses for user ${userId}`);
      return responses;
    } catch (error) {
      console.error(`Error fetching responses for user ${userId}:`, error);
      throw error;
    }
  }

  async removeUserFromAllSessions(userId: number): Promise<void> {
    try {
      console.log(`Removing all session assignments for user ${userId}`);
      await db.delete(userSessions)
        .where(eq(userSessions.userId, userId));
      console.log("Successfully removed all session assignments");
    } catch (error) {
      console.error("Error removing user session assignments:", error);
      throw new Error("Failed to remove user session assignments");
    }
  }

  async assignUserToSession(userId: number, sessionId: number): Promise<void> {
    try {
      console.log(`Assigning user ${userId} to session ${sessionId}`);
      await db.insert(userSessions)
        .values({
          userId,
          sessionId,
          createdAt: new Date(),
        });
      console.log("Successfully assigned user to session");
    } catch (error) {
      console.error("Error assigning user to session:", error);
      throw new Error("Failed to assign user to session");
    }
  }

  async storeNotificationChannel(userId: number, channelName: string): Promise<void> {
    try {
      console.log(`Storing notification channel for user ${userId} with channel name ${channelName}`);
      const [existingChannel] = await db
        .select()
        .from(notificationChannels)
        .where(eq(notificationChannels.channelName, channelName));

      if (existingChannel) {
        console.log('Channel already exists, updating lastUsed timestamp');
        await db
          .update(notificationChannels)
          .set({ lastUsed: new Date() })
          .where(eq(notificationChannels.channelName, channelName));
        return;
      }

      await db.insert(notificationChannels).values({
        userId,
        channelName,
        createdAt: new Date(),
        lastUsed: new Date()
      });
      console.log("Notification channel stored successfully");
    } catch (error) {
      console.error("Error storing notification channel:", error);
      throw new Error(`Failed to store notification channel: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  async getAllNotificationChannels(): Promise<NotificationChannel[]> {
    try {
      console.log("Fetching all notification channels");
      const channels = await db
        .select()
        .from(notificationChannels)
        .orderBy(desc(notificationChannels.lastUsed));
      console.log(`Found ${channels.length} notification channels`);
      return channels;
    } catch (error) {
      console.error("Error fetching notification channels:", error);
      throw new Error(`Failed to fetch notification channels: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  async removeNotificationChannel(channelName: string): Promise<void> {
    try {
      console.log(`Removing notification channel: ${channelName}`);
      await db
        .delete(notificationChannels)
        .where(eq(notificationChannels.channelName, channelName));
      console.log("Notification channel removed successfully");
    } catch (error) {
      console.error("Error removing notification channel:", error);
      throw new Error(`Failed to remove notification channel: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  async removeSessionSpeakers(sessionId: number): Promise<void> {
    await db.delete(sessionSpeakers)
      .where(eq(sessionSpeakers.sessionId, sessionId));
  }

  async deleteSession(id: number): Promise<void> {
    // First remove speaker associations
    await this.removeSessionSpeakers(id);
    // Then delete the session
    await db.delete(sessions).where(eq(sessions.id, id));
  }

  // Add transcription methods
  async getTranscriptions(sessionId?: number, status?: string): Promise<Transcription[]> {
    try {
      console.log('Fetching transcriptions for session:', sessionId, 'with status:', status);
      let query = db
        .select({
          id: transcriptions.id,
          originalText: transcriptions.originalText,
          translatedText: transcriptions.translatedText,
          clipStartTime: transcriptions.clipStartTime,
          clipEndTime: transcriptions.clipEndTime,
          sourceLanguage: transcriptions.sourceLanguage,
          sessionId: transcriptions.sessionId,
          status: transcriptions.status,
          createdAt: transcriptions.createdAt
        })
        .from(transcriptions);

      if (sessionId) {
        query = query.where(eq(transcriptions.sessionId, sessionId));
      }

      if (status) {
        query = query.where(eq(transcriptions.status, status));
      }

      const results = await query.orderBy(desc(transcriptions.clipStartTime));
      console.log(`Found ${results.length} transcriptions`);
      return results;
    } catch (error) {
      console.error("Error fetching transcriptions:", error);
      throw new Error('Failed to fetch transcriptions');
    }
  }

  async createTranscription(data: InsertTranscription): Promise<Transcription> {
    try {
      const [transcription] = await db
        .insert(transcriptions)
        .values(data)
        .returning();
      return transcription;
    } catch (error) {
      console.error("Error creating transcription:", error);
      throw new Error("Failed to create transcription");
    }
  }

  async updateTranscription(id: number, data: Partial<Transcription>): Promise<Transcription> {
    try {
      const [transcription] = await db
        .update(transcriptions)
        .set(data)
        .where(eq(transcriptions.id, id))
        .returning();
      return transcription;
    } catch (error) {
      console.error("Error updating transcription:", error);
      throw new Error("Failed to update transcription");
    }
  }
  async getUserSessions(userId: number): Promise<Session[]> {
    try {
      console.log(`Fetching sessions for user ${userId}`);
      const userSessionsList = await db
        .select({
          id: sessions.id,
          eventId: sessions.eventId,
          sessionTitle: sessions.sessionTitle,
          sessionTitleAr: sessions.sessionTitleAr,
          description: sessions.description,
          descriptionAr: sessions.descriptionAr,
          startTime: sessions.startTime,
          endTime: sessions.endTime,
          sessionType: sessions.sessionType,
          liveStreamUrl: sessions.liveStreamUrl,
          recordingUrl: sessions.recordingUrl,
          createdAt: sessions.createdAt,
          updatedAt: sessions.updatedAt
        })
        .from(userSessions)
        .innerJoin(sessions, eq(sessions.id, userSessions.sessionId))
        .where(eq(userSessions.userId, userId));

      console.log(`Found ${userSessionsList.length} sessions for user ${userId}`);
      return userSessionsList;
    } catch (error) {
      console.error(`Error fetching sessions for user ${userId}:`, error);
      throw new Error("Failed to fetch user sessions");
    }
  }

  async getUserQuestions(userId: number): Promise<Question[]> {
    try {
      console.log(`Fetching questions for user ${userId}`);
      return await db
        .select()
        .from(questions)
        .where(eq(questions.userId, userId))
        .orderBy(desc(questions.timestamp));
    } catch (error) {
      console.error(`Error fetching questions for user ${userId}:`, error);
      throw new Error('Failed to fetch user questions');
    }
  }

  async getLiveStreamSessions(): Promise<Session[]> {
    try {
      console.log('Fetching live stream sessions');
      const liveSessions = await db
        .select({
          id: sessions.id,
          eventId: sessions.eventId,
          sessionTitle: sessions.sessionTitle,
          sessionTitleAr: sessions.sessionTitleAr,
          venue: sessions.venue,
          venueAr: sessions.venueAr,
          sessionDate: sessions.sessionDate,
          sessionTime: sessions.sessionTime,
          liveStreamUrl: sessions.liveStreamUrl,
          sessionType: sessions.sessionType
        })
        .from(sessions)
        .where(sql`${sessions.liveStreamUrl} IS NOT NULL AND ${sessions.liveStreamUrl} != ''`)
        .orderBy(asc(sessions.sessionDate), asc(sessions.sessionTime));

      console.log(`Found ${liveSessions.length} live stream sessions:`, liveSessions);
      return liveSessions;
    } catch (error) {
      console.error('Error fetching live stream sessions:', error);
      throw new Error('Failed to fetch live stream sessions');
    }
  }

  // Keep existing getTranscriptions method which already handles sessionId
  async getQuestionsByUserAndSession(userId: number, sessionId: number): Promise<Question[]> {
    try {
      console.log(`Fetching questions for user ${userId} in session ${sessionId}`);
      return await db
        .select()
        .from(questions)
        .where(
          and(
            eq(questions.userId, userId),
            eq(questions.sessionId, sessionId)
          )
        )
        .orderBy(desc(questions.timestamp));
    } catch (error) {
      console.error(`Error fetching questions for user ${userId} in session ${sessionId}:`, error);
      throw new Error('Failed to fetch user session questions');
    }
  }
}

export const storage = new DatabaseStorage();