import { Injectable } from '@nestjs/common';
import { Prisma } from '@/generated/prisma/client';
import { PrismaService } from '@/prisma/prisma.service';

// Stakeholder type used as the issue owner working group.
const PRIVATE_SECTOR_TYPE_NAME = 'Private Sector';

// Stakeholder type that can be assigned as a responsible government agency.
const GOVERNMENT_AGENCY_TYPE_NAME = 'Ministry';

// Plain data shape for one government-agency assignment on an issue.
// The repository takes this and translates it into Prisma's relational input.
export interface IssueGovernmentAgencyInput {
  stakeholderId: number;
  agencyOrder: number;
}

// Plain data shape for creating a working-group issue. The repository
// converts this into Prisma's `connect`/`create` shape internally so that
// the service never needs to know about Prisma's input types.
export interface CreateIssueData {
  title: string;
  description: string;
  recommendation: string;
  attachment: string | null;
  issueStatusId: number;
  categoryId: number;
  meetingRequestId?: number;
  userId: number;
  stakeholderId: number;
  governmentAgencies: IssueGovernmentAgencyInput[];
}

// Plain data shape for updating a working-group issue. Every field is
// optional — only the provided ones are pushed into Prisma.
export interface UpdateIssueData {
  title?: string;
  description?: string;
  recommendation?: string;
  attachment?: string | null;
  issueStatusId?: number;
  categoryId?: number;
  meetingRequestId?: number;
}

// Allowed sort columns (mirrored in the DTO whitelist).
export type IssueSortField = 'id' | 'title' | 'createdAt' | 'updatedAt';
export type IssueSortOrder = 'asc' | 'desc';

// Plain filter shape consumed by `findPaginated`. The repository translates
// these into Prisma's where clause so the service never touches Prisma types.
export interface IssueListFilters {
  search?: string;
  issueStatusId?: number;
  issueStatusIds?: number[];
  categoryId?: number;
  categoryIds?: number[];
  meetingRequestId?: number;
  userId?: number;
  ownerStakeholderId?: number;
  ownerStakeholderIds?: number[];
  excludeOwnerStakeholderId?: number;
  ownerStakeholderTypeName?: string;
  stakeholderId?: number;
  primaryAgencyId?: number;
  primaryAgencyIds?: number[];
  createdFrom?: Date;
  createdTo?: Date;
  years?: number[];
  hasAttachment?: boolean;
}

// Order in which the pivot row is considered the "primary" agency.
// Same convention used in the create/update flow (see service tests on dto).
const PRIMARY_AGENCY_ORDER = 1;

@Injectable()
export class WorkingGroupIssuesRepository {
  constructor(private readonly prisma: PrismaService) {}

  // Single source of truth for the relations the API returns with an issue.
  // Reused by every read so the response shape stays consistent.
  private readonly issueInclude = {
    issueStatus: {
      select: { id: true, code: true, name: true },
    },
    category: {
      select: { id: true, name: true },
    },
    meetingRequest: {
      select: {
        id: true,
        title: true,
        // The scheduled meeting(s) for this request. The newest one's date is
        // used as the issue's "Meeting Date". Newest first so [0] is current.
        meetings: {
          where: { deletedAt: null },
          orderBy: { meetingDate: 'desc' },
          select: {
            id: true,
            meetingDate: true,
            startTime: true,
            endTime: true,
          },
        },
      },
    },
    stakeholder: {
      select: {
        id: true,
        name: true,
        logo: true,
        stakeholderType: {
          select: { id: true, name: true },
        },
      },
    },
    user: {
      select: {
        id: true,
        email: true,
        name: true,
        position: true,
        avatar: true,
      },
    },
    governmentAgencies: {
      orderBy: { agencyOrder: 'asc' },
      select: {
        agencyOrder: true,
        stakeholder: {
          select: {
            id: true,
            name: true,
            logo: true,
            stakeholderType: {
              select: { id: true, name: true },
            },
          },
        },
      },
    },
  } satisfies Prisma.IssuesInclude;

  // List non-deleted issues, newest first.
  findAll() {
    return this.prisma.issues.findMany({
      where: { deletedAt: null },
      orderBy: { createdAt: 'desc' },
      include: this.issueInclude,
    });
  }

  // Find the active Private Sector stakeholder assigned to the current user.
  findPrivateSectorStakeholderByUserId(userId: number) {
    return this.prisma.stakeholderUser
      .findFirst({
        where: {
          userId,
          stakeholder: {
            active: true,
            deletedAt: null,
            stakeholderType: {
              name: {
                equals: PRIVATE_SECTOR_TYPE_NAME,
                mode: 'insensitive',
              },
            },
          },
        },
        select: {
          stakeholder: {
            select: {
              id: true,
              name: true,
              relatedStakeholderId: true,
            },
          },
        },
      })
      .then((membership) => membership?.stakeholder ?? null);
  }

  // Return a single page of non-deleted issues — filtered, sorted, and
  // counted in one transaction so the totals stay consistent with the
  // page that was just read.
  async findPaginated(params: {
    filters: IssueListFilters;
    sortBy: IssueSortField;
    sortOrder: IssueSortOrder;
    skip: number;
    take: number;
  }) {
    const where = this.buildListWhere(params.filters);
    const orderBy: Prisma.IssuesOrderByWithRelationInput = {
      [params.sortBy]: params.sortOrder,
    };

    const [items, total] = await this.prisma.$transaction([
      this.prisma.issues.findMany({
        where,
        orderBy,
        include: this.issueInclude,
        skip: params.skip,
        take: params.take,
      }),
      this.prisma.issues.count({ where }),
    ]);

    return { items, total };
  }

  // Return all matching non-deleted issues for export (capped for safety).
  findAllForExport(params: {
    filters: IssueListFilters;
    sortBy: IssueSortField;
    sortOrder: IssueSortOrder;
    maxRows?: number;
  }) {
    const where = this.buildListWhere(params.filters);
    const orderBy: Prisma.IssuesOrderByWithRelationInput = {
      [params.sortBy]: params.sortOrder,
    };

    return this.prisma.issues.findMany({
      where,
      orderBy,
      include: this.issueInclude,
      take: params.maxRows ?? 5000,
    });
  }

  // Find a single non-deleted issue, with the full include shape.
  findById(id: number) {
    return this.prisma.issues.findFirst({
      where: { id, deletedAt: null },
      include: this.issueInclude,
    });
  }

  // Lightweight existence check that does not pay for the relation joins.
  findMinimalById(id: number) {
    return this.prisma.issues.findFirst({
      where: { id, deletedAt: null },
      select: { id: true },
    });
  }

  // Insert an issue along with its government-agency assignments in one call.
  create(data: CreateIssueData) {
    return this.prisma.issues.create({
      data: {
        title: data.title,
        description: data.description,
        recommendation: data.recommendation,
        attachment: data.attachment,
        issueStatus: { connect: { id: data.issueStatusId } },
        category: { connect: { id: data.categoryId } },
        ...(data.meetingRequestId !== undefined && {
          meetingRequest: { connect: { id: data.meetingRequestId } },
        }),
        user: { connect: { id: data.userId } },
        stakeholder: { connect: { id: data.stakeholderId } },
        governmentAgencies: {
          create: data.governmentAgencies.map((agency) => ({
            agencyOrder: agency.agencyOrder,
            stakeholder: { connect: { id: agency.stakeholderId } },
          })),
        },
      },
      include: this.issueInclude,
    });
  }

  // Update an issue, optionally replacing its government agencies in the same
  // transaction. When `agencies` is undefined the assignments are left alone.
  async update(
    id: number,
    data: UpdateIssueData,
    agencies?: IssueGovernmentAgencyInput[],
  ) {
    const updateInput = this.buildUpdateInput(data);

    return this.prisma.$transaction(async (tx) => {
      // If the caller passed an agency list, wipe the existing pivot rows and
      // re-create them so the final state matches exactly what was sent in.
      if (agencies !== undefined) {
        await tx.governmentAgencyIssue.deleteMany({ where: { issueId: id } });

        if (agencies.length > 0) {
          await tx.governmentAgencyIssue.createMany({
            data: agencies.map((agency) => ({
              issueId: id,
              stakeholderId: agency.stakeholderId,
              agencyOrder: agency.agencyOrder,
            })),
          });
        }
      }

      return tx.issues.update({
        where: { id },
        data: updateInput,
        include: this.issueInclude,
      });
    });
  }

  // Mark an issue as soft-deleted by stamping `deletedAt`.
  softDelete(id: number) {
    return this.prisma.issues.update({
      where: { id },
      data: { deletedAt: new Date() },
      select: { id: true },
    });
  }

  // Dashboard counts: total active issues, one count per named status,
  // and the number of distinct active primary agencies. Every query uses
  // the same issue visibility filters and runs in one transaction.
  async getSummaryCounts(
    statusNames: {
      solved: string;
      inProgress: string;
      notAddressed: string;
    },
    options?: {
      ownerStakeholderId?: number;
      excludeOwnerStakeholderId?: number;
      ownerStakeholderTypeName?: string;
    },
  ) {
    const baseWhere: Prisma.IssuesWhereInput = {
      deletedAt: null,
      ...(options?.ownerStakeholderId !== undefined && {
        stakeholderId: options.ownerStakeholderId,
      }),
      ...(options?.excludeOwnerStakeholderId !== undefined && {
        stakeholderId: { not: options.excludeOwnerStakeholderId },
      }),
      ...(options?.ownerStakeholderTypeName !== undefined && {
        stakeholder: {
          stakeholderType: {
            name: {
              equals: options.ownerStakeholderTypeName,
              mode: 'insensitive',
            },
          },
        },
      }),
    };

    const buildStatusWhere = (name: string): Prisma.IssuesWhereInput => ({
      ...baseWhere,
      issueStatus: {
        deletedAt: null,
        name: { equals: name, mode: 'insensitive' },
      },
    });

    const [totalIssues, solved, inProgress, notAddressed, primaryAgencies] =
      await this.prisma.$transaction([
        this.prisma.issues.count({ where: baseWhere }),
        this.prisma.issues.count({
          where: buildStatusWhere(statusNames.solved),
        }),
        this.prisma.issues.count({
          where: buildStatusWhere(statusNames.inProgress),
        }),
        this.prisma.issues.count({
          where: buildStatusWhere(statusNames.notAddressed),
        }),
        this.prisma.governmentAgencyIssue.findMany({
          where: {
            agencyOrder: PRIMARY_AGENCY_ORDER,
            issue: baseWhere,
            stakeholder: {
              active: true,
              deletedAt: null,
            },
          },
          distinct: ['stakeholderId'],
          select: {
            stakeholderId: true,
          },
        }),
      ]);

    return {
      totalIssues,
      solved,
      inProgress,
      notAddressed,
      totalPrimaryAgencies: primaryAgencies.length,
    };
  }

  // --- Lookups for form dropdowns ----------------------------------------

  // All non-deleted issue statuses, sorted by id (insertion order).
  listIssueStatuses() {
    return this.prisma.issueStatuses.findMany({
      where: { deletedAt: null },
      orderBy: { id: 'asc' },
      select: { id: true, code: true, name: true },
    });
  }

  // All non-deleted issue categories, sorted by id (insertion order).
  listCategories() {
    return this.prisma.categories.findMany({
      where: { deletedAt: null },
      orderBy: { id: 'asc' },
      select: { id: true, name: true },
    });
  }

  // Active stakeholders that qualify as government agencies.
  listGovernmentAgencies() {
    return this.prisma.stakeholder.findMany({
      where: this.buildGovernmentAgencyWhere(),
      orderBy: { name: 'asc' },
      select: {
        id: true,
        name: true,
        logo: true,
        stakeholderType: { select: { id: true, name: true } },
      },
    });
  }

  // One active ministry stakeholder used as the auto-selected agency.
  findGovernmentAgencyById(id: number) {
    return this.prisma.stakeholder.findFirst({
      where: {
        id,
        ...this.buildGovernmentAgencyWhere(),
      },
      select: {
        id: true,
        name: true,
        logo: true,
        stakeholderType: { select: { id: true, name: true } },
      },
    });
  }

  // --- Existence checks (used by the service for validation) -------------

  findIssueStatusById(id: number) {
    return this.prisma.issueStatuses.findFirst({
      where: { id, deletedAt: null },
      select: { id: true },
    });
  }

  // Case-insensitive lookup used by the create/update uniqueness checks.
  // `excludeId` lets the update flow ignore the row being edited so it does
  // not collide with itself.
  findIssueStatusByName(name: string, excludeId?: number) {
    return this.prisma.issueStatuses.findFirst({
      where: {
        name: { equals: name, mode: 'insensitive' },
        deletedAt: null,
        ...(excludeId !== undefined && { id: { not: excludeId } }),
      },
      select: { id: true },
    });
  }

  // Same as findIssueStatusByName, but matches on the machine `code`.
  // Codes are stored in UPPER_SNAKE form; we still match case-insensitively
  // for safety since the column is plain VARCHAR.
  findIssueStatusByCode(code: string, excludeId?: number) {
    return this.prisma.issueStatuses.findFirst({
      where: {
        code: { equals: code, mode: 'insensitive' },
        deletedAt: null,
        ...(excludeId !== undefined && { id: { not: excludeId } }),
      },
      select: { id: true },
    });
  }

  findCategoryById(id: number) {
    return this.prisma.categories.findFirst({
      where: { id, deletedAt: null },
      select: { id: true },
    });
  }

  // Case-insensitive lookup used both by the default-category bootstrap
  // and by the create/update uniqueness checks. `excludeId` lets the
  // update flow ignore the row being edited so it does not collide with
  // itself.
  findCategoryByName(name: string, excludeId?: number) {
    return this.prisma.categories.findFirst({
      where: {
        name: { equals: name, mode: 'insensitive' },
        deletedAt: null,
        ...(excludeId !== undefined && { id: { not: excludeId } }),
      },
      select: { id: true },
    });
  }

  // Auto-create the "default" issue category when none exists yet.
  // Linked back to the user that triggered the creation so the audit trail is intact.
  createCategory(name: string, userId: number) {
    return this.prisma.categories.create({
      data: {
        name,
        user: { connect: { id: userId } },
      },
      select: { id: true },
    });
  }

  // --- CRUD operations for the category endpoints ------------------------

  // Full detail of one non-deleted category (used by GET :id / POST / PATCH).
  findCategoryDetailById(id: number) {
    return this.prisma.categories.findFirst({
      where: { id, deletedAt: null },
      select: {
        id: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Create a category and return its full detail shape.
  createCategoryDetail(name: string, userId: number) {
    return this.prisma.categories.create({
      data: {
        name,
        user: { connect: { id: userId } },
      },
      select: {
        id: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Update a category's name. Only the fields that are present in `data`
  // are pushed into Prisma.
  updateCategory(id: number, data: { name?: string }) {
    return this.prisma.categories.update({
      where: { id },
      data: {
        ...(data.name !== undefined && { name: data.name }),
      },
      select: {
        id: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Soft delete: mark the category as deleted without removing the row.
  softDeleteCategory(id: number) {
    return this.prisma.categories.update({
      where: { id },
      data: { deletedAt: new Date() },
      select: { id: true },
    });
  }

  // --- CRUD operations for the status endpoints --------------------------

  // Full detail of one non-deleted status (used by GET :id / POST / PATCH).
  findIssueStatusDetailById(id: number) {
    return this.prisma.issueStatuses.findFirst({
      where: { id, deletedAt: null },
      select: {
        id: true,
        code: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Create a status and return its full detail shape.
  createIssueStatusDetail(name: string, code: string, userId: number) {
    return this.prisma.issueStatuses.create({
      data: {
        name,
        code,
        user: { connect: { id: userId } },
      },
      select: {
        id: true,
        code: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Update a status's name / code. Only the fields that are present in
  // `data` are pushed into Prisma.
  updateIssueStatus(id: number, data: { name?: string; code?: string }) {
    return this.prisma.issueStatuses.update({
      where: { id },
      data: {
        ...(data.name !== undefined && { name: data.name }),
        ...(data.code !== undefined && { code: data.code }),
      },
      select: {
        id: true,
        code: true,
        name: true,
        userId: true,
        createdAt: true,
        updatedAt: true,
        deletedAt: true,
      },
    });
  }

  // Soft delete: mark the status as deleted without removing the row.
  softDeleteIssueStatus(id: number) {
    return this.prisma.issueStatuses.update({
      where: { id },
      data: { deletedAt: new Date() },
      select: { id: true },
    });
  }

  findMeetingRequestById(id: number) {
    return this.prisma.meetingRequests.findFirst({
      where: { id, deletedAt: null },
      select: { id: true },
    });
  }

  // Resolve a set of stakeholder ids, but only those that are active
  // government agencies. Used to validate the agency selection before saving.
  findGovernmentAgenciesByIds(ids: number[]) {
    return this.prisma.stakeholder.findMany({
      where: {
        id: { in: ids },
        ...this.buildGovernmentAgencyWhere(),
      },
      select: { id: true },
    });
  }

  // --- Private helpers ---------------------------------------------------

  // Translate the plain UpdateIssueData into Prisma's update input shape,
  // only including the fields the caller actually provided.
  private buildUpdateInput(data: UpdateIssueData): Prisma.IssuesUpdateInput {
    return {
      ...(data.title !== undefined && { title: data.title }),
      ...(data.description !== undefined && { description: data.description }),
      ...(data.recommendation !== undefined && {
        recommendation: data.recommendation,
      }),
      ...(data.attachment !== undefined && { attachment: data.attachment }),
      ...(data.issueStatusId !== undefined && {
        issueStatus: { connect: { id: data.issueStatusId } },
      }),
      ...(data.categoryId !== undefined && {
        category: { connect: { id: data.categoryId } },
      }),
      ...(data.meetingRequestId !== undefined && {
        meetingRequest: { connect: { id: data.meetingRequestId } },
      }),
    };
  }

  // Translate the plain filter shape into Prisma's where clause. Always
  // anchored on `deletedAt: null`; every other field is conditional so the
  // generated SQL stays minimal when no filters are supplied.
  private buildListWhere(filters: IssueListFilters): Prisma.IssuesWhereInput {
    const where: Prisma.IssuesWhereInput = { deletedAt: null };
    const andConditions: Prisma.IssuesWhereInput[] = [];

    if (
      filters.issueStatusIds !== undefined &&
      filters.issueStatusIds.length > 0
    ) {
      where.issueStatusId = { in: filters.issueStatusIds };
    } else if (filters.issueStatusId !== undefined) {
      where.issueStatusId = filters.issueStatusId;
    }

    if (filters.categoryIds !== undefined && filters.categoryIds.length > 0) {
      where.categoryId = { in: filters.categoryIds };
    } else if (filters.categoryId !== undefined) {
      where.categoryId = filters.categoryId;
    }
    if (filters.meetingRequestId !== undefined) {
      where.meetingRequestId = filters.meetingRequestId;
    }
    if (filters.userId !== undefined) {
      where.userId = filters.userId;
    }
    if (
      filters.ownerStakeholderIds !== undefined &&
      filters.ownerStakeholderIds.length > 0
    ) {
      where.stakeholderId = { in: filters.ownerStakeholderIds };
    } else if (filters.ownerStakeholderId !== undefined) {
      where.stakeholderId = filters.ownerStakeholderId;
    }
    if (filters.excludeOwnerStakeholderId !== undefined) {
      where.stakeholderId = {
        ...(typeof where.stakeholderId === 'object' &&
        where.stakeholderId !== null
          ? where.stakeholderId
          : {}),
        ...(typeof where.stakeholderId === 'number'
          ? { equals: where.stakeholderId }
          : {}),
        not: filters.excludeOwnerStakeholderId,
      };
    }
    if (filters.ownerStakeholderTypeName !== undefined) {
      where.stakeholder = {
        stakeholderType: {
          name: {
            equals: filters.ownerStakeholderTypeName,
            mode: 'insensitive',
          },
        },
      };
    }

    if (filters.hasAttachment === true) {
      where.attachment = { not: null };
    } else if (filters.hasAttachment === false) {
      where.attachment = null;
    }

    if (filters.years !== undefined && filters.years.length > 0) {
      andConditions.push({
        OR: filters.years.map((year) => ({
          createdAt: {
            gte: new Date(`${year}-01-01T00:00:00.000Z`),
            lte: new Date(`${year}-12-31T23:59:59.999Z`),
          },
        })),
      });
    } else if (
      filters.createdFrom !== undefined ||
      filters.createdTo !== undefined
    ) {
      where.createdAt = {
        ...(filters.createdFrom !== undefined && { gte: filters.createdFrom }),
        ...(filters.createdTo !== undefined && { lte: filters.createdTo }),
      };
    }

    // Case-insensitive contains-search across the three free-text columns.
    if (filters.search) {
      where.OR = [
        { title: { contains: filters.search, mode: 'insensitive' } },
        { description: { contains: filters.search, mode: 'insensitive' } },
        { recommendation: { contains: filters.search, mode: 'insensitive' } },
      ];
    }

    // Agency filters go through the pivot table. Using AND lets both
    // `stakeholderId` and `primaryAgencyId` be combined without one
    // overwriting the other on the relation field.
    if (filters.stakeholderId !== undefined) {
      andConditions.push({
        governmentAgencies: {
          some: { stakeholderId: filters.stakeholderId },
        },
      });
    }
    if (
      filters.primaryAgencyIds !== undefined &&
      filters.primaryAgencyIds.length > 0
    ) {
      andConditions.push({
        OR: filters.primaryAgencyIds.map((agencyId) => ({
          governmentAgencies: {
            some: {
              stakeholderId: agencyId,
              agencyOrder: PRIMARY_AGENCY_ORDER,
            },
          },
        })),
      });
    } else if (filters.primaryAgencyId !== undefined) {
      andConditions.push({
        governmentAgencies: {
          some: {
            stakeholderId: filters.primaryAgencyId,
            agencyOrder: PRIMARY_AGENCY_ORDER,
          },
        },
      });
    }

    if (andConditions.length > 0) {
      where.AND = andConditions;
    }

    return where;
  }

  // Shared where-clause that defines "is this stakeholder a government agency?".
  // Used by both the dropdown query and the validation query.
  private buildGovernmentAgencyWhere(): Prisma.StakeholderWhereInput {
    return {
      active: true,
      deletedAt: null,
      stakeholderType: {
        name: { equals: GOVERNMENT_AGENCY_TYPE_NAME, mode: 'insensitive' },
      },
    };
  }
}