import { pgTable, text, serial, integer, boolean, timestamp, date, time } from "drizzle-orm/pg-core";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";
import { relations } from "drizzle-orm";

// Users with enhanced fields
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  username: text("username").notNull().unique(),
  password: text("password").notNull(),
  name: text("name").notNull(),
  organization: text("organization").notNull(),
  position: text("position").notNull(),
  phoneNumber: text("phone_number"),
  email: text("email").notNull(),
  role: text("role").notNull().default("attendee"),
  isActive: boolean("is_active").notNull().default(true),
  profileImage: text("profile_image"),
  sessionId: integer("session_id").references(() => sessions.id, { onDelete: 'set null' }), // Columns are nullable by default in Drizzle
  createdAt: timestamp("created_at").notNull().defaultNow(),
  lastLogin: timestamp("last_login"),
});

// Update the user roles array
export const userRoles = ["admin", "moderator", "organizer", "attendee"] as const;
export type UserRole = typeof userRoles[number];

// Add settings table right after the users table
export const settings = pgTable("settings", {
  id: serial("id").primaryKey(),
  // Site Configuration
  siteTitle: text("site_title").notNull(),
  siteTitleAr: text("site_title_ar").notNull(),
  logoText: text("logo_text").notNull(),
  logoTextAr: text("logo_text_ar").notNull(),

  // Homepage Configuration
  homeBannerTitle: text("home_banner_title").notNull(),
  homeBannerTitleAr: text("home_banner_title_ar").notNull(),
  homeBannerDescription: text("home_banner_description").notNull(),
  homeBannerDescriptionAr: text("home_banner_description_ar").notNull(),

  // Auth Page Configuration
  authWelcomeTitle: text("auth_welcome_title").notNull(),
  authWelcomeTitleAr: text("auth_welcome_title_ar").notNull(),
  authDescription: text("auth_description").notNull(),
  authDescriptionAr: text("auth_description_ar").notNull(),

  // Stats Cards
  statsCard1Title: text("stats_card1_title").notNull().default("50+ Speakers"),
  statsCard1TitleAr: text("stats_card1_title_ar").notNull().default("50+ متحدث"),
  statsCard1Description: text("stats_card1_description").notNull().default("Industry leaders"),
  statsCard1DescriptionAr: text("stats_card1_description_ar").notNull().default("قادة الصناعة"),
  statsCard2Title: text("stats_card2_title").notNull().default("3 Days"),
  statsCard2TitleAr: text("stats_card2_title_ar").notNull().default("3 أيام"),
  statsCard2Description: text("stats_card2_description").notNull().default("Of networking"),
  statsCard2DescriptionAr: text("stats_card2_description_ar").notNull().default("من التواصل"),
  enableRegistration: boolean("enable_registration").notNull().default(true),

  // Privacy Policy
  privacyPolicyTitle: text("privacy_policy_title").notNull().default("Privacy Policy"),
  privacyPolicyTitleAr: text("privacy_policy_title_ar").notNull().default("سياسة الخصوصية"),
  privacyPolicyContent: text("privacy_policy_content").notNull().default("Enter your privacy policy content here."),
  privacyPolicyContentAr: text("privacy_policy_content_ar").notNull().default("أدخل محتوى سياسة الخصوصية هنا."),

  // API Keys
  openaiApiKey: text("openai_api_key"),
  firebaseApiKey: text("firebase_api_key"),
  firebaseProjectId: text("firebase_project_id"),
  firebaseAppId: text("firebase_app_id"),
  firebaseMessagingSenderId: text("firebase_messaging_sender_id"),
  firebaseVapidKey: text("firebase_vapid_key"),
  pusherAppId: text("pusher_app_id"),
  pusherKey: text("pusher_key"),
  pusherSecret: text("pusher_secret"),
  pusherCluster: text("pusher_cluster"),
  deepgramApiKey: text("deepgram_api_key"),

  // SendGrid fields
  sendgridApiKey: text("sendgrid_api_key"),
  sendgridFromEmail: text("sendgrid_from_email"),

  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

// Add notification channels table for Pusher
export const notificationChannels = pgTable("notification_channels", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id),
  channelName: text("channel_name").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  lastUsed: timestamp("last_used"),
});

// Add the FCM tokens table definition near the notification channels table
export const fcmTokens = pgTable("fcm_tokens", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id, { onDelete: 'cascade' }),
  token: text("token").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// News articles
export const news = pgTable("news", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  titleAr: text("title_ar").notNull(),
  content: text("content").notNull(),
  contentAr: text("content_ar").notNull(),
  image: text("image"),
  authorId: integer("author_id").notNull().references(() => users.id),
  publishDate: timestamp("publish_date").notNull(),
  isPublished: boolean("is_published").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// Media items (for gallery and videos)
export const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: text("type").notNull(), // 'image' or 'video'
  title: text("title").notNull(),
  titleAr: text("title_ar").notNull(),
  description: text("description"),
  descriptionAr: text("description_ar"),
  url: text("url").notNull(),
  link: text("link"), // New field for mobile video URL
  thumbnail: text("thumbnail"),
  uploaderId: integer("uploader_id").notNull().references(() => users.id),
  eventId: integer("event_id").references(() => events.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// Keep existing tables
export const events = pgTable("events", {
  id: serial("id").primaryKey(),
  eventName: text("event_name").notNull(),
  eventNameAr: text("event_name_ar").notNull(),
  eventDescription: text("event_description").notNull(),
  eventDescriptionAr: text("event_description_ar").notNull(),
  startDate: date("start_date").notNull(),
  endDate: date("end_date").notNull(),
  location: text("location").notNull(),
  locationAr: text("location_ar").notNull(),
  eventLogo: text("event_logo"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// Add the sessionType to the sessions table definition
export const sessions = pgTable("sessions", {
  id: serial("id").primaryKey(),
  eventId: integer("event_id").notNull().references(() => events.id),
  sessionTitle: text("session_title").notNull(),
  sessionTitleAr: text("session_title_ar").notNull(),
  sessionDescription: text("session_description").notNull(),
  sessionDescriptionAr: text("session_description_ar").notNull(),
  venue: text("venue").notNull(),
  venueAr: text("venue_ar").notNull(),
  sessionDate: date("session_date").notNull(),
  sessionTime: time("session_time").notNull(),
  liveStreamUrl: text("live_stream_url"),
  sessionType: text("session_type").notNull().default("main"), // Add session type field
});

// Update the userSessions table to ensure proper relations
export const userSessions = pgTable("user_sessions", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id, { onDelete: 'cascade' }),
  sessionId: integer("session_id").notNull().references(() => sessions.id, { onDelete: 'cascade' }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const speakers = pgTable("speakers", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  nameAr: text("name_ar").notNull(),
  bio: text("bio").notNull(),
  bioAr: text("bio_ar").notNull(),
  imageUrl: text("image_url").notNull(),
  title: text("title").notNull(),
  titleAr: text("title_ar").notNull(),
  organization: text("organization").notNull(),
  organizationAr: text("organization_ar").notNull(),
  position: text("position").notNull(),
  positionAr: text("position_ar").notNull(),
});

export const sessionSpeakers = pgTable("session_speakers", {
  id: serial("id").primaryKey(),
  sessionId: integer("session_id").notNull().references(() => sessions.id),
  speakerId: integer("speaker_id").notNull().references(() => speakers.id),
});

// Update questions table to support session questions and bilingual content
export const questions = pgTable("questions", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id),
  sessionId: integer("session_id").references(() => sessions.id), // Optional for general questions
  question: text("question").notNull(),
  questionAr: text("question_ar").notNull(),
  status: text("status").notNull().default("pending"), // pending, approved, rejected
  response: text("response"),
  responseAr: text("response_ar"),
  timestamp: timestamp("timestamp").notNull().defaultNow(),
});

export const transcriptions = pgTable("transcriptions", {
  id: serial("id").primaryKey(),
  sessionId: integer("session_id").notNull().references(() => sessions.id),
  status: text("status").notNull().default("pending"), // pending, accepted, rejected
  sourceLanguage: text("source_language").notNull(), // detected by Deepgram
  originalText: text("original_text").notNull(),
  translatedText: text("translated_text").notNull(),
  clipStartTime: timestamp("clip_start_time").notNull(),
  clipEndTime: timestamp("clip_end_time").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  rtmpStreamStatus: text("rtmp_stream_status").notNull().default("stopped"), // running, stopped, error
  rtmpErrorMessage: text("rtmp_error_message"),
});

export const pollTypes = pgTable("poll_types", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(), // 'range', 'yesNoMaybe', 'multipleChoice'
  description: text("description").notNull(),
});

export const polls = pgTable("polls", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  titleAr: text("title_ar").notNull(),
  description: text("description").notNull(),
  descriptionAr: text("description_ar").notNull(),
  eventId: integer("event_id").references(() => events.id),
  sessionId: integer("session_id").references(() => sessions.id),
  creatorId: integer("creator_id").notNull().references(() => users.id),
  startDate: timestamp("start_date").notNull(),
  endDate: timestamp("end_date").notNull(),
  isActive: boolean("is_active").notNull().default(true),
  resultsPublished: boolean("results_published").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const pollQuestions = pgTable("poll_questions", {
  id: serial("id").primaryKey(),
  pollId: integer("poll_id").notNull().references(() => polls.id),
  pollTypeId: integer("poll_type_id").notNull().references(() => pollTypes.id),
  question: text("question").notNull(),
  questionAr: text("question_ar").notNull(),
  options: text("options").array(), // For multiple choice questions
  optionsAr: text("options_ar").array(), // Arabic options for multiple choice
  required: boolean("required").notNull().default(true),
  order: integer("order").notNull(),
});

export const pollResponses = pgTable("poll_responses", {
  id: serial("id").primaryKey(),
  pollId: integer("poll_id").notNull().references(() => polls.id),
  questionId: integer("question_id").notNull().references(() => pollQuestions.id),
  userId: integer("user_id").notNull().references(() => users.id),
  numericResponse: integer("numeric_response"), // For range questions (1-5)
  textResponse: text("text_response"), // For multiple choice with notes
  selectedOptions: text("selected_options").array(), // For multiple choice selections
  yesNoResponse: text("yes_no_response"), // 'yes', 'no', 'maybe'
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// Add relations
export const usersRelations = relations(users, ({ many }) => ({
  news: many(news),
  media: many(media),
  notificationChannels: many(notificationChannels),
  sessions: many(userSessions),
  fcmTokens: many(fcmTokens),
}));

export const newsRelations = relations(news, ({ one }) => ({
  author: one(users, {
    fields: [news.authorId],
    references: [users.id],
  }),
}));

export const mediaRelations = relations(media, ({ one }) => ({
  uploader: one(users, {
    fields: [media.uploaderId],
    references: [users.id],
  }),
  event: one(events, {
    fields: [media.eventId],
    references: [events.id],
  }),
}));

export const eventsRelations = relations(events, ({ many }) => ({
  sessions: many(sessions),
  media: many(media),
  polls: many(polls),
}));

export const sessionsRelations = relations(sessions, ({ one, many }) => ({
  event: one(events, {
    fields: [sessions.eventId],
    references: [events.id],
  }),
  speakers: many(sessionSpeakers),
  transcriptions: many(transcriptions),
  polls: many(polls),
  users: many(userSessions),
}));

export const speakersRelations = relations(speakers, ({ many }) => ({
  sessions: many(sessionSpeakers),
}));

export const sessionSpeakersRelations = relations(sessionSpeakers, ({ one }) => ({
  session: one(sessions, {
    fields: [sessionSpeakers.sessionId],
    references: [sessions.id],
  }),
  speaker: one(speakers, {
    fields: [sessionSpeakers.speakerId],
    references: [speakers.id],
  }),
}));

export const questionsRelations = relations(questions, ({ one }) => ({
  user: one(users, {
    fields: [questions.userId],
    references: [users.id],
  }),
  session: one(sessions, {
    fields: [questions.sessionId],
    references: [sessions.id],
  }),
}));

export const transcriptionsRelations = relations(transcriptions, ({ one }) => ({
  session: one(sessions, {
    fields: [transcriptions.sessionId],
    references: [sessions.id],
  }),
}));

export const pollsRelations = relations(polls, ({ one, many }) => ({
  event: one(events, {
    fields: [polls.eventId],
    references: [events.id],
  }),
  session: one(sessions, {
    fields: [polls.sessionId],
    references: [sessions.id],
  }),
  creator: one(users, {
    fields: [polls.creatorId],
    references: [users.id],
  }),
  questions: many(pollQuestions),
}));

export const pollQuestionsRelations = relations(pollQuestions, ({ one }) => ({
  poll: one(polls, {
    fields: [pollQuestions.pollId],
    references: [polls.id],
  }),
  pollType: one(pollTypes, {
    fields: [pollQuestions.pollTypeId],
    references: [pollTypes.id],
  }),
}));

export const pollResponsesRelations = relations(pollResponses, ({ one }) => ({
  poll: one(polls, {
    fields: [pollResponses.pollId],
    references: [polls.id],
  }),
  question: one(pollQuestions, {
    fields: [pollResponses.questionId],
    references: [pollQuestions.id],
  }),
  user: one(users, {
    fields: [pollResponses.userId],
    references: [users.id],
  }),
}));

// Add relations for notification channels
export const notificationChannelsRelations = relations(notificationChannels, ({ one }) => ({
  user: one(users, {
    fields: [notificationChannels.userId],
    references: [users.id],
  }),
}));

// Add FCM tokens relation to users
export const fcmTokensRelations = relations(fcmTokens, ({ one }) => ({
  user: one(users, {
    fields: [fcmTokens.userId],
    references: [users.id],
  }),
}));

// Update the userSessions relations
export const userSessionsRelations = relations(userSessions, ({ one }) => ({
  user: one(users, {
    fields: [userSessions.userId],
    references: [users.id],
  }),
  session: one(sessions, {
    fields: [userSessions.sessionId],
    references: [sessions.id],
  }),
}));


// Add insert schemas
export const insertUserSchema = createInsertSchema(users).pick({
  username: true,
  password: true,
  name: true,
  organization: true,
  position: true,
  phoneNumber: true,
  email: true,
  role: true,
  profileImage: true,
  sessionId: true,
}).extend({
  username: z.string().min(1, "Username is required"),
  password: z.string().min(8, "Password must be at least 8 characters"),
  name: z.string().min(1, "Full name is required"),
  organization: z.string().min(1, "Organization is required"),
  position: z.string().min(1, "Position is required"),
  email: z.string().email("Invalid email address"),
  phoneNumber: z.string().optional(),
  role: z.string().default("attendee"),
  profileImage: z.string().optional(),
  sessionId: z.number().nullable().optional(),
});

// Update the insertNewsSchema to properly handle the date
export const insertNewsSchema = createInsertSchema(news).omit({
  id: true,
  createdAt: true,
  authorId: true,
}).extend({
  publishDate: z.coerce.date(),
  isPublished: z.boolean().default(false),
});

// Update the insert schema for media to include the link field
export const insertMediaSchema = createInsertSchema(media).omit({
  id: true,
  createdAt: true,
  uploaderId: true,
}).extend({
  link: z.string().optional(),
  thumbnail: z.string().optional(),
  eventId: z.number().optional(),
});

export const insertEventSchema = createInsertSchema(events).omit({ id: true, createdAt: true });
// Update the insert schema to include sessionType
export const insertSessionSchema = createInsertSchema(sessions).omit({ id: true }).extend({
  speakerIds: z.array(z.number()).optional(),
  sessionType: z.enum(["main", "breakout"]).default("main"),
});
export const insertSpeakerSchema = createInsertSchema(speakers).omit({ id: true });
export const insertSessionSpeakerSchema = createInsertSchema(sessionSpeakers).omit({ id: true });
// Update question schema
export const insertQuestionSchema = createInsertSchema(questions).pick({
  question: true,
  questionAr: true,
  sessionId: true,
}).extend({
  questionAr: z.string().default(""), // Make questionAr required but default to empty string
  sessionId: z.number().nullable().optional(), // Make sessionId optional and nullable
  userId: z.number().optional(), // Make userId optional as it will be set by the server
});

export const insertTranscriptionSchema = createInsertSchema(transcriptions).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
  rtmpStreamStatus: true,
  rtmpErrorMessage: true,
});

// Update the insertPollSchema to properly handle dates
export const insertPollSchema = createInsertSchema(polls).omit({
  id: true,
  createdAt: true,
}).extend({
  startDate: z.coerce.date(),
  endDate: z.coerce.date(),
});

export const insertPollQuestionSchema = createInsertSchema(pollQuestions).omit({
  id: true,
});

export const insertPollResponseSchema = createInsertSchema(pollResponses).omit({
  id: true,
  createdAt: true,
});

// Add near the bottom with other insert schemas
export const insertSettingsSchema = createInsertSchema(settings).omit({
  id: true,
  updatedAt: true,
}).extend({
  privacyPolicyTitle: z.string().min(1, "Privacy Policy title is required"),
  privacyPolicyTitleAr: z.string().min(1, "Privacy Policy title in Arabic is required"),
  privacyPolicyContent: z.string().min(1, "Privacy Policy content is required"),
  privacyPolicyContentAr: z.string().min(1, "Privacy Policy content in Arabic is required"),
  sendgridApiKey: z.string().optional(),
  sendgridFromEmail: z.string().email("Invalid email address").optional(),
});

// Add types
export type User = typeof users.$inferSelect;
export type News = typeof news.$inferSelect;
export type Media = typeof media.$inferSelect;
export type Event = typeof events.$inferSelect;
export type Session = typeof sessions.$inferSelect;
export type Speaker = typeof speakers.$inferSelect;
export type SessionSpeaker = typeof sessionSpeakers.$inferSelect;
// Export type
export type Question = typeof questions.$inferSelect;
export type InsertQuestion = z.infer<typeof insertQuestionSchema>;

export type InsertUser = z.infer<typeof insertUserSchema>;
export type InsertNews = z.infer<typeof insertNewsSchema>;
export type InsertMedia = z.infer<typeof insertMediaSchema>;
export type InsertEvent = z.infer<typeof insertEventSchema>;
export type InsertSession = z.infer<typeof insertSessionSchema>;
export type InsertSpeaker = z.infer<typeof insertSpeakerSchema>;
export type InsertSessionSpeaker = z.infer<typeof insertSessionSpeakerSchema>;

export type Transcription = typeof transcriptions.$inferSelect;
export type InsertTranscription = z.infer<typeof insertTranscriptionSchema>;

export type Poll = typeof polls.$inferSelect;
export type PollQuestion = typeof pollQuestions.$inferSelect;
export type PollResponse = typeof pollResponses.$inferSelect;
export type PollType = typeof pollTypes.$inferSelect;

export type InsertPoll = z.infer<typeof insertPollSchema>;
export type InsertPollQuestion = z.infer<typeof insertPollQuestionSchema>;
export type InsertPollResponse = z.infer<typeof insertPollResponseSchema>;

// Add with other types at the bottom
export type Settings = typeof settings.$inferSelect;
export type InsertSettings = z.infer<typeof insertSettingsSchema>;

// Add types for notification channels
export type NotificationChannel = typeof notificationChannels.$inferSelect;
export type InsertNotificationChannel = typeof notificationChannels.$inferInsert;

// Add near the bottom with other types
export type UserSession = typeof userSessions.$inferSelect;
export type InsertUserSession = typeof userSessions.$inferInsert;

// Add types for FCM tokens
export type FCMToken = typeof fcmTokens.$inferSelect;
export type InsertFCMToken = typeof fcmTokens.$inferInsert;