import { parseJson } from '@/common/utils/json';
import type { Database } from '@/remote/database';
import type { UserProfile } from '@/types/foodlog';

export type ProfileRow = {
  id: string;
  workos_user_id: string;
  email: string | null;
  callname: string | null;
  person_name: string | null;
  home: string | null;
  birth_year: number | null;
  biological_sex: string | null;
  body_height_cm: number | null;
  body_weight_kg: number | null;
  health_conditions: string | null;
  food_restrictions: string | null;
  food_preferences: string | null;
  creation_time: string;
  last_edit_time: string;
};

export class ProfileRepository {
  private updateLocks = new Map<string, Promise<void>>();

  constructor(private db: Database) {}

  static mapRowToProfile(row: ProfileRow): UserProfile {
    return {
      id: row.id,
      workos_user_id: row.workos_user_id,
      email: row.email,
      callname: row.callname,
      person_name: row.person_name,
      home: row.home,
      birth_year: row.birth_year,
      biological_sex: row.biological_sex as 'M' | 'F' | null,
      body_height_cm: row.body_height_cm,
      body_weight_kg: row.body_weight_kg,
      health_conditions: parseJson(row.health_conditions, []),
      food_restrictions: parseJson(row.food_restrictions, []),
      food_preferences: parseJson(row.food_preferences, []),
      creation_time: row.creation_time,
      last_edit_time: row.last_edit_time,
    };
  }

  async getProfile(profileId: string): Promise<UserProfile | null> {
    const row = await this.db
      .get<ProfileRow>('SELECT * FROM profiles WHERE id = ?', [profileId])
      .catch(() => null);
    return row ? ProfileRepository.mapRowToProfile(row) : null;
  }

  async watchProfile(
    profileId: string,
    onChange: (profile: UserProfile | null) => void,
    signal?: AbortSignal
  ) {
    const asyncIterable = this.db.watch('SELECT * FROM profiles WHERE id = ?', [profileId], {
      signal,
    });
    for await (const result of asyncIterable) {
      const row = result.rows?._array?.[0] as ProfileRow | undefined;
      onChange(row ? ProfileRepository.mapRowToProfile(row) : null);
    }
  }

  async updateProfile(
    profileId: string,
    workosUserId: string,
    updates: Partial<
      Omit<UserProfile, 'id' | 'workos_user_id' | 'creation_time' | 'last_edit_time'>
    >
  ): Promise<void> {
    const existingLock = this.updateLocks.get(profileId);
    if (existingLock) await existingLock;

    const promise = this.doUpdateProfile(profileId, workosUserId, updates);
    this.updateLocks.set(profileId, promise);
    try {
      await promise;
    } finally {
      this.updateLocks.delete(profileId);
    }
  }

  private async doUpdateProfile(
    profileId: string,
    workosUserId: string,
    updates: Partial<
      Omit<UserProfile, 'id' | 'workos_user_id' | 'creation_time' | 'last_edit_time'>
    >
  ): Promise<void> {
    const now = new Date().toISOString();

    const setClauses: string[] = ['last_edit_time = ?'];
    const values: unknown[] = [now];
    for (const [key, value] of Object.entries(updates)) {
      setClauses.push(`${this.toSnakeCase(key)} = ?`);
      values.push(Array.isArray(value) ? JSON.stringify(value) : value);
    }
    values.push(profileId);

    const updateSql = `UPDATE profiles SET ${setClauses.join(', ')} WHERE id = ?`;
    const result = await this.db.execute(updateSql, values);

    if (result.rowsAffected === 0) {
      const columns = ['id', 'workos_user_id', 'creation_time', 'last_edit_time'];
      const insertValues: unknown[] = [profileId, workosUserId, now, now];
      for (const [key, value] of Object.entries(updates)) {
        columns.push(this.toSnakeCase(key));
        insertValues.push(Array.isArray(value) ? JSON.stringify(value) : value);
      }
      const placeholders = columns.map(() => '?').join(', ');
      const insertSql = `INSERT INTO profiles (${columns.join(', ')}) VALUES (${placeholders})`;
      try {
        await this.db.execute(insertSql, insertValues);
      } catch (e) {
        if (String(e).includes('UNIQUE constraint')) {
          await this.db.execute(updateSql, values);
        } else {
          throw e;
        }
      }
    }
  }

  private toSnakeCase(str: string): string {
    return str.replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`);
  }
}