const crypto = require('node:crypto');
const helpers = require('../common/helpers');
const { ensureEntitySyncUuids, queryDatabase, withTransaction } = require('../common/sqlDB');
const sync = require('../common/sync');

function mapLesson(row) {
  return {
    id: Number(row.ID),
    uuid: row.SyncUUID || '',
    type: Number(row.Type || 0),
    start: helpers.isoOrNull(row.Start),
    end: helpers.isoOrNull(row.End),
    student: Number(row.Student || row.StudentID || 0),
    studentUuid: row.StudentSyncUUID || '',
    reminder: Number(row.Reminder || 0),
    reminderSent: Boolean(row.ReminderSent),
    deleted: Boolean(row.Deleted),
    lastUpdated: helpers.isoOrNull(row.LastUpdated),
    studentName: `${row.FirstName || ''} ${row.LastName || ''}`.trim(),
    studentEmail: row.Email || '',
    studentNumber: row.MobilePhone || '',
    studentAddress: row.PickUpAddress1 && row.PickUpPostCode ? `${row.PickUpAddress1}, ${row.PickUpPostCode}` : ''
  };
}

function getLessonPayload(userId, body, studentId, uuid = null) {
  const type = Number(body.type || 0);
  const isHoliday = type === 4;

  const payload = {
    Instructor: userId,
    Type: type,
    Start: helpers.toMysqlDateTime(body.start),
    End: helpers.toMysqlDateTime(body.end),
    Student: isHoliday ? 0 : Number(studentId || 0),
    Reminder: isHoliday ? 0 : Number(body.reminder || 0),
    ReminderSent: 0,
    Deleted: body.deleted ? 1 : 0,
    LastUpdated: helpers.toMysqlDateTime(new Date())
  };

  if (uuid) {
    payload.SyncUUID = uuid;
  }

  return payload;
}

async function ensureLessonUuids(userId) {
  await ensureEntitySyncUuids('Lessons', userId);
}

async function ensureOwnedStudent(tx, userId, studentId) {
  const rows = await tx.query(
    'SELECT ID FROM Students WHERE ID = ? AND Instructor = ? AND Deleted = 0 LIMIT 1',
    [studentId, userId]
  );

  if (!rows.length) {
    const error = new Error('Student not found.');
    error.statusCode = 404;
    throw error;
  }
}

async function resolveLessonStudentId(tx, userId, body) {
  if (Number(body.type || 0) === 4) {
    return 0;
  }

  if (body.studentUuid) {
    const rows = await tx.query(
      'SELECT ID FROM Students WHERE SyncUUID = ? AND Instructor = ? AND Deleted = 0 LIMIT 1',
      [sync.normalizeUuid(body.studentUuid), userId]
    );

    if (!rows.length) {
      const error = new Error('Student not found.');
      error.statusCode = 404;
      throw error;
    }

    return Number(rows[0].ID);
  }

  const studentId = Number(body.student || 0);
  await ensureOwnedStudent(tx, userId, studentId);
  return studentId;
}

async function listLessons(userId, afterCursor) {
  await ensureLessonUuids(userId);

  const options = arguments[2] || {};
  const pageSize = Math.min(Math.max(Number(options.pageSize || 2000), 1), 5000);
  const pageAfterId = Math.max(Number(options.pageAfterId || 0), 0);
  const ids = await sync.getChangedEntityIds(userId, 'lesson', afterCursor);
  const isDeltaPull = Boolean(afterCursor);
  const latestCursor = isDeltaPull
    ? Math.max(Number(afterCursor) || 0, await sync.ensureLatestCursor(userId))
    : Number(options.cursorSnapshot || 0) || await sync.ensureLatestCursor(userId);

  let sql = `
    SELECT Lessons.*, Students.ID AS StudentID, Students.SyncUUID AS StudentSyncUUID, Students.FirstName, Students.LastName, Students.Email, Students.MobilePhone, Students.PickUpAddress1, Students.PickUpPostCode
    FROM Lessons
    LEFT JOIN Students ON Lessons.Student = Students.ID
    WHERE Lessons.Instructor = ?
  `;
  const values = [userId];

  if (ids && ids.length === 0) {
    return { lessons: [], nextCursor: latestCursor, hasMore: false, nextPageAfterId: null };
  }

  if (ids && ids.length > 0) {
    sql += ` AND Lessons.ID IN (${ids.map(() => '?').join(', ')})`;
    values.push(...ids);
  } else if (!isDeltaPull) {
    sql += ' AND Lessons.Deleted = 0';
    if (pageAfterId > 0) {
      sql += ' AND Lessons.ID > ?';
      values.push(pageAfterId);
    }
  }

  sql += ' ORDER BY Lessons.ID ASC';
  if (!isDeltaPull) {
    sql += ' LIMIT ?';
    values.push(pageSize + 1);
  }

  const rows = await queryDatabase(sql, values);
  let pageRows = rows;
  let hasMore = false;
  let nextPageAfterId = null;
  if (!isDeltaPull && rows.length > pageSize) {
    hasMore = true;
    pageRows = rows.slice(0, pageSize);
    nextPageAfterId = Number(pageRows[pageRows.length - 1].ID);
  }

  return {
    lessons: pageRows.map(mapLesson),
    nextCursor: latestCursor,
    hasMore,
    nextPageAfterId
  };
}

async function createLesson(userId, body) {
  sync.requireSyncFields(body, true);
  helpers.requireFields(body, ['type', 'start', 'end', 'student', 'reminder']);
  let lessonUuid = body.uuid && String(body.uuid).trim() ? sync.normalizeUuid(body.uuid) : null;

  return withTransaction(async (tx) => {
    const stored = await sync.getStoredOperation(tx, userId, body.actionId);
    if (stored) {
      return stored;
    }

    let lessonId = await sync.resolveEntityIdFromLocal(tx, userId, body.deviceId, 'lesson', body.localId);
    if (!lessonId) {
      lessonUuid ??= crypto.randomUUID();
      const studentId = await resolveLessonStudentId(tx, userId, body);
      lessonId = await tx.insertRow('Lessons', getLessonPayload(userId, body, studentId, lessonUuid));
    } else {
      lessonUuid = await sync.resolveUuidFromEntityId(tx, userId, 'Lessons', lessonId);
    }

    await sync.rememberLocalId(tx, userId, body.deviceId, 'lesson', body.localId, lessonId);
    await sync.touchEntityState(tx, userId, 'lesson', lessonId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'lesson', lessonId, 'create', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: lessonId,
      uuid: lessonUuid || await sync.resolveUuidFromEntityId(tx, userId, 'Lessons', lessonId),
      cursor
    });

    await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'applied', body, response);
    return response;
  });
}

async function updateLesson(userId, lessonId, body) {
  sync.requireSyncFields(body);
  helpers.requireFields(body, ['type', 'start', 'end', 'student', 'reminder']);
  await ensureLessonUuids(userId);

  return withTransaction(async (tx) => {
    const stored = await sync.getStoredOperation(tx, userId, body.actionId);
    if (stored) {
      return stored;
    }

    const rows = await tx.query('SELECT ID, SyncUUID FROM Lessons WHERE ID = ? AND Instructor = ? LIMIT 1', [lessonId, userId]);
    if (!rows.length) {
      const error = new Error('Lesson not found.');
      error.statusCode = 404;
      throw error;
    }

    const existingState = await sync.getEntityState(tx, userId, 'lesson', lessonId);
    if (sync.isStale(existingState, body.clientEditedAtUtc)) {
      const response = sync.buildMutationResponse({
        actionId: body.actionId,
        status: 'skipped',
        serverId: lessonId,
        uuid: rows[0].SyncUUID,
        cursor: await sync.getLatestCursor(userId),
        reason: 'stale_update'
      });
      await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'skipped', body, response);
      return response;
    }

    const studentId = await resolveLessonStudentId(tx, userId, body);
    await tx.updateRows('Lessons', getLessonPayload(userId, body, studentId), 'ID = ? AND Instructor = ?', [lessonId, userId]);
    await sync.touchEntityState(tx, userId, 'lesson', lessonId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'lesson', lessonId, 'update', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: lessonId,
      uuid: rows[0].SyncUUID,
      cursor
    });

    await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'applied', body, response);
    return response;
  });
}

async function deleteLesson(userId, lessonId, body) {
  sync.requireSyncFields(body);
  await ensureLessonUuids(userId);

  return withTransaction(async (tx) => {
    const stored = await sync.getStoredOperation(tx, userId, body.actionId);
    if (stored) {
      return stored;
    }

    const rows = await tx.query('SELECT ID, SyncUUID FROM Lessons WHERE ID = ? AND Instructor = ? LIMIT 1', [lessonId, userId]);
    if (!rows.length) {
      const error = new Error('Lesson not found.');
      error.statusCode = 404;
      throw error;
    }

    const existingState = await sync.getEntityState(tx, userId, 'lesson', lessonId);
    if (sync.isStale(existingState, body.clientEditedAtUtc)) {
      const response = sync.buildMutationResponse({
        actionId: body.actionId,
        status: 'skipped',
        serverId: lessonId,
        uuid: rows[0].SyncUUID,
        cursor: await sync.getLatestCursor(userId),
        reason: 'stale_update'
      });
      await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'skipped', body, response);
      return response;
    }

    await tx.updateRows('Lessons', {
      Deleted: 1,
      LastUpdated: helpers.toMysqlDateTime(new Date())
    }, 'ID = ? AND Instructor = ?', [lessonId, userId]);

    await sync.touchEntityState(tx, userId, 'lesson', lessonId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'lesson', lessonId, 'delete', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: lessonId,
      uuid: rows[0].SyncUUID,
      cursor
    });

    await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'applied', body, response);
    return response;
  });
}

async function getLessonByUuid(userId, uuidValue) {
  const uuid = sync.normalizeUuid(uuidValue);
  const rows = await queryDatabase(
    `SELECT Lessons.*, Students.ID AS StudentID, Students.SyncUUID AS StudentSyncUUID, Students.FirstName, Students.LastName, Students.Email, Students.MobilePhone, Students.PickUpAddress1, Students.PickUpPostCode
     FROM Lessons
     LEFT JOIN Students ON Lessons.Student = Students.ID
     WHERE Lessons.Instructor = ? AND Lessons.SyncUUID = ?
     LIMIT 1`,
    [userId, uuid]
  );

  if (!rows.length) {
    const error = new Error('Lesson not found.');
    error.statusCode = 404;
    throw error;
  }

  return {
    lesson: mapLesson(rows[0])
  };
}

async function upsertLessonByUuid(userId, uuidValue, body) {
  const uuid = sync.normalizeUuid(uuidValue);
  sync.requireSyncFields(body);
  helpers.requireFields(body, ['type', 'start', 'end', 'reminder']);

  return withTransaction(async (tx) => {
    const stored = await sync.getStoredOperation(tx, userId, body.actionId);
    if (stored) {
      return stored;
    }

    const rows = await tx.query('SELECT ID FROM Lessons WHERE Instructor = ? AND SyncUUID = ? LIMIT 1', [userId, uuid]);
    const existingLessonId = rows.length ? Number(rows[0].ID) : null;

    if (existingLessonId) {
      const existingState = await sync.getEntityState(tx, userId, 'lesson', existingLessonId);
      if (sync.isStale(existingState, body.clientEditedAtUtc)) {
        const response = sync.buildMutationResponse({
          actionId: body.actionId,
          status: 'skipped',
          serverId: existingLessonId,
          uuid,
          cursor: await sync.getLatestCursor(userId),
          reason: 'stale_update'
        });
        await sync.storeOperation(tx, userId, body.actionId, 'lesson', existingLessonId, 'skipped', body, response);
        return response;
      }

      const studentId = await resolveLessonStudentId(tx, userId, body);
      await tx.updateRows('Lessons', getLessonPayload(userId, body, studentId), 'ID = ? AND Instructor = ?', [existingLessonId, userId]);
      await sync.touchEntityState(tx, userId, 'lesson', existingLessonId, body.clientEditedAtUtc, body.deviceId);
      const cursor = await sync.appendChangeLog(tx, userId, 'lesson', existingLessonId, 'update', body.clientEditedAtUtc);
      const response = sync.buildMutationResponse({
        actionId: body.actionId,
        status: 'applied',
        serverId: existingLessonId,
        uuid,
        cursor
      });
      await sync.storeOperation(tx, userId, body.actionId, 'lesson', existingLessonId, 'applied', body, response);
      return response;
    }

    const studentId = await resolveLessonStudentId(tx, userId, body);
    const lessonId = await tx.insertRow('Lessons', getLessonPayload(userId, body, studentId, uuid));
    await sync.touchEntityState(tx, userId, 'lesson', lessonId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'lesson', lessonId, 'create', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: lessonId,
      uuid,
      cursor
    });
    await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'applied', body, response);
    return response;
  });
}

async function deleteLessonByUuid(userId, uuidValue, body) {
  const uuid = sync.normalizeUuid(uuidValue);
  sync.requireSyncFields(body);

  return withTransaction(async (tx) => {
    const stored = await sync.getStoredOperation(tx, userId, body.actionId);
    if (stored) {
      return stored;
    }

    const rows = await tx.query('SELECT ID FROM Lessons WHERE Instructor = ? AND SyncUUID = ? LIMIT 1', [userId, uuid]);
    if (!rows.length) {
      const error = new Error('Lesson not found.');
      error.statusCode = 404;
      throw error;
    }

    const lessonId = Number(rows[0].ID);
    const existingState = await sync.getEntityState(tx, userId, 'lesson', lessonId);
    if (sync.isStale(existingState, body.clientEditedAtUtc)) {
      const response = sync.buildMutationResponse({
        actionId: body.actionId,
        status: 'skipped',
        serverId: lessonId,
        uuid,
        cursor: await sync.getLatestCursor(userId),
        reason: 'stale_update'
      });
      await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'skipped', body, response);
      return response;
    }

    await tx.updateRows('Lessons', {
      Deleted: 1,
      LastUpdated: helpers.toMysqlDateTime(new Date())
    }, 'ID = ? AND Instructor = ?', [lessonId, userId]);

    await sync.touchEntityState(tx, userId, 'lesson', lessonId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'lesson', lessonId, 'delete', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: lessonId,
      uuid,
      cursor
    });
    await sync.storeOperation(tx, userId, body.actionId, 'lesson', lessonId, 'applied', body, response);
    return response;
  });
}

module.exports = {
  createLesson,
  deleteLesson,
  deleteLessonByUuid,
  getLessonByUuid,
  listLessons,
  updateLesson,
  upsertLessonByUuid
};