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

function mapRecord(row) {
  return {
    id: Number(row.ID),
    studentId: Number(row.StudentID),
    skillId: Number(row.SkillID),
    rating: Number(row.Rating || 0),
    dateTracked: helpers.isoOrNull(row.DateTracked)
  };
}

async function listTrackRecords(userId, afterCursor) {
  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, 'track-record', 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 TrackRecord.*
    FROM TrackRecord
    INNER JOIN Students ON Students.ID = TrackRecord.StudentID
    WHERE Students.Instructor = ?
  `;
  const values = [userId];

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

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

  sql += ' ORDER BY TrackRecord.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 {
    records: pageRows.map(mapRecord),
    nextCursor: latestCursor,
    hasMore,
    nextPageAfterId
  };
}

async function upsertTrackRecord(userId, studentId, skillId, body) {
  sync.requireSyncFields(body);
  helpers.requireFields(body, ['rating']);

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

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

    const existingRows = await tx.query(
      'SELECT ID FROM TrackRecord WHERE StudentID = ? AND SkillID = ? LIMIT 1',
      [studentId, skillId]
    );

    const existingRecordId = existingRows.length ? Number(existingRows[0].ID) : null;
    const existingState = existingRecordId ? await sync.getEntityState(tx, userId, 'track-record', existingRecordId) : null;

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

    let recordId = existingRecordId;
    if (recordId) {
      await tx.updateRows('TrackRecord', {
        Rating: Number(body.rating),
        DateTracked: sync.normalizeClientEditedAt(body.clientEditedAtUtc)
      }, 'ID = ?', [recordId]);
    } else {
      recordId = await tx.insertRow('TrackRecord', {
        StudentID: studentId,
        SkillID: skillId,
        Rating: Number(body.rating),
        DateTracked: sync.normalizeClientEditedAt(body.clientEditedAtUtc)
      });
    }

    await sync.touchEntityState(tx, userId, 'track-record', recordId, body.clientEditedAtUtc, body.deviceId);
    const cursor = await sync.appendChangeLog(tx, userId, 'track-record', recordId, existingRecordId ? 'update' : 'create', body.clientEditedAtUtc);
    const response = sync.buildMutationResponse({
      actionId: body.actionId,
      status: 'applied',
      serverId: recordId,
      cursor
    });

    await sync.storeOperation(tx, userId, body.actionId, 'track-record', recordId, 'applied', body, response);
    return response;
  });
}

module.exports = {
  listTrackRecords,
  upsertTrackRecord
};