import { getDb, pgGet, pgAll } from '@/shared/db';
import type { SyncHistoryInsert, SyncHistoryRow } from './types';

export const SyncHistoryRepo = {
  async create(insert: SyncHistoryInsert): Promise<SyncHistoryRow> {
    const db = getDb();
    return pgGet<SyncHistoryRow>(
      db,
      `INSERT INTO macsync.sync_history (
        device_id, module, started_at, completed_at, status,
        messages_synced, contacts_synced, conversations_synced, error_message
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      RETURNING *`,
      [
        insert.device_id,
        insert.module,
        insert.started_at,
        insert.completed_at ?? null,
        insert.status,
        insert.messages_synced ?? 0,
        insert.contacts_synced ?? 0,
        insert.conversations_synced ?? 0,
        insert.error_message ?? null,
      ],
    ) as Promise<SyncHistoryRow>;
  },

  async update(id: string, updates: Partial<SyncHistoryInsert>): Promise<SyncHistoryRow> {
    const db = getDb();
    const setClauses: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

    if (updates.status !== undefined) {
      setClauses.push(`status = $${paramIndex}`);
      values.push(updates.status);
      paramIndex++;
    }
    if (updates.completed_at !== undefined) {
      setClauses.push(`completed_at = $${paramIndex}`);
      values.push(updates.completed_at);
      paramIndex++;
    }
    if (updates.messages_synced !== undefined) {
      setClauses.push(`messages_synced = $${paramIndex}`);
      values.push(updates.messages_synced);
      paramIndex++;
    }
    if (updates.contacts_synced !== undefined) {
      setClauses.push(`contacts_synced = $${paramIndex}`);
      values.push(updates.contacts_synced);
      paramIndex++;
    }
    if (updates.conversations_synced !== undefined) {
      setClauses.push(`conversations_synced = $${paramIndex}`);
      values.push(updates.conversations_synced);
      paramIndex++;
    }
    if (updates.error_message !== undefined) {
      setClauses.push(`error_message = $${paramIndex}`);
      values.push(updates.error_message);
      paramIndex++;
    }

    if (setClauses.length === 0) {
      const existing = await this.getById(id);
      if (!existing) throw new Error(`sync_history row ${id} not found`);
      return existing;
    }

    const query = `
      UPDATE macsync.sync_history
      SET ${setClauses.join(', ')}
      WHERE id = $${paramIndex}
      RETURNING *
    `;
    values.push(id);

    return pgGet<SyncHistoryRow>(db, query, values) as Promise<SyncHistoryRow>;
  },

  async getById(id: string): Promise<SyncHistoryRow | null> {
    const db = getDb();
    return pgGet<SyncHistoryRow>(db, 'SELECT * FROM macsync.sync_history WHERE id = $1', [id]);
  },

  async getRecentByDevice(deviceId: string, limit = 20): Promise<SyncHistoryRow[]> {
    const db = getDb();
    return pgAll<SyncHistoryRow>(
      db,
      `SELECT * FROM macsync.sync_history
       WHERE device_id = $1
       ORDER BY started_at DESC
       LIMIT $2`,
      [deviceId, limit],
    );
  },

  async getStats(deviceId: string): Promise<{
    total_syncs: string;
    successful_syncs: string;
    failed_syncs: string;
    total_messages: string;
    total_contacts: string;
    last_sync_at: string | null;
  }> {
    const db = getDb();
    return pgGet<{
      total_syncs: string;
      successful_syncs: string;
      failed_syncs: string;
      total_messages: string;
      total_contacts: string;
      last_sync_at: string | null;
    }>(
      db,
      `SELECT
        COUNT(*)::text as total_syncs,
        COUNT(CASE WHEN status = 'success' THEN 1 END)::text as successful_syncs,
        COUNT(CASE WHEN status = 'error' THEN 1 END)::text as failed_syncs,
        COALESCE(SUM(messages_synced), 0)::text as total_messages,
        COALESCE(SUM(contacts_synced), 0)::text as total_contacts,
        MAX(started_at)::text as last_sync_at
       FROM macsync.sync_history
       WHERE device_id = $1`,
      [deviceId],
    ) as Promise<{
      total_syncs: string;
      successful_syncs: string;
      failed_syncs: string;
      total_messages: string;
      total_contacts: string;
      last_sync_at: string | null;
    }>;
  },
};
