import { parseJson } from '@/common/utils/json';
import type { Database } from '@/remote/database';
import type { BoundingBox, FoodLog, FoodLogPred, FoodLogStatus } from '@/types/foodlog';
import type { Nutrition } from '@/types/nutrition';
import * as Crypto from 'expo-crypto';

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

  async getAll(): Promise<FoodLog[]> {
    const result = await this.db.getAll<Record<string, unknown>>(
      'SELECT * FROM food_logs WHERE deleted_at IS NULL ORDER BY eaten_time DESC'
    );
    return result.map(FoodLogRepository.mapRowToFoodLog);
  }

  async create(data: {
    id: string;
    profileId: string;
    dayId: string;
    foodName: string;
    ingredients: string[];
    novaClass?: number;
    nutrition?: Nutrition;
    photoUri?: string;
    boundingBox?: unknown;
    eatenAt: Date;
    status: FoodLogStatus;
    predictions?: Array<{
      provider: string;
      predictedFoodName: string;
      ingredients: string[];
      novaClass?: number;
      nutrition?: Nutrition;
    }>;
  }): Promise<void> {
    const now = new Date().toISOString();
    await this.db.writeTransaction(async tx => {
      await tx.execute(
        `INSERT INTO food_logs (
           id, profile_id, day_id, food_item_name, ingredients,
           processed_food_nova_class, nutrition,
           bounding_box, eaten_time, creation_time, last_edit_time,
           processing_status
         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          data.id,
          data.profileId,
          data.dayId,
          data.foodName,
          JSON.stringify(data.ingredients ?? []),
          data.novaClass ?? null,
          data.nutrition ? JSON.stringify(data.nutrition) : null,
          data.boundingBox ? JSON.stringify(data.boundingBox) : null,
          data.eatenAt.toISOString(),
          now,
          now,
          data.status,
        ]
      );

      if (data.photoUri) {
        await tx.execute(
          `INSERT INTO food_log_photos (id, photo_uri, pending_upload) VALUES (?, ?, 1)`,
          [data.id, data.photoUri]
        );
      }

      if (data.predictions) {
        for (const pred of data.predictions) {
          await tx.execute(
            `INSERT INTO food_log_preds (
               id, food_log_id, profile_id, ai_model, food_item_name,
               ingredients, processed_food_nova_class, nutrition,
               creation_time, last_edit_time
             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              Crypto.randomUUID(),
              data.id,
              data.profileId,
              pred.provider,
              pred.predictedFoodName,
              JSON.stringify(pred.ingredients),
              pred.novaClass ?? null,
              pred.nutrition ? JSON.stringify(pred.nutrition) : null,
              now,
              now,
            ]
          );
        }
      }
    });
  }

  async update(id: string, updates: { foodName?: string }): Promise<void> {
    const now = new Date().toISOString();
    if (updates.foodName !== undefined) {
      await this.db.execute(
        `UPDATE food_logs SET food_item_name = ?, last_edit_time = ? WHERE id = ?`,
        [updates.foodName, now, id]
      );
    }
  }

  async updateNutrition(
    id: string,
    novaClass: number | undefined,
    nutrition: Nutrition | undefined
  ): Promise<void> {
    const now = new Date().toISOString();
    await this.db.execute(
      `UPDATE food_logs
       SET processed_food_nova_class = ?,
           nutrition = ?,
           processing_status = ?,
           last_edit_time = ?
       WHERE id = ?`,
      [novaClass ?? null, nutrition ? JSON.stringify(nutrition) : null, 'completed', now, id]
    );
  }

  async savePredictions(
    foodLogId: string,
    profileId: string,
    foodName: string,
    predictions: Array<{
      provider: string;
      novaClass?: number;
      nutrition?: Nutrition;
    }>
  ): Promise<void> {
    const now = new Date().toISOString();
    for (const pred of predictions) {
      await this.db.execute(
        `INSERT INTO food_log_preds (
           id, food_log_id, profile_id, ai_model, food_item_name,
           ingredients, processed_food_nova_class, nutrition,
           creation_time, last_edit_time
         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          Crypto.randomUUID(),
          foodLogId,
          profileId,
          pred.provider,
          foodName,
          JSON.stringify([]),
          pred.novaClass ?? null,
          pred.nutrition ? JSON.stringify(pred.nutrition) : null,
          now,
          now,
        ]
      );
    }
  }

  async delete(id: string): Promise<void> {
    const now = new Date().toISOString();
    await this.db.execute(`UPDATE food_logs SET deleted_at = ?, last_edit_time = ? WHERE id = ?`, [
      now,
      now,
      id,
    ]);
    await this.db.execute('DELETE FROM food_log_photos WHERE id = ?', [id]);
  }

  async getPredictions(foodLogId: string): Promise<FoodLogPred[]> {
    const rows = await this.db.getAll<Record<string, unknown>>(
      'SELECT * FROM food_log_preds WHERE food_log_id = ?',
      [foodLogId]
    );
    return rows.map(this.mapRowToPrediction);
  }

  async getPendingUploads(): Promise<FoodLog[]> {
    const result = await this.db.getAll<Record<string, unknown>>(
      `SELECT fl.*, flp.photo_uri
       FROM food_log_photos flp
       JOIN food_logs fl ON fl.id = flp.id
       WHERE flp.pending_upload = 1 AND flp.photo_uri IS NOT NULL AND fl.deleted_at IS NULL`
    );
    return result.map(FoodLogRepository.mapRowToFoodLog);
  }

  async getLocalPhotoUri(id: string): Promise<string | null> {
    const row = await this.db.getOptional<{ photo_uri: string }>(
      'SELECT photo_uri FROM food_log_photos WHERE id = ?',
      [id]
    );
    return row?.photo_uri ?? null;
  }

  async updatePhotoUri(id: string, photoUri: string): Promise<void> {
    await this.db.execute(
      `INSERT INTO food_log_photos (id, photo_uri, pending_upload) VALUES (?, ?, 0)
       ON CONFLICT(id) DO UPDATE SET photo_uri = excluded.photo_uri`,
      [id, photoUri]
    );
  }

  async updateRemotePhotoPath(id: string, path: string): Promise<void> {
    await this.db.execute('UPDATE food_logs SET photo_path = ? WHERE id = ?', [path, id]);
    await this.db.execute('UPDATE food_log_photos SET pending_upload = 0 WHERE id = ?', [id]);
  }

  async updateIdentification(
    id: string,
    foodName: string,
    ingredients: string[],
    boundingBox?: unknown
  ): Promise<void> {
    const now = new Date().toISOString();
    await this.db.execute(
      `UPDATE food_logs
       SET food_item_name = ?,
           ingredients = ?,
           bounding_box = ?,
           processing_status = ?,
           last_edit_time = ?
       WHERE id = ?`,
      [
        foodName,
        JSON.stringify(ingredients),
        boundingBox ? JSON.stringify(boundingBox) : null,
        'pending_ai_nutrition_reco',
        now,
        id,
      ]
    );
  }

  async markFailure(id: string, status: FoodLogStatus): Promise<void> {
    const now = new Date().toISOString();
    await this.db.execute(
      `UPDATE food_logs SET processing_status = ?, last_edit_time = ? WHERE id = ?`,
      [status, now, id]
    );
  }

  async getPendingProcessing(limit: number): Promise<FoodLog[]> {
    const result = await this.db.getAll<Record<string, unknown>>(
      `SELECT fl.*, flp.photo_uri
       FROM food_logs fl
       LEFT JOIN food_log_photos flp ON fl.id = flp.id
       WHERE fl.processing_status IN ('pending_ai_food_reco', 'pending_ai_nutrition_reco')
       ORDER BY fl.eaten_time ASC
       LIMIT ?`,
      [limit]
    );
    return result.map(FoodLogRepository.mapRowToFoodLog);
  }

  static mapRowToFoodLog(row: Record<string, unknown>): FoodLog {
    const eatenTime = row.eaten_time as string;
    const localDate = eatenTime ? FoodLogRepository.toLocalDateKey(eatenTime) : '';
    return {
      id: row.id as string,
      profileId: row.profile_id as string,
      foodName: (row.food_item_name as string) || '',
      ingredients: parseJson(row.ingredients, []),
      novaClass: row.processed_food_nova_class as number | undefined,
      nutrition: parseJson(row.nutrition, undefined),
      photoUri: (row.photo_uri as string) || undefined,
      eatenAt: eatenTime,
      date: localDate,
      remotePhotoPath: row.photo_path as string | undefined,
      dayId: row.day_id as string | undefined,
      boundingBox: parseJson(row.bounding_box, undefined) as BoundingBox | undefined,
      processingStatus: (row.processing_status as FoodLogStatus) ?? 'completed',
      processingAttempts: 0,
      updatedAt: row.last_edit_time as string | undefined,
      deletedAt: row.deleted_at as string | undefined,
    };
  }

  private static toLocalDateKey(isoString: string): string {
    const date = new Date(isoString);
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
  }

  private mapRowToPrediction(row: Record<string, unknown>): FoodLogPred {
    return {
      id: row.id as string,
      foodLogId: row.food_log_id as string,
      profileId: row.profile_id as string,
      aiModel: row.ai_model as string,
      predictedFoodName: row.food_item_name as string,
      ingredients: parseJson(row.ingredients, []),
      novaClass: row.processed_food_nova_class as number | undefined,
      nutrition: parseJson(row.nutrition, undefined),
    };
  }
}

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

  async getOrCreateDay(profileId: string, date: Date): Promise<string> {
    const dateStr = this.formatDateKey(date);
    const dayId = await this.generateDayId(profileId, dateStr);
    const now = new Date().toISOString();

    const existing = await this.db.getOptional<{ id: string }>(
      'SELECT id FROM days WHERE profile_id = ? AND date = ?',
      [profileId, dateStr]
    );
    if (existing) return existing.id;

    await this.db.execute(
      `INSERT INTO days (id, profile_id, date, creation_time, last_edit_time)
       VALUES (?, ?, ?, ?, ?)`,
      [dayId, profileId, dateStr, now, now]
    );
    return dayId;
  }

  async updateRecommendations(
    profileId: string,
    date: string,
    ingredients: string[],
    foods: unknown[]
  ): Promise<void> {
    const now = new Date().toISOString();
    const existing = await this.db.getOptional<{ id: string }>(
      'SELECT id FROM days WHERE profile_id = ? AND date = ?',
      [profileId, date]
    );

    if (existing) {
      await this.db.execute(
        `UPDATE days SET recommended_ingredients = ?, recommended_foods = ?, last_edit_time = ? WHERE id = ?`,
        [JSON.stringify(ingredients), JSON.stringify(foods), now, existing.id]
      );
    } else {
      const dayId = await this.generateDayId(profileId, date);
      await this.db.execute(
        `INSERT INTO days (id, profile_id, date, recommended_ingredients, recommended_foods, creation_time, last_edit_time) VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [dayId, profileId, date, JSON.stringify(ingredients), JSON.stringify(foods), now, now]
      );
    }
  }

  private async generateDayId(profileId: string, dateStr: string): Promise<string> {
    const hash = await Crypto.digestStringAsync(
      Crypto.CryptoDigestAlgorithm.SHA256,
      `day:${profileId}:${dateStr}`
    );
    const h = hash.slice(0, 32);
    return `${h.slice(0, 8)}-${h.slice(8, 12)}-4${h.slice(13, 16)}-a${h.slice(17, 20)}-${h.slice(20, 32)}`;
  }

  private formatDateKey(date: Date): string {
    return date.toISOString().split('T')[0];
  }
}