import {
  Op,
  type InferAttributes,
  type InferCreationAttributes,
  type WhereAttributeHash,
} from 'sequelize';
import db from '@/db/models';
import {
  removeRecord,
  deleteRecordsByIds,
  autocompleteByField,
  findOwnedByPk,
  tenantWhere,
} from '@/db/api/shared/repository';
import { BULK_IMPORT_TIMESTAMP_STEP_MS } from '@/shared/constants/database';
import { resolvePagination } from '@/shared/constants/pagination';
import Utils from '@/db/utils';
import ValidationError from '@/shared/errors/validation';
import type { Schools } from '@/db/models/schools';
import type { CurrentUser, DbApiOptions } from '@/db/api/types';

type SchoolsData = Partial<InferCreationAttributes<Schools>> & {
  organization?: string | null;
};

interface SchoolsFilter {
  limit?: number | string;
  page?: number | string;
  id?: string;
  name?: string;
  code?: string;
  address?: string;
  phone?: string;
  email?: string;
  active?: boolean | string;
  organization?: string;
  field?: string;
  sort?: string;
}

const NO_USER: CurrentUser = { id: null };

class SchoolsDBApi {
  static async create(
    data: SchoolsData,
    options?: DbApiOptions,
  ): Promise<Schools> {
    const currentUser = options?.currentUser ?? NO_USER;
    const transaction = options?.transaction;

    if (data.name == null || data.code == null) {
      throw new ValidationError();
    }

    const school = await db.schools.create(
      {
        id: data.id || undefined,
        name: data.name,
        code: data.code,
        address: data.address || null,
        phone: data.phone || null,
        email: data.email || null,
        description: data.description || null,
        logo: data.logo || null,
        active: data.active || false,
        importHash: data.importHash || null,
        createdById: currentUser.id,
        updatedById: currentUser.id,
      },
      { transaction },
    );

    // Non-global creators are pinned to their own org (tenant isolation);
    // global creators (no own org) may target an org explicitly via `data`.
    await school.setOrganization(
      currentUser.organizationId ?? data.organization ?? undefined,
      { transaction },
    );

    return school;
  }

  static async bulkImport(
    data: SchoolsData[],
    options?: DbApiOptions,
  ): Promise<Schools[]> {
    const currentUser = options?.currentUser ?? NO_USER;
    const transaction = options?.transaction;

    const schoolsData = data.map((item, index) => {
      if (item.name == null || item.code == null) {
        throw new ValidationError();
      }
      return {
        id: item.id || undefined,
        name: item.name,
        code: item.code,
        address: item.address || null,
        phone: item.phone || null,
        email: item.email || null,
        description: item.description || null,
        logo: item.logo || null,
        active: item.active || false,
        importHash: item.importHash || null,
        organizationId: currentUser.organizationId ?? null,
        createdById: currentUser.id,
        updatedById: currentUser.id,
        createdAt: new Date(Date.now() + index * BULK_IMPORT_TIMESTAMP_STEP_MS),
      };
    });

    return db.schools.bulkCreate(schoolsData, { transaction });
  }

  static async update(
    id: string,
    data: SchoolsData,
    options?: DbApiOptions,
  ): Promise<Schools | null> {
    const currentUser = options?.currentUser ?? NO_USER;
    const transaction = options?.transaction;
    const globalAccess = currentUser.app_role?.globalAccess;

    const school = await findOwnedByPk(db.schools, id, options);

    if (!school) {
      return null;
    }

    const updatePayload: Partial<InferAttributes<Schools>> = {};

    if (data.name !== undefined) updatePayload.name = data.name;
    if (data.code !== undefined) updatePayload.code = data.code;
    if (data.address !== undefined) updatePayload.address = data.address;
    if (data.phone !== undefined) updatePayload.phone = data.phone;
    if (data.email !== undefined) updatePayload.email = data.email;
    if (data.description !== undefined)
      updatePayload.description = data.description;
    if (data.logo !== undefined) updatePayload.logo = data.logo;
    if (data.active !== undefined) updatePayload.active = data.active;

    updatePayload.updatedById = currentUser.id;

    await school.update(updatePayload, { transaction });

    if (data.organization !== undefined) {
      const orgId = globalAccess
        ? data.organization
        : currentUser.organizationId;
      await school.setOrganization(orgId ?? undefined, { transaction });
    }

    return school;
  }

  static async deleteByIds(
    ids: string[],
    options?: DbApiOptions,
  ): Promise<Schools[]> {
    return deleteRecordsByIds(db.schools, ids, options);
  }

  static async remove(
    id: string,
    options?: DbApiOptions,
  ): Promise<Schools | null> {
    return removeRecord(db.schools, id, options);
  }

  static async findBy(
    where: WhereAttributeHash,
    options?: DbApiOptions,
  ): Promise<Record<string, unknown> | null> {
    const transaction = options?.transaction;

    const school = await db.schools.findOne({
      where: { ...where, ...tenantWhere(options?.currentUser) },
      include: [
        { model: db.organizations, as: 'organization' },
        { model: db.campuses, as: 'campuses_school' },
      ],
      transaction,
    });

    if (!school) {
      return null;
    }

    return school.get({ plain: true });
  }

  static async findAll(
    filter: SchoolsFilter,
    globalAccess: boolean,
    options?: DbApiOptions,
  ): Promise<{ rows: Schools[]; count: number }> {
    const { limit, offset } = resolvePagination(filter.limit, filter.page);

    let where: WhereAttributeHash = {};

    const userOrganizations = options?.currentUser?.organizations?.id ?? null;
    if (userOrganizations && options?.currentUser?.organizationId) {
      where.organizationId = options.currentUser.organizationId;
    }

    if (filter.id) {
      where = { ...where, id: Utils.uuid(filter.id) };
    }
    if (filter.name) {
      where = { ...where, [Op.and]: Utils.ilike('schools', 'name', filter.name) };
    }
    if (filter.code) {
      where = { ...where, [Op.and]: Utils.ilike('schools', 'code', filter.code) };
    }
    if (filter.active !== undefined) {
      where = {
        ...where,
        active: filter.active === true || filter.active === 'true',
      };
    }
    if (filter.organization) {
      const listItems = filter.organization
        .split('|')
        .map((item) => Utils.uuid(item));
      where = { ...where, organizationId: { [Op.or]: listItems } };
    }

    if (globalAccess) {
      delete where.organizationId;
    }

    const order: [string, string][] =
      filter.field && filter.sort
        ? [[filter.field, filter.sort]]
        : [['createdAt', 'desc']];

    const { rows, count } = await db.schools.findAndCountAll({
      where,
      include: [{ model: db.organizations, as: 'organization' }],
      distinct: true,
      order,
      transaction: options?.transaction,
      limit: !options?.countOnly && limit ? limit : undefined,
      offset: !options?.countOnly && offset ? offset : undefined,
    });

    return { rows: options?.countOnly ? [] : rows, count };
  }

  static async findAllAutocomplete(
    query: string | undefined,
    limit: number | undefined,
    offset: number | undefined,
    globalAccess: boolean,
    organizationId: string | undefined,
  ): Promise<Array<{ id: string; label: string | null }>> {
    return autocompleteByField(
      db.schools,
      'name',
      query,
      limit,
      offset,
      globalAccess,
      organizationId,
    );
  }
}

export default SchoolsDBApi;
