import { parseJson } from '@/common/utils/json';
import type { Database } from '@/remote/database';
import type { InputType, MemoLog, MemoLogger, MemoLoggerOption } from './types';
import * as Crypto from 'expo-crypto';

export async function migrateDemoData(db: Database, realProfileId: string): Promise<void> {
  const now = new Date().toISOString();
  await db.writeTransaction(async tx => {
    await tx.execute(
      `UPDATE days SET profile_id = ?, last_edit_time = ? WHERE profile_id = 'demo'`,
      [realProfileId, now]
    );
    await tx.execute(
      `UPDATE memo_loggers SET profile_id = ?, last_edit_time = ? WHERE profile_id = 'demo'`,
      [realProfileId, now]
    );
    await tx.execute(
      `UPDATE memo_logs SET profile_id = ?, last_edit_time = ? WHERE profile_id = 'demo'`,
      [realProfileId, now]
    );
  });
}

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

  async getAll(profileId: string): Promise<MemoLog[]> {
    const rows = await this.db.getAll<Record<string, unknown>>(
      `SELECT * FROM memo_logs
       WHERE profile_id = ? AND deleted_at IS NULL
       ORDER BY logged_time DESC`,
      [profileId]
    );
    return rows.map(MemoLogRepository.mapRowToMemoLog);
  }

  async getByDay(profileId: string, dayId: string): Promise<MemoLog[]> {
    const rows = await this.db.getAll<Record<string, unknown>>(
      `SELECT * FROM memo_logs
       WHERE profile_id = ? AND day_id = ? AND deleted_at IS NULL
       ORDER BY logged_time DESC`,
      [profileId, dayId]
    );
    return rows.map(MemoLogRepository.mapRowToMemoLog);
  }

  async create(
    profileId: string,
    dayId: string,
    entry: Omit<MemoLog, 'id' | 'timestamp'> & { timestamp?: Date }
  ): Promise<string> {
    const id = Crypto.randomUUID();
    const now = new Date().toISOString();
    const loggedTime = (entry.timestamp ?? new Date()).toISOString();

    await this.db.writeTransaction(async tx => {
      await tx.execute(
        `INSERT INTO memo_logs (
          id, profile_id, day_id, logger_id, title, type, value,
          value_unit, icon, logged_time, creation_time, last_edit_time
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          id,
          profileId,
          dayId,
          entry.loggerId || null,
          entry.title,
          entry.type,
          String(entry.value),
          entry.valueUnit ?? null,
          entry.icon ?? null,
          loggedTime,
          now,
          now,
        ]
      );
    });
    return id;
  }

  async update(id: string, updates: { value?: number | string; icon?: string }): Promise<void> {
    const now = new Date().toISOString();
    const setClauses: string[] = ['last_edit_time = ?'];
    const values: unknown[] = [now];

    if (updates.value !== undefined) {
      setClauses.push('value = ?');
      values.push(String(updates.value));
    }
    if (updates.icon !== undefined) {
      setClauses.push('icon = ?');
      values.push(updates.icon);
    }

    values.push(id);
    await this.db.writeTransaction(async tx => {
      await tx.execute(`UPDATE memo_logs SET ${setClauses.join(', ')} WHERE id = ?`, values);
    });
  }

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

  static mapRowToMemoLog(row: Record<string, unknown>): MemoLog {
    const value = row.value as string;
    const numValue = parseFloat(value);
    return {
      id: row.id as string,
      loggerId: row.logger_id as string,
      type: row.type as InputType,
      title: row.title as string,
      value: isNaN(numValue) ? value : numValue,
      valueUnit: row.value_unit as string | undefined,
      icon: row.icon as string | undefined,
      timestamp: new Date(row.logged_time as string),
    };
  }
}

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

  async seedDefaults(profileId: string, defaults: MemoLogger[]): Promise<void> {
    const now = new Date().toISOString();
    await this.db.writeTransaction(async tx => {
      const existing = await tx.getOptional<{ id: string }>(
        `SELECT id FROM memo_loggers WHERE profile_id = ? LIMIT 1`,
        [profileId]
      );
      if (existing) return;

      for (let i = 0; i < defaults.length; i++) {
        const logger = defaults[i];
        await tx.execute(
          `INSERT INTO memo_loggers (
            id, profile_id, name, icon, color, type, unit, options, sort_order, creation_time, last_edit_time
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            Crypto.randomUUID(),
            profileId,
            logger.name,
            logger.icon ?? null,
            logger.color ?? null,
            logger.type,
            logger.unit ?? null,
            logger.options ? JSON.stringify(logger.options) : null,
            i,
            now,
            now,
          ]
        );
      }
    });
  }

  async getAll(profileId: string): Promise<MemoLogger[]> {
    const rows = await this.db.getAll<Record<string, unknown>>(
      `SELECT * FROM memo_loggers
       WHERE profile_id = ? AND deleted_at IS NULL
       ORDER BY sort_order ASC`,
      [profileId]
    );
    return rows.map(MemoLoggerRepository.mapRowToMemoLogger);
  }

  async create(profileId: string, logger: Omit<MemoLogger, 'id'>): Promise<string> {
    const id = Crypto.randomUUID();
    const now = new Date().toISOString();

    await this.db.writeTransaction(async tx => {
      const maxOrder = await tx.getOptional<{ max_order: number }>(
        `SELECT MAX(sort_order) as max_order FROM memo_loggers WHERE profile_id = ?`,
        [profileId]
      );
      const sortOrder = (maxOrder?.max_order ?? -1) + 1;

      await tx.execute(
        `INSERT INTO memo_loggers (
          id, profile_id, name, icon, color, type, unit, options, sort_order, creation_time, last_edit_time
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          id,
          profileId,
          logger.name,
          logger.icon ?? null,
          logger.color ?? null,
          logger.type,
          logger.unit ?? null,
          logger.options ? JSON.stringify(logger.options) : null,
          sortOrder,
          now,
          now,
        ]
      );
    });
    return id;
  }

  async update(id: string, updates: Partial<Omit<MemoLogger, 'id'>>): Promise<void> {
    const now = new Date().toISOString();
    const setClauses: string[] = ['last_edit_time = ?'];
    const values: unknown[] = [now];

    if (updates.name !== undefined) {
      setClauses.push('name = ?');
      values.push(updates.name);
    }
    if (updates.icon !== undefined) {
      setClauses.push('icon = ?');
      values.push(updates.icon);
    }
    if (updates.color !== undefined) {
      setClauses.push('color = ?');
      values.push(updates.color);
    }
    if (updates.type !== undefined) {
      setClauses.push('type = ?');
      values.push(updates.type);
    }
    if (updates.unit !== undefined) {
      setClauses.push('unit = ?');
      values.push(updates.unit);
    }
    if (updates.options !== undefined) {
      setClauses.push('options = ?');
      values.push(JSON.stringify(updates.options));
    }

    values.push(id);
    await this.db.writeTransaction(async tx => {
      await tx.execute(`UPDATE memo_loggers SET ${setClauses.join(', ')} WHERE id = ?`, values);
    });
  }

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

  static mapRowToMemoLogger(row: Record<string, unknown>): MemoLogger {
    return {
      id: row.id as string,
      name: row.name as string,
      icon: row.icon as string | undefined,
      color: row.color as string | undefined,
      type: row.type as InputType,
      unit: row.unit as string | undefined,
      options: row.options ? parseJson<MemoLoggerOption[]>(row.options, []) : undefined,
    };
  }
}