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

// A "primary agency" is the responsible agency stored with agencyOrder = 1
// in the government_agency_issue pivot table. This matches the rule used by
// the working-group-issues module, so both dashboards agree.
const PRIMARY_AGENCY_ORDER = 1;

// Stakeholder type of government agencies. The "Total Primary Agencies"
// card counts every active stakeholder of this type.
const GOVERNMENT_AGENCY_TYPE_NAME = 'Ministry';

// Stakeholder type of working groups (the issue owners).
const PRIVATE_SECTOR_TYPE_NAME = 'Private Sector';

// Plain filter shape the service passes in. The repository is the only layer
// that knows about Prisma types, so it translates this into a Prisma "where"
// clause. Every field is optional.
export interface DashboardSummaryFilters {
  year?: number;
  issueStatusId?: number;
  statusCode?: string;
  primaryAgencyId?: number;
  categoryId?: number;
  workingGroupId?: number;
}

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

  // Runs every dashboard query inside one transaction so all the numbers come
  // from the same snapshot of the database. It returns raw counts and rows;
  // the service is responsible for reshaping them into the API response.
  getSummaryData(filters: DashboardSummaryFilters) {
    // The same "which issues count" rule is shared by every query below.
    const issueWhere = this.buildIssueWhere(filters);

    return this.prisma.$transaction(async (tx) => {
      const [
        totalIssues,
        statusCounts,
        workingGroupStatusCounts,
        categoryCounts,
        primaryAgencyRows,
        statuses,
        ministries,
        workingGroups,
        categories,
      ] = await Promise.all([
        // Total number of issues (any status) that match the filters.
        tx.issues.count({ where: issueWhere }),

        // Issues grouped by status → feeds the donut chart and the cards.
        tx.issues.groupBy({
          by: ['issueStatusId'],
          where: issueWhere,
          _count: { _all: true },
        }),

        // Issues grouped by owning working group + status → the working
        // group bar chart. On the Issues table, `stakeholderId` is the
        // working group that owns the issue.
        tx.issues.groupBy({
          by: ['stakeholderId', 'issueStatusId'],
          where: issueWhere,
          _count: { _all: true },
        }),

        // Issues grouped by category → the category bar chart.
        tx.issues.groupBy({
          by: ['categoryId'],
          where: issueWhere,
          _count: { _all: true },
        }),

        // Primary-agency chart. Prisma's groupBy cannot join to another
        // table, so we read the pivot rows directly and let the service
        // tally them. The @@unique([issueId, agencyOrder]) constraint means
        // each issue has at most one primary-agency row, so this returns at
        // most one row per counted issue. We pull the agency name here to
        // avoid a second lookup, and the issue's status for the split bars.
        tx.governmentAgencyIssue.findMany({
          where: {
            agencyOrder: PRIMARY_AGENCY_ORDER,
            issue: issueWhere,
            stakeholder: { active: true, deletedAt: null },
          },
          select: {
            stakeholderId: true,
            stakeholder: { select: { name: true } },
            issue: { select: { issueStatusId: true } },
          },
        }),

        // Lookup table: status id → code + name, used to label every chart.
        tx.issueStatuses.findMany({
          where: { deletedAt: null },
          orderBy: { id: 'asc' },
          select: { id: true, code: true, name: true },
        }),

        // ALL active ministries in the system, whether or not they have
        // issues assigned. Drives both the "Total Primary Agencies" card
        // and the full agency list on the bar chart (agencies without
        // issues show as empty bars). Deliberately ignores the filters.
        tx.stakeholder.findMany({
          where: {
            active: true,
            deletedAt: null,
            stakeholderType: {
              name: {
                equals: GOVERNMENT_AGENCY_TYPE_NAME,
                mode: 'insensitive',
              },
            },
          },
          orderBy: { name: 'asc' },
          select: { id: true, name: true },
        }),

        // ALL active working groups, whether or not they have issues.
        // Groups without issues show as empty bars on the chart.
        tx.stakeholder.findMany({
          where: {
            active: true,
            deletedAt: null,
            stakeholderType: {
              name: {
                equals: PRIVATE_SECTOR_TYPE_NAME,
                mode: 'insensitive',
              },
            },
          },
          orderBy: { name: 'asc' },
          select: { id: true, name: true },
        }),

        // ALL categories, whether or not they have issues. Categories
        // without issues show as empty bars on the chart.
        tx.categories.findMany({
          where: { deletedAt: null },
          orderBy: { name: 'asc' },
          select: { id: true, name: true },
        }),
      ]);

      return {
        totalIssues,
        statusCounts,
        workingGroupStatusCounts,
        categoryCounts,
        primaryAgencyRows,
        statuses,
        ministries,
        workingGroups,
        categories,
      };
    });
  }

  // Builds the shared "which issues count" filter. Copies the patterns used by
  // working-group-issues.repository.ts so the numbers stay consistent.
  private buildIssueWhere(
    filters: DashboardSummaryFilters,
  ): Prisma.IssuesWhereInput {
    // Soft-deleted issues never count.
    const where: Prisma.IssuesWhereInput = { deletedAt: null };

    // Year filter: match issues created between Jan 1 and Dec 31 of that year.
    if (filters.year !== undefined) {
      where.createdAt = {
        gte: new Date(`${filters.year}-01-01T00:00:00.000Z`),
        lte: new Date(`${filters.year}-12-31T23:59:59.999Z`),
      };
    }

    // Status filter: an explicit id wins; otherwise match the status code
    // case-insensitively (codes are stored in UPPER_SNAKE form).
    if (filters.issueStatusId !== undefined) {
      where.issueStatusId = filters.issueStatusId;
    } else if (filters.statusCode !== undefined) {
      where.issueStatus = {
        deletedAt: null,
        code: { equals: filters.statusCode, mode: 'insensitive' },
      };
    }

    if (filters.categoryId !== undefined) {
      where.categoryId = filters.categoryId;
    }

    // Working group filter: on the Issues table, `stakeholderId` is the
    // working group that owns the issue.
    if (filters.workingGroupId !== undefined) {
      where.stakeholderId = filters.workingGroupId;
    }

    // Primary-agency filter goes through the pivot table: keep only issues
    // whose agencyOrder = 1 row points at this stakeholder.
    if (filters.primaryAgencyId !== undefined) {
      where.AND = [
        {
          governmentAgencies: {
            some: {
              stakeholderId: filters.primaryAgencyId,
              agencyOrder: PRIMARY_AGENCY_ORDER,
            },
          },
        },
      ];
    }

    return where;
  }
}