import { clampLimit, optionalIsoDate, requiredIsoDate } from '@/services/shared/validate';
import { Op, literal, type WhereOptions } from 'sequelize';
import db from '@/db/models';
import { withTransaction } from '@/db/with-transaction';
import ForbiddenError from '@/shared/errors/forbidden';
import ValidationError from '@/shared/errors/validation';
import {
  STAFF_ATTENDANCE_DEFAULT_LIMIT,
  STAFF_ATTENDANCE_MAX_LIMIT,
  STAFF_ATTENDANCE_STATUSES,
} from '@/shared/constants/staff-attendance';
import {
  assertAuthenticatedTenantUser,
  campusDimensionScope,
  getCampusId,
  getRoleScope,
  getSchoolId,
  hasFeaturePermission,
  requireOrganizationId,
  requireUserId,
} from '@/services/shared/access';
import { ROLE_NAMES, ROLE_SCOPES } from '@/shared/constants/roles';
import { FEATURE_PERMISSIONS } from '@/shared/constants/product-permissions';
import type { StaffAttendanceRecords } from '@/db/models/staff_attendance_records';
import type { CurrentUser } from '@/db/api/types';

interface StaffAttendanceFilter {
  startDate?: unknown;
  endDate?: unknown;
  limit?: unknown;
}

interface StaffAttendanceInput {
  date?: unknown;
  status?: unknown;
  note?: unknown;
  userId?: unknown;
}

const STAFF_ATTENDANCE_INTERNAL_ROLE_NAMES = [
  ROLE_NAMES.OWNER,
  ROLE_NAMES.SUPERINTENDENT,
  ROLE_NAMES.PRINCIPAL,
  ROLE_NAMES.REGISTRAR,
  ROLE_NAMES.DIRECTOR,
  ROLE_NAMES.OFFICE_MANAGER,
  ROLE_NAMES.TEACHER,
  ROLE_NAMES.SUPPORT_STAFF,
] as const;
const STAFF_ATTENDANCE_STATUS_VALUES: readonly string[] = Object.values(STAFF_ATTENDANCE_STATUSES);

const STAFF_ATTENDANCE_UUID_RE =
  /^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$/;

function schoolCampusIdSubquery(schoolId: string) {
  if (!STAFF_ATTENDANCE_UUID_RE.test(schoolId)) {
    return null;
  }

  return literal(
    `(SELECT "id" FROM "campuses" WHERE "schoolId" = '${schoolId}' AND "deletedAt" IS NULL)`,
  );
}

function schoolUserIdSubquery(schoolId: string) {
  if (!STAFF_ATTENDANCE_UUID_RE.test(schoolId)) {
    return null;
  }

  return literal(
    `(SELECT "id" FROM "users" WHERE "schoolId" = '${schoolId}' AND "deletedAt" IS NULL)`,
  );
}

/**
 * Restricts records to the staff member, or (for report roles) to the records
 * their scope allows: org-wide for owner/superintendent, the school's campuses
 * for principal/registrar, a single campus for director.
 */
function visibilityScope(currentUser?: CurrentUser) {
  if (
    !hasFeaturePermission(
      currentUser,
      FEATURE_PERMISSIONS.READ_STAFF_ATTENDANCE_REPORTS,
    )
  ) {
    return { userId: requireUserId(currentUser) };
  }

  if (getRoleScope(currentUser) === ROLE_SCOPES.SCHOOL) {
    const schoolId = getSchoolId(currentUser);
    const campusSubquery = schoolId ? schoolCampusIdSubquery(schoolId) : null;
    const userSubquery = schoolId ? schoolUserIdSubquery(schoolId) : null;

    if (campusSubquery && userSubquery) {
      return {
        [Op.or]: [
          { campusId: { [Op.in]: campusSubquery } },
          { userId: { [Op.in]: userSubquery } },
        ],
      };
    }
  }

  return campusDimensionScope(currentUser);
}

function staffCountScope(currentUser?: CurrentUser): WhereOptions {
  if (getRoleScope(currentUser) === ROLE_SCOPES.SCHOOL) {
    const schoolId = getSchoolId(currentUser);
    const campusSubquery = schoolId ? schoolCampusIdSubquery(schoolId) : null;

    if (campusSubquery) {
      return {
        [Op.or]: [
          { campusId: { [Op.in]: campusSubquery } },
          { schoolId },
        ],
      };
    }
  }

  return campusDimensionScope(currentUser);
}

function dateFilter(filter: StaffAttendanceFilter): {
  attendance_date?: { [Op.gte]?: string; [Op.lte]?: string };
} {
  const startDate = optionalIsoDate(filter.startDate);
  const endDate = optionalIsoDate(filter.endDate);

  if (!startDate && !endDate) {
    return {};
  }

  return {
    attendance_date: {
      ...(startDate ? { [Op.gte]: startDate } : {}),
      ...(endDate ? { [Op.lte]: endDate } : {}),
    },
  };
}

function toRecordDto(record: StaffAttendanceRecords) {
  const plain = record.get({ plain: true });

  return {
    id: plain.id,
    date: plain.attendance_date,
    status: plain.status,
    note: plain.note,
    user_name: plain.user_name,
    user_role: plain.user_role,
    organizationId: plain.organizationId,
    campusId: plain.campusId,
    userId: plain.userId,
    createdAt: plain.createdAt,
    updatedAt: plain.updatedAt,
  };
}

function assertCanFillStaffAttendance(currentUser?: CurrentUser): void {
  assertAuthenticatedTenantUser(currentUser);

  if (hasFeaturePermission(currentUser, FEATURE_PERMISSIONS.FILL_ATTENDANCE)) {
    return;
  }

  throw new ForbiddenError();
}

function validateStatus(value: unknown): string {
  if (
    typeof value === 'string'
    && STAFF_ATTENDANCE_STATUS_VALUES.includes(value)
  ) {
    return value;
  }

  throw new ValidationError();
}

function validateNote(value: unknown): string | null {
  if (typeof value !== 'string' || value.trim().length === 0) {
    return null;
  }

  return value.trim();
}

function requireString(value: unknown): string {
  if (typeof value !== 'string' || value.trim().length === 0) {
    throw new ValidationError();
  }

  return value.trim();
}

function staffUserScope(currentUser?: CurrentUser): WhereOptions {
  const scope = getRoleScope(currentUser);
  const base = { organizationId: requireOrganizationId(currentUser) };

  if (scope === ROLE_SCOPES.SCHOOL) {
    const schoolId = getSchoolId(currentUser);
    if (!schoolId) {
      throw new ForbiddenError();
    }
    return { ...base, schoolId, campusId: null };
  }

  if (scope === ROLE_SCOPES.CAMPUS || scope === ROLE_SCOPES.CLASS) {
    const campusId = getCampusId(currentUser);
    if (!campusId) {
      throw new ForbiddenError();
    }
    return { ...base, campusId };
  }

  return { ...base, schoolId: null, campusId: null };
}

function staffUserName(user: {
  firstName?: string | null;
  lastName?: string | null;
  email?: string | null;
}): string {
  const fullName = `${user.firstName ?? ''} ${user.lastName ?? ''}`.trim();
  return fullName || user.email || 'Staff Member';
}

class StaffAttendanceService {
  static async listRecords(
    filter: StaffAttendanceFilter,
    currentUser?: CurrentUser,
  ) {
    assertAuthenticatedTenantUser(currentUser);

    const result = await db.staff_attendance_records.findAndCountAll({
      where: {
        organizationId: requireOrganizationId(currentUser),
        ...visibilityScope(currentUser),
        ...dateFilter(filter),
      },
      limit: clampLimit(filter.limit, STAFF_ATTENDANCE_DEFAULT_LIMIT, STAFF_ATTENDANCE_MAX_LIMIT),
      order: [
        ['attendance_date', 'desc'],
        ['user_name', 'asc'],
      ],
    });

    return {
      rows: result.rows.map(toRecordDto),
      count: result.count,
    };
  }

  static async summary(
    filter: StaffAttendanceFilter,
    currentUser?: CurrentUser,
  ) {
    assertAuthenticatedTenantUser(currentUser);

    // Aggregate in SQL (COUNT per status) instead of fetching every record and
    // counting in JS — avoids the limit-truncated, incorrect totals and the
    // large row transfer.
    const recordsWhere = {
      organizationId: requireOrganizationId(currentUser),
      ...visibilityScope(currentUser),
      ...dateFilter(filter),
    };

    const [present, late, absent, staffCount] = await Promise.all([
      db.staff_attendance_records.count({
        where: { ...recordsWhere, status: STAFF_ATTENDANCE_STATUSES.PRESENT },
      }),
      db.staff_attendance_records.count({
        where: { ...recordsWhere, status: STAFF_ATTENDANCE_STATUSES.LATE },
      }),
      db.staff_attendance_records.count({
        where: { ...recordsWhere, status: STAFF_ATTENDANCE_STATUSES.ABSENT },
      }),
      db.users.count({
        where: {
          organizationId: requireOrganizationId(currentUser),
          ...staffCountScope(currentUser),
        },
        include: [
          {
            model: db.roles,
            as: 'app_role',
            required: true,
            where: {
              name: { [Op.in]: STAFF_ATTENDANCE_INTERNAL_ROLE_NAMES },
            },
          },
        ],
      }),
    ]);

    return {
      staffCount,
      recordsCount: present + late + absent,
      present,
      late,
      absent,
    };
  }

  static async upsertRecord(
    data: StaffAttendanceInput,
    currentUser?: CurrentUser,
  ) {
    assertCanFillStaffAttendance(currentUser);

    const userId = requireString(data.userId);
    const attendanceDate = requiredIsoDate(data.date);
    const status = validateStatus(data.status);
    const note = validateNote(data.note);

    const staffUser = await db.users.findOne({
      where: {
        id: userId,
        ...staffUserScope(currentUser),
      },
      include: [
        {
          model: db.roles,
          as: 'app_role',
          attributes: ['name'],
        },
      ],
    });

    if (!staffUser) {
      throw new ForbiddenError();
    }

    const plain = staffUser.get({ plain: true }) as {
      id: string;
      firstName?: string | null;
      lastName?: string | null;
      email?: string | null;
      campusId?: string | null;
      app_role?: { name?: string | null } | null;
    };

    return withTransaction(async (transaction) => {
      const existing = await db.staff_attendance_records.findOne({
        where: {
          organizationId: requireOrganizationId(currentUser),
          userId,
          attendance_date: attendanceDate,
        },
        transaction,
      });
      const payload = {
        attendance_date: attendanceDate,
        status,
        note,
        user_name: staffUserName(plain),
        user_role: plain.app_role?.name ?? null,
        organizationId: requireOrganizationId(currentUser),
        campusId: plain.campusId ?? null,
        userId,
        updatedById: requireUserId(currentUser),
      };
      const saved = existing
        ? await existing.update(payload, { transaction })
        : await db.staff_attendance_records.create(
            { ...payload, createdById: requireUserId(currentUser) },
            { transaction },
          );

      return toRecordDto(saved);
    });
  }
}

export default StaffAttendanceService;
