import { Database } from "bun:sqlite";

export interface User {
  id: number;
  name: string;
  creds: string;
}

export interface Session {
  id: string;
  user_id: number;
  created_at: number;
  expires_at: number;
  last_activity?: number;
  ip_address?: string;
  user_agent?: string;
  data?: any;
}

export interface UserSettings {
  id?: number;
  user_id: number;
  preferred_session_length: number;
  target_daily_reviews: number;
  difficulty_preference: number;
  audio_enabled: boolean;
  tone_visualization_enabled: boolean;
  auto_advance: boolean;
  created_at?: number;
  updated_at?: number;
}

export class UserQueries {
  constructor(private db: Database) {}

  /**
   * Create a new user
   */
  createUser(name: string, creds: string): User {
    const query = this.db.query(`
      INSERT INTO users (name, creds)
      VALUES (?, ?)
      RETURNING id, name, creds
    `);

    return query.get(name, creds) as User;
  }

  /**
   * Get user by ID
   */
  getUserById(id: number): User | null {
    const query = this.db.query(`
      SELECT id, name, creds
      FROM users
      WHERE id = ?
    `);

    return query.get(id) as User | null;
  }

  /**
   * Get user by name
   */
  getUserByName(name: string): User | null {
    const query = this.db.query(`
      SELECT id, name, creds
      FROM users
      WHERE name = ?
    `);

    return query.get(name) as User | null;
  }

  /**
   * Create a new session for a user
   */
  createSession(userId: number, sessionToken: string, expiresAt: number): Session {
    const query = this.db.query(`
      INSERT INTO sessions (id, user_id, created_at, expires_at, last_activity)
      VALUES (?, ?, ?, ?, ?)
      RETURNING id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
    `);

    const now = Date.now();
    return query.get(sessionToken, userId, now, expiresAt, now) as Session;
  }

  /**
   * Get session by token
   */
  getSessionByToken(sessionToken: string): Session | null {
    const query = this.db.query(`
      SELECT id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
      FROM sessions
      WHERE id = ? AND expires_at > ?
    `);

    return query.get(sessionToken, Date.now()) as Session | null;
  }

  /**
   * Get user from session token
   */
  getUserFromSession(sessionToken: string): User | null {
    const query = this.db.query(`
      SELECT u.id, u.name, u.creds
      FROM users u
      JOIN sessions s ON u.id = s.user_id
      WHERE s.id = ? AND s.expires_at > ?
    `);

    return query.get(sessionToken, Date.now()) as User | null;
  }

  /**
   * Delete session by token
   */
  deleteSession(sessionToken: string): boolean {
    const query = this.db.query(`
      DELETE FROM sessions
      WHERE id = ?
    `);

    const result = query.run(sessionToken);
    return result.changes > 0;
  }

  /**
   * Clean up expired sessions
   */
  cleanupExpiredSessions(): number {
    const query = this.db.query(`
      DELETE FROM sessions
      WHERE expires_at <= ?
    `);

    const result = query.run(Date.now());
    return result.changes;
  }

  /**
   * Update session last activity
   */
  updateSessionActivity(sessionToken: string): boolean {
    const query = this.db.query(`
      UPDATE sessions
      SET last_activity = ?
      WHERE id = ? AND expires_at > ?
    `);

    const result = query.run(Date.now(), sessionToken, Date.now());
    return result.changes > 0;
  }

  /**
   * Extend session expiry
   */
  extendSessionExpiry(sessionToken: string, newExpiry: number): boolean {
    const query = this.db.query(`
      UPDATE sessions
      SET expires_at = ?, last_activity = ?
      WHERE id = ? AND expires_at > ?
    `);

    const result = query.run(newExpiry, Date.now(), sessionToken, Date.now());
    return result.changes > 0;
  }

  /**
   * Get all active sessions for a user
   */
  getUserSessions(userId: number): Session[] {
    const query = this.db.query(`
      SELECT id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
      FROM sessions
      WHERE user_id = ? AND expires_at > ?
      ORDER BY last_activity DESC
    `);

    return query.all(userId, Date.now()) as Session[];
  }

  /**
   * Delete all sessions for a user (for logout all devices)
   */
  deleteAllUserSessions(userId: number): number {
    const query = this.db.query(`
      DELETE FROM sessions
      WHERE user_id = ?
    `);

    const result = query.run(userId);
    return result.changes;
  }

  /**
   * Get user settings
   */
  getUserSettings(userId: number): UserSettings | null {
    const query = this.db.query(`
      SELECT id, user_id, preferred_session_length, target_daily_reviews, 
             difficulty_preference, audio_enabled, tone_visualization_enabled, 
             auto_advance, created_at, updated_at
      FROM tone_learning_settings
      WHERE user_id = ?
    `);

    return query.get(userId) as UserSettings | null;
  }

  /**
   * Create or update user settings
   */
  upsertUserSettings(
    settings: Omit<UserSettings, "id" | "created_at" | "updated_at">,
  ): UserSettings {
    const query = this.db.query(`
      INSERT INTO tone_learning_settings 
        (user_id, preferred_session_length, target_daily_reviews, 
         difficulty_preference, audio_enabled, tone_visualization_enabled, auto_advance)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT (user_id) DO UPDATE SET
        preferred_session_length = excluded.preferred_session_length,
        target_daily_reviews = excluded.target_daily_reviews,
        difficulty_preference = excluded.difficulty_preference,
        audio_enabled = excluded.audio_enabled,
        tone_visualization_enabled = excluded.tone_visualization_enabled,
        auto_advance = excluded.auto_advance,
        updated_at = (strftime('%s', 'now') * 1000)
      RETURNING id, user_id, preferred_session_length, target_daily_reviews,
                difficulty_preference, audio_enabled, tone_visualization_enabled,
                auto_advance, created_at, updated_at
    `);

    return query.get(
      settings.user_id,
      settings.preferred_session_length,
      settings.target_daily_reviews,
      settings.difficulty_preference,
      settings.audio_enabled,
      settings.tone_visualization_enabled,
      settings.auto_advance,
    ) as UserSettings;
  }

  /**
   * Update user settings partially
   */
  updateUserSettings(
    userId: number,
    updates: Partial<UserSettings>,
  ): UserSettings | null {
    const setClause = Object.keys(updates)
      .filter(
        (key) => key !== "id" && key !== "user_id" && key !== "created_at",
      )
      .map((key) => `${key} = ?`)
      .join(", ");

    if (!setClause) return null;

    const values = Object.values(updates).filter((_, index) => {
      const key = Object.keys(updates)[index];
      return key !== "id" && key !== "user_id" && key !== "created_at";
    });

    const query = this.db.query(`
      UPDATE tone_learning_settings
      SET ${setClause}, updated_at = (strftime('%s', 'now') * 1000)
      WHERE user_id = ?
      RETURNING id, user_id, preferred_session_length, target_daily_reviews,
                difficulty_preference, audio_enabled, tone_visualization_enabled,
                auto_advance, created_at, updated_at
    `);

    return query.get(...values, userId) as UserSettings | null;
  }

  /**
   * Get all users (admin function)
   */
  getAllUsers(): User[] {
    const query = this.db.query(`
      SELECT id, name, creds
      FROM users
      ORDER BY name
    `);

    return query.all() as User[];
  }

  /**
   * Delete user (admin function)
   */
  deleteUser(userId: number): boolean {
    const query = this.db.query(`
      DELETE FROM users
      WHERE id = ?
    `);

    const result = query.run(userId);
    return result.changes > 0;
  }
}