import {
  Op,
  type Includeable,
  type InferAttributes,
  type InferCreationAttributes,
  type WhereAttributeHash,
} from 'sequelize';
import db from '@/db/models';
import {
  removeRecord,
  deleteRecordsByIds,
  autocompleteByField,
} 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 FileDBApi from '@/db/api/file';
import type { Invoices } from '@/db/models/invoices';
import type { CurrentUser, DbApiOptions, FileInput } from '@/db/api/types';

type InvoicesData = Partial<InferCreationAttributes<Invoices>> & {
  organization?: string | null;
  campus?: string | null;
  student?: string | null;
  fee_plan?: string | null;
  attachments?: FileInput | FileInput[] | null;
};

type NumberRange = Array<number | string | null | undefined>;
type DateRange = Array<string | null | undefined>;

interface InvoicesFilter {
  limit?: number | string;
  page?: number | string;
  id?: string;
  invoice_number?: string;
  notes?: string;
  issue_dateRange?: DateRange;
  due_dateRange?: DateRange;
  subtotalRange?: NumberRange;
  discount_amountRange?: NumberRange;
  tax_amountRange?: NumberRange;
  total_amountRange?: NumberRange;
  balance_dueRange?: NumberRange;
  active?: boolean | string;
  status?: string;
  campus?: string;
  student?: string;
  fee_plan?: string;
  organization?: string;
  createdAtRange?: DateRange;
  field?: string;
  sort?: string;
}

const NO_USER: CurrentUser = { id: null };

function invoicesTableName(): string {
  const name = db.invoices.getTableName();
  return typeof name === 'string' ? name : name.tableName;
}

/** Apply a `>= / <=` range to a where field, preserving an existing bound. */
function applyRange(
  where: WhereAttributeHash,
  field: string,
  range: NumberRange | DateRange | undefined,
): WhereAttributeHash {
  if (!range) return where;
  const [start, end] = range;
  let next = where;
  if (start !== undefined && start !== null && start !== '') {
    next = { ...next, [field]: { [Op.gte]: start } };
  }
  if (end !== undefined && end !== null && end !== '') {
    const existing = next[field];
    next = {
      ...next,
      [field]: {
        ...(typeof existing === 'object' && existing !== null ? existing : {}),
        [Op.lte]: end,
      },
    };
  }
  return next;
}

class InvoicesDBApi {
  static async create(
    data: InvoicesData,
    options?: DbApiOptions,
  ): Promise<Invoices> {
    const currentUser = options?.currentUser ?? NO_USER;
    const transaction = options?.transaction;

    const invoices = await db.invoices.create(
      {
        id: data.id || undefined,
        invoice_number: data.invoice_number || null,
        issue_date: data.issue_date || null,
        due_date: data.due_date || null,
        subtotal: data.subtotal || null,
        discount_amount: data.discount_amount || null,
        tax_amount: data.tax_amount || null,
        total_amount: data.total_amount || null,
        balance_due: data.balance_due || null,
        status: data.status || null,
        notes: data.notes || null,
        importHash: data.importHash || null,
        createdById: currentUser.id,
        updatedById: currentUser.id,
      },
      { transaction },
    );

    await invoices.setOrganization(currentUser.organizationId ?? undefined, {
      transaction,
    });
    await invoices.setCampus(data.campus ?? undefined, { transaction });
    await invoices.setStudent(data.student ?? undefined, { transaction });
    await invoices.setFee_plan(data.fee_plan ?? undefined, { transaction });

    await FileDBApi.replaceRelationFiles(
      {
        belongsTo: invoicesTableName(),
        belongsToColumn: 'attachments',
        belongsToId: invoices.id,
      },
      data.attachments,
      options,
    );

    return invoices;
  }

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

    const invoicesData = data.map((item, index) => ({
      id: item.id || undefined,
      invoice_number: item.invoice_number || null,
      issue_date: item.issue_date || null,
      due_date: item.due_date || null,
      subtotal: item.subtotal || null,
      discount_amount: item.discount_amount || null,
      tax_amount: item.tax_amount || null,
      total_amount: item.total_amount || null,
      balance_due: item.balance_due || null,
      status: item.status || null,
      notes: item.notes || null,
      importHash: item.importHash || null,
      createdById: currentUser.id,
      updatedById: currentUser.id,
      createdAt: new Date(Date.now() + index * BULK_IMPORT_TIMESTAMP_STEP_MS),
    }));

    const invoices = await db.invoices.bulkCreate(invoicesData, { transaction });

    for (let i = 0; i < invoices.length; i++) {
      await FileDBApi.replaceRelationFiles(
        {
          belongsTo: invoicesTableName(),
          belongsToColumn: 'attachments',
          belongsToId: invoices[i].id,
        },
        data[i].attachments,
        options,
      );
    }

    return invoices;
  }

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

    const invoices = await db.invoices.findByPk(id, { transaction });

    if (!invoices) {
      return null;
    }

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

    if (data.invoice_number !== undefined)
      updatePayload.invoice_number = data.invoice_number;
    if (data.issue_date !== undefined)
      updatePayload.issue_date = data.issue_date;
    if (data.due_date !== undefined) updatePayload.due_date = data.due_date;
    if (data.subtotal !== undefined) updatePayload.subtotal = data.subtotal;
    if (data.discount_amount !== undefined)
      updatePayload.discount_amount = data.discount_amount;
    if (data.tax_amount !== undefined)
      updatePayload.tax_amount = data.tax_amount;
    if (data.total_amount !== undefined)
      updatePayload.total_amount = data.total_amount;
    if (data.balance_due !== undefined)
      updatePayload.balance_due = data.balance_due;
    if (data.status !== undefined) updatePayload.status = data.status;
    if (data.notes !== undefined) updatePayload.notes = data.notes;

    updatePayload.updatedById = currentUser.id;

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

    if (data.organization !== undefined) {
      const orgId = globalAccess
        ? data.organization
        : currentUser.organizationId;
      await invoices.setOrganization(orgId ?? undefined, { transaction });
    }
    if (data.campus !== undefined) {
      await invoices.setCampus(data.campus ?? undefined, { transaction });
    }
    if (data.student !== undefined) {
      await invoices.setStudent(data.student ?? undefined, { transaction });
    }
    if (data.fee_plan !== undefined) {
      await invoices.setFee_plan(data.fee_plan ?? undefined, { transaction });
    }

    await FileDBApi.replaceRelationFiles(
      {
        belongsTo: invoicesTableName(),
        belongsToColumn: 'attachments',
        belongsToId: invoices.id,
      },
      data.attachments,
      options,
    );

    return invoices;
  }

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

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

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

    const invoices = await db.invoices.findOne({ where, transaction });

    if (!invoices) {
      return null;
    }

    const output: Record<string, unknown> = invoices.get({ plain: true });

    const [
      payments_invoice,
      organization,
      campus,
      student,
      fee_plan,
      attachments,
    ] = await Promise.all([
      invoices.getPayments_invoice({ transaction }),
      invoices.getOrganization({ transaction }),
      invoices.getCampus({ transaction }),
      invoices.getStudent({ transaction }),
      invoices.getFee_plan({ transaction }),
      invoices.getAttachments({ transaction }),
    ]);
    output.payments_invoice = payments_invoice;
    output.organization = organization;
    output.campus = campus;
    output.student = student;
    output.fee_plan = fee_plan;
    output.attachments = attachments;

    return output;
  }

  static async findAll(
    filter: InvoicesFilter,
    globalAccess: boolean,
    options?: DbApiOptions,
  ): Promise<{ rows: Invoices[]; 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;
    }

    const include: Includeable[] = [
      { model: db.organizations, as: 'organization' },
      {
        model: db.campuses,
        as: 'campus',
        where: filter.campus
          ? {
              [Op.or]: [
                {
                  id: {
                    [Op.in]: filter.campus.split('|').map((t) => Utils.uuid(t)),
                  },
                },
                {
                  name: {
                    [Op.or]: filter.campus
                      .split('|')
                      .map((t) => ({ [Op.iLike]: `%${t}%` })),
                  },
                },
              ],
            }
          : {},
      },
      {
        model: db.students,
        as: 'student',
        where: filter.student
          ? {
              [Op.or]: [
                {
                  id: {
                    [Op.in]: filter.student.split('|').map((t) => Utils.uuid(t)),
                  },
                },
                {
                  student_number: {
                    [Op.or]: filter.student
                      .split('|')
                      .map((t) => ({ [Op.iLike]: `%${t}%` })),
                  },
                },
              ],
            }
          : {},
      },
      {
        model: db.fee_plans,
        as: 'fee_plan',
        where: filter.fee_plan
          ? {
              [Op.or]: [
                {
                  id: {
                    [Op.in]: filter.fee_plan
                      .split('|')
                      .map((t) => Utils.uuid(t)),
                  },
                },
                {
                  name: {
                    [Op.or]: filter.fee_plan
                      .split('|')
                      .map((t) => ({ [Op.iLike]: `%${t}%` })),
                  },
                },
              ],
            }
          : {},
      },
      { model: db.file, as: 'attachments' },
    ];

    if (filter.id) {
      where = { ...where, id: Utils.uuid(filter.id) };
    }
    if (filter.invoice_number) {
      where = {
        ...where,
        [Op.and]: Utils.ilike('invoices', 'invoice_number', filter.invoice_number),
      };
    }
    if (filter.notes) {
      where = {
        ...where,
        [Op.and]: Utils.ilike('invoices', 'notes', filter.notes),
      };
    }

    where = applyRange(where, 'issue_date', filter.issue_dateRange);
    where = applyRange(where, 'due_date', filter.due_dateRange);
    where = applyRange(where, 'subtotal', filter.subtotalRange);
    where = applyRange(where, 'discount_amount', filter.discount_amountRange);
    where = applyRange(where, 'tax_amount', filter.tax_amountRange);
    where = applyRange(where, 'total_amount', filter.total_amountRange);
    where = applyRange(where, 'balance_due', filter.balance_dueRange);

    if (filter.active !== undefined) {
      where = {
        ...where,
        active: filter.active === true || filter.active === 'true',
      };
    }
    if (filter.status) {
      where = { ...where, status: filter.status };
    }
    if (filter.organization) {
      const listItems = filter.organization
        .split('|')
        .map((item) => Utils.uuid(item));
      where = { ...where, organizationId: { [Op.or]: listItems } };
    }
    where = applyRange(where, 'createdAt', filter.createdAtRange);

    if (globalAccess) {
      delete where.organizationId;
    }

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

    const { rows, count } = await db.invoices.findAndCountAll({
      where,
      include,
      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.invoices,
      'invoice_number',
      query,
      limit,
      offset,
      globalAccess,
      organizationId,
    );
  }
}

export default InvoicesDBApi;
