import { ENV_OPTIONAL_NUMBER_KEYS_MAPPING, SEQUELIZE_INSTANCE_TOKEN } from '@humand-packages/common';
import { QueryTypes } from 'sequelize';
import { Sequelize } from 'sequelize-typescript';
import { Inject, Service } from 'typedi';
import { BaseRepositoryAdapter } from '../../../../../../dbAdapters/baseRepositoryAdapter';
import { OrphanedFaceAssignment } from '../../../../business/interfaces/orphanedFaceAssignment';
import { PendingFaceAssignment } from '../../../../business/interfaces/pendingFaceAssignment';
import {
    TimeTrackingFaceAssignment,
    TimeTrackingFaceAssignmentForCreation,
} from '../../../../business/models/timeTracking/timeTrackingFaceAssignments';
import { TimeTrackingFaceAssignmentSource } from '../../../constants/timeTracking/faceAssignmentSource';
import { TimeTrackingFaceAssignmentDAO } from '../../../dataAccessObjects/timeTracking/timeTrackingFaceAssignment';
import {
    mapTimeTrackingFaceAssignmentDAOToModel,
    mapTimeTrackingFaceAssignmentModelToDAO,
} from '../../../mappers/timeTracking/timeTrackingFaceAssignmentMapper';
import TimeTrackingFaceAssignmentRepositoryPort from '../../../ports/timeTracking/timeTrackingFaceAssignmentRepositoryPort';
import { OrphanedFaceAssignmentRow } from './orphanedFaceAssignmentRow';
import { PendingFaceAssignmentRow } from './pendingFaceAssignmentRow';

@Service()
export default class TimeTrackingFaceAssignmentRepositoryAdapter extends BaseRepositoryAdapter implements TimeTrackingFaceAssignmentRepositoryPort {
    constructor(
        @Inject(ENV_OPTIONAL_NUMBER_KEYS_MAPPING.BULK_CREATE_CHUNK_SIZE) bulkCreateChunkSize: number,
        @Inject(SEQUELIZE_INSTANCE_TOKEN) sequelize: Sequelize,
    ) {
        super(bulkCreateChunkSize, sequelize);
    }

    public getAll = async (
        instanceId: number,
        userId: number,
        options?: { source?: TimeTrackingFaceAssignmentSource; regionId?: number; siteId?: number },
    ): Promise<TimeTrackingFaceAssignment[]> => {
        const faceAssignments = await TimeTrackingFaceAssignmentDAO.findAll({ where: { instanceId, ...options, userId } });
        return faceAssignments.map(mapTimeTrackingFaceAssignmentDAOToModel);
    };

    public create = async (faceAssignment: TimeTrackingFaceAssignmentForCreation): Promise<TimeTrackingFaceAssignment> => {
        const faceAssignmentDAO = mapTimeTrackingFaceAssignmentModelToDAO(faceAssignment);
        await faceAssignmentDAO.save();
        return mapTimeTrackingFaceAssignmentDAOToModel(faceAssignmentDAO);
    };

    public bulkCreate = async (faceAssignments: TimeTrackingFaceAssignmentForCreation[]): Promise<TimeTrackingFaceAssignment[]> => {
        const createdFaceAssignments = await TimeTrackingFaceAssignmentDAO.bulkCreate(faceAssignments, { ignoreDuplicates: true });
        return createdFaceAssignments.map(mapTimeTrackingFaceAssignmentDAOToModel);
    };

    public deleteAll = async (
        instanceId: number,
        userId: number,
        options?: { source?: TimeTrackingFaceAssignmentSource; regionId?: number; siteId?: number },
    ): Promise<void> => {
        await TimeTrackingFaceAssignmentDAO.destroy({ where: { instanceId, ...options, userId } });
    };

    public getAllBySiteId = async (
        instanceId: number,
        siteId: number,
        source: TimeTrackingFaceAssignmentSource,
    ): Promise<TimeTrackingFaceAssignment[]> => {
        const faceAssignments = await TimeTrackingFaceAssignmentDAO.findAll({ where: { instanceId, siteId, source } });
        return faceAssignments.map(mapTimeTrackingFaceAssignmentDAOToModel);
    };

    public deleteByIds = async (instanceId: number, ids: number[]): Promise<void> => {
        await TimeTrackingFaceAssignmentDAO.destroy({ where: { id: ids, instanceId } });
    };

    public findPendingAssignments = async (options?: { limit?: number; maxInstances?: number }): Promise<PendingFaceAssignment[]> => {
        const instanceLimit = options?.maxInstances;
        const rowLimit = options?.limit;

        const replacements: { maxInstances?: number; limit?: number } = {};
        if (instanceLimit !== undefined) replacements.maxInstances = instanceLimit;
        if (rowLimit !== undefined) replacements.limit = rowLimit;

        const rows = await this.sequelize.query<PendingFaceAssignmentRow>(
            `WITH pending AS (
                SELECT
                    s."instanceId" AS "instanceId",
                    sur."userId" AS "userId",
                    frc."referenceImage" AS "referenceImage",
                    COUNT(*) AS "missingSitesCount"
                FROM "SiteUserRegion" sur
                JOIN "Sites" s ON s.id = sur."siteId" AND s."deletedAt" IS NULL
                JOIN "Users" u ON u.id = sur."userId" AND u."deletedAt" IS NULL
                JOIN "FaceRecognitionConfigurations" frc
                    ON frc."instanceId" = s."instanceId"
                    AND frc."userId" = sur."userId"
                    AND frc."referenceImage" IS NOT NULL
                    AND frc."deletedAt" IS NULL
                LEFT JOIN "TimeTrackingFaceAssignments" ttfa
                    ON ttfa."instanceId" = s."instanceId"
                    AND ttfa."userId" = sur."userId"
                    AND ttfa."siteId" = sur."siteId"
                    AND ttfa."deletedAt" IS NULL
                WHERE sur."deletedAt" IS NULL
                  AND ttfa.id IS NULL
                GROUP BY s."instanceId", sur."userId", frc."referenceImage"
            ),
            capped_instances AS (
                SELECT DISTINCT "instanceId"
                FROM pending
                ORDER BY "instanceId"
                ${instanceLimit !== undefined ? 'LIMIT :maxInstances' : ''}
            )
            SELECT p."instanceId", p."userId", p."referenceImage", p."missingSitesCount"
            FROM pending p
            JOIN capped_instances c ON c."instanceId" = p."instanceId"
            ORDER BY p."missingSitesCount" DESC, p."instanceId", p."userId"
            ${rowLimit !== undefined ? 'LIMIT :limit' : ''}`,
            {
                type: QueryTypes.SELECT,
                replacements,
            },
        );

        return rows.map((row) => ({
            instanceId: row.instanceId,
            userId: row.userId,
            referenceImage: row.referenceImage,
            missingSitesCount: Number(row.missingSitesCount),
        }));
    };

    // Every FACIAL_RECOGNITION assignment that should NOT exist in its current form. Four staleness
    // sources, all deleted by reconcileOrphanedAssignments:
    //   1. membership gone  — sur."userId" IS NULL (user no longer at the site; PK is (userId, siteId)),
    //   2. no face config   — frc."userId" IS NULL,
    //   3. reference reset   — frc."referenceImage" IS NULL,
    //   4. reference changed — frc."referenceImage" <> ttfa."referenceImage" (photo re-uploaded).
    // (1)-(3) are removed for good. (4) is removed here too, then re-enrolled with the CURRENT image by
    // reconcilePendingAssignments — which is WHY the reconcile endpoint runs orphaned (delete) BEFORE
    // pending (enroll): once the stale row is gone the site looks unassigned, so pending re-indexes it
    // with frc."referenceImage". Both deletedAt filters live in the ON, not WHERE, so the anti-joins hold.
    public findOrphanedAssignments = async (options?: { limit?: number; maxInstances?: number }): Promise<OrphanedFaceAssignment[]> => {
        const instanceLimit = options?.maxInstances;
        const rowLimit = options?.limit;

        const replacements: { source: string; maxInstances?: number; limit?: number } = {
            source: TimeTrackingFaceAssignmentSource.FACIAL_RECOGNITION,
        };
        if (instanceLimit !== undefined) replacements.maxInstances = instanceLimit;
        if (rowLimit !== undefined) replacements.limit = rowLimit;

        const rows = await this.sequelize.query<OrphanedFaceAssignmentRow>(
            `WITH orphaned AS (
                SELECT
                    ttfa."instanceId" AS "instanceId",
                    ttfa."userId" AS "userId",
                    ttfa."siteId" AS "siteId",
                    ttfa."id" AS "assignmentId",
                    ttfa."faceId" AS "faceId"
                FROM "TimeTrackingFaceAssignments" ttfa
                LEFT JOIN "SiteUserRegion" sur
                    ON sur."userId" = ttfa."userId"
                    AND sur."siteId" = ttfa."siteId"
                    AND sur."deletedAt" IS NULL
                LEFT JOIN "FaceRecognitionConfigurations" frc
                    ON frc."instanceId" = ttfa."instanceId"
                    AND frc."userId" = ttfa."userId"
                    AND frc."deletedAt" IS NULL
                WHERE ttfa."deletedAt" IS NULL
                  AND ttfa."source" = :source
                  AND (
                        sur."userId" IS NULL
                     OR frc."userId" IS NULL
                     OR frc."referenceImage" IS NULL
                     OR frc."referenceImage" <> ttfa."referenceImage"
                  )
            ),
            capped_instances AS (
                SELECT DISTINCT "instanceId"
                FROM orphaned
                ORDER BY "instanceId"
                ${instanceLimit !== undefined ? 'LIMIT :maxInstances' : ''}
            )
            SELECT o."instanceId", o."userId", o."siteId", o."assignmentId", o."faceId"
            FROM orphaned o
            JOIN capped_instances c ON c."instanceId" = o."instanceId"
            ORDER BY o."instanceId", o."siteId", o."userId"
            ${rowLimit !== undefined ? 'LIMIT :limit' : ''}`,
            {
                replacements,
                type: QueryTypes.SELECT,
            },
        );

        return rows.map((row) => ({
            assignmentId: row.assignmentId,
            faceId: row.faceId,
            instanceId: row.instanceId,
            siteId: row.siteId,
            userId: row.userId,
        }));
    };
}
