import type {
  PortfolioStatus,
  PortfolioGoal,
} from "./../../db/schema/portfolio";
import { formatNumber } from "@/app/utils";
import { and, eq, inArray } from "drizzle-orm";
import { db } from "@/app/db/db";
import { portfolio } from "@/app/db/schema/portfolio";
import { property } from "@/app/db/schema/property";
import type { Portfolio } from "@/app/db/schema/portfolio";
import type { PropertyWithRelations } from "@/app/db/schema/property";
import {
  plan,
  planRecommendations,
  recommendation,
  UnnestedRecommendation,
  PortfolioPlanRecommendation,
  scenario,
  ScenarioSelect,
} from "@/app/db/schema/recommendations";
import { sql } from "drizzle-orm";
import {
  newApproachJoins,
  carbonSql,
  totalFloorAreaSql,
  lodgementDateSql,
  isExpiredSql,
  effectiveSapSql,
  effectiveEpcBandSql,
  lodgedEpcBandSql,
  lodgedSapSql,
  provenanceSignalSql,
  propertyTypeOverrideJoin,
  resolvedPropertyTypeSql,
  builtFormOverrideJoin,
  resolvedBuiltFormSql,
  constructionAgeOverrideJoin,
  resolvedConstructionAgeSql,
  mainFuelOverrideJoin,
  resolvedMainFuelSql,
  wallTypeOverrideJoin,
  resolvedWallTypeSql,
  roofTypeOverrideJoin,
  resolvedRoofTypeSql,
  mainHeatingOverrideJoin,
  resolvedHeatingSystemSql,
} from "@/lib/services/epcSources";
import {
  FilterGroups,
  FilterField,
  PropertyFilter,
  PROPERTY_TYPE_OPTIONS,
  BUILT_FORM_OPTIONS,
  YEAR_BUILT_OPTIONS,
  MAINFUEL_OPTIONS,
  PROVENANCE_OPTIONS,
  EnumOption,
  DESCRIPTOR_FILTER_CONFIG,
  DescriptorBucket,
  DescriptorMatch,
} from "@/app/utils/propertyFilters";
import { EPC_TO_SAP_MIN, EPC_TO_SAP_MAX } from "@/app/utils/epc";

const ENUM_FIELD_DB_OPTIONS: Record<string, EnumOption[]> = {
  propertyType: PROPERTY_TYPE_OPTIONS,
  builtForm:    BUILT_FORM_OPTIONS,
  yearBuilt:    YEAR_BUILT_OPTIONS,
  provenance:   PROVENANCE_OPTIONS,
  mainfuel:     MAINFUEL_OPTIONS,
};

export interface PortfolioSettingsType {
  name: string;
  budget: number | null;
  goal: (typeof PortfolioGoal)[number];
  status: (typeof PortfolioStatus)[number];
}

export async function getPortfolioSettings(
  portfolioId: string
): Promise<PortfolioSettingsType> {
  //name, budget, goal, status
  const data = await db
    .select({
      name: portfolio.name,
      budget: portfolio.budget,
      goal: portfolio.goal,
      status: portfolio.status,
    })
    .from(portfolio)
    .where(eq(portfolio.id, BigInt(portfolioId)));

  if (data.length === 0) {
    throw new Error("Portfolio not found");
  }

  if (data.length > 1) {
    throw new Error("More than one portfolio found");
  }

  return data[0];
}

export async function getPortfolio(portfolioId: string): Promise<Portfolio> {
  const data = await db
    .select()
    .from(portfolio)
    .where(eq(portfolio.id, BigInt(portfolioId)));

  if (data.length === 0) {
    throw new Error("Portfolio not found");
  }

  if (data.length > 1) {
    throw new Error("More than one portfolio found");
  }

  return data[0];
}

export async function getPortfolioScenarios(
  portfolioId: string
): Promise<ScenarioSelect[]> {
  // This function will grab all scenarios from the database for a given portfolio
  const scenarios = await db
    .select()
    .from(scenario)
    .where(eq(scenario.portfolioId, BigInt(portfolioId)));

  return scenarios;
}

export async function getPortfolioPerformance(
  portfolioId: string
): Promise<ScenarioSelect[]> {
  // This function will grab portfolio metrics from the database, but it will firstly check for the existence of scenarios and
  // use those figures. If they don't exist, we will use the default figures from the portfolio table
  const scenarios = await db
    .select()
    .from(scenario)
    .where(eq(scenario.portfolioId, BigInt(portfolioId)));

  // If we have scenarios, return them
  if (scenarios.length > 0) {
    return scenarios;
  }

  return [];
}

// Types needed for the overview page
export interface ChartData {
  name: string;
  [key: string]: number | string; // Allows for any number of properties with numeric values, plus the name
}

interface Scenario {
  scenarioName: string; // E.g., "today", "baseline", or dynamically named scenarios
  data: ChartData[] | string; // This could be an array of chart data or a simple string if applicable
}

export interface DataItem {
  title: string;
  scenarios: Scenario[];
}

export async function getOverviewPortfolioData(
  portfolioId: string
): Promise<DataItem[]> {
  // Keep just the columns we need for the overview

  // We firstly attempt to retrieve the data from the scenarios table, starting with the default
  // scenario. If the default scenario doesn't exist, we'll use the data from the
  // portfolio
  let data = await db
    .select({
      name: scenario.name,
      cost: scenario.cost,
      funding: scenario.funding,
      contingency: scenario.contingency,
      epcBreakdownPreRetrofit: scenario.epcBreakdownPreRetrofit,
      epcBreakdownPostRetrofit: scenario.epcBreakdownPostRetrofit,
      numberOfProperties: scenario.numberOfProperties,
      nUnitsToRetrofit: scenario.nUnitsToRetrofit,
      co2PerUnitPreRetrofit: scenario.co2PerUnitPreRetrofit,
      co2PerUnitPostRetrofit: scenario.co2PerUnitPostRetrofit,
      energyBillPerUnitPreRetrofit: scenario.energyBillPerUnitPreRetrofit,
      energyBillPerUnitPostRetrofit: scenario.energyBillPerUnitPostRetrofit,
      energyConsumptionPerUnitPreRetrofit:
        scenario.energyConsumptionPerUnitPreRetrofit,
      energyConsumptionPerUnitPostRetrofit:
        scenario.energyConsumptionPerUnitPostRetrofit,
      valuationImprovementPerUnit: scenario.valuationImprovementPerUnit,
      costPerUnit: scenario.costPerUnit,
      costPerCo2Saved: scenario.costPerCo2Saved,
      costPerSapPoint: scenario.costPerSapPoint,
      valuationReturnOnInvestment: scenario.valuationReturnOnInvestment,
    })
    .from(scenario)
    .where(
      and(
        eq(scenario.portfolioId, BigInt(portfolioId)),
        eq(scenario.isDefault, true)
      )
    );

  if (data.length === 0) {
    // If we have no data, we fetch it from the scenarios. This will facilitate this for older portfolios
    const fallback = await db
      .select({
        name: portfolio.name,
        cost: portfolio.cost,
        epcBreakdownPreRetrofit: portfolio.epcBreakdownPreRetrofit,
        epcBreakdownPostRetrofit: portfolio.epcBreakdownPostRetrofit,
        numberOfProperties: portfolio.numberOfProperties,
        nUnitsToRetrofit: portfolio.nUnitsToRetrofit,
        co2PerUnitPreRetrofit: portfolio.co2PerUnitPreRetrofit,
        co2PerUnitPostRetrofit: portfolio.co2PerUnitPostRetrofit,
        energyBillPerUnitPreRetrofit: portfolio.energyBillPerUnitPreRetrofit,
        energyBillPerUnitPostRetrofit: portfolio.energyBillPerUnitPostRetrofit,
        energyConsumptionPerUnitPreRetrofit:
          portfolio.energyConsumptionPerUnitPreRetrofit,
        energyConsumptionPerUnitPostRetrofit:
          portfolio.energyConsumptionPerUnitPostRetrofit,
        valuationImprovementPerUnit: portfolio.valuationImprovementPerUnit,
        costPerUnit: portfolio.costPerUnit,
        costPerCo2Saved: portfolio.costPerCo2Saved,
        costPerSapPoint: portfolio.costPerSapPoint,
        valuationReturnOnInvestment: portfolio.valuationReturnOnInvestment,
      })
      .from(portfolio)
      .where(eq(portfolio.id, BigInt(portfolioId)));

    // funding and contingency are not available so we insert them
    data = fallback.map((x) => ({
      ...x,
      funding: 0,
      contingency: 0,
    }));
    if (data.length === 0) {
      throw new Error("Portfolio not found");
    }
  }

  if (data.length > 1) {
    throw new Error("More than one portfolio found");
  }

  const portfolioName = data[0].name;

  // Format the data we need for the overview
  const output: DataItem[] = [
    {
      title: "EPCs",
      scenarios: [
        {
          scenarioName: "Today",
          data: JSON.parse(
            data[0].epcBreakdownPreRetrofit || "[]"
          ) as ChartData[],
        },
        {
          scenarioName: portfolioName || "Default",
          data: JSON.parse(
            data[0].epcBreakdownPostRetrofit || "[]"
          ) as ChartData[],
        },
      ],
    },
    {
      title: "# Units",
      scenarios: [
        {
          scenarioName: "Today",
          data: String(data[0].numberOfProperties) || "",
        },
        {
          scenarioName: portfolioName || "Default",
          data: String(data[0].numberOfProperties) || "",
        },
      ],
    },
    {
      title: "# Units to retrofit",
      scenarios: [
        { scenarioName: "Today", data: "-" },
        {
          scenarioName: portfolioName || "Default",
          data: String(data[0].nUnitsToRetrofit) || "",
        },
      ],
    },
    {
      title: "CO2/unit",
      scenarios: [
        { scenarioName: "Today", data: data[0].co2PerUnitPreRetrofit || "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].co2PerUnitPostRetrofit || "",
        },
      ],
    },
    {
      title: "Annual energy bill/unit",
      scenarios: [
        {
          scenarioName: "Today",
          data: data[0].energyBillPerUnitPreRetrofit || "",
        },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].energyBillPerUnitPostRetrofit || "",
        },
      ],
    },
    {
      title: "Annual energy demand (kWh)/unit",
      scenarios: [
        {
          scenarioName: "Today",
          data: data[0].energyConsumptionPerUnitPreRetrofit || "",
        },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].energyConsumptionPerUnitPostRetrofit || "",
        },
      ],
    },
    {
      title: "Cost of works (£)",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: "£" + formatNumber(data[0].cost || 0),
        },
      ],
    },
    {
      title: "Cost of works (£)/unit",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].costPerUnit || "",
        },
      ],
    },
    {
      title: "Funding (£)",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: "£" + formatNumber(data[0].funding || 0),
        },
      ],
    },
    {
      title: "Funding (£)/unit",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data:
            "£" +
            formatNumber(
              (data[0].funding || 0) / (data[0].nUnitsToRetrofit || 1)
            ),
        },
      ],
    },
    {
      title: "Contingency (£)",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: "£" + formatNumber(data[0].contingency || 0),
        },
      ],
    },
    {
      title: "Contingency (£)/unit",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data:
            "£" +
            formatNumber(
              (data[0].contingency || 0) / (data[0].nUnitsToRetrofit || 1)
            ),
        },
      ],
    },
    {
      title: "£ per CO2 reduction",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].costPerCo2Saved || "",
        },
      ],
    },
    {
      title: "£ per SAP point",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].costPerSapPoint || "",
        },
      ],
    },
    {
      title:
        "Potential valuation improvement/unit - highly indicative EPC effect*",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].valuationImprovementPerUnit || "",
        },
      ],
    },
    {
      title:
        "Potential valuation return on investment - highly indicative EPC effect*",
      scenarios: [
        { scenarioName: "Today", data: "" },
        {
          scenarioName: portfolioName || "Default",
          data: data[0].valuationReturnOnInvestment || "",
        },
      ],
    },
  ];

  return output;
}

export async function getNonDefaultPortfolioScenarios(
  portfolioId: string
): Promise<{ id: bigint; name: string | null }[]> {
  // For a given portfolio Id, we want to retrieve all of the scenarios that are attributed to this portfolio
  // We exclude the default scenario
  const scenarios = await db
    .select({
      id: scenario.id,
      name: scenario.name,
    })
    .from(scenario)
    .where(
      and(
        eq(scenario.portfolioId, BigInt(portfolioId)),
        eq(scenario.isDefault, false)
      )
    );

  return scenarios;
}

type EpcLetter = "A" | "B" | "C" | "D" | "E" | "F" | "G";
const EPC_ORDER: EpcLetter[] = ["A", "B", "C", "D", "E", "F", "G"];

function buildEpcSapCondition(
  col: ReturnType<typeof sql>,
  operator: PropertyFilter["operator"],
  value: string
): ReturnType<typeof sql> | null {
  const letter = value as EpcLetter;

  if (operator === "epc_at_most") {
    const maxSap = EPC_TO_SAP_MAX[letter as keyof typeof EPC_TO_SAP_MAX];
    if (maxSap === undefined) return null;
    return sql`${col} <= ${maxSap}`;
  }

  if (operator === "epc_at_least") {
    const minSap = EPC_TO_SAP_MIN[letter as keyof typeof EPC_TO_SAP_MIN];
    if (minSap === undefined) return null;
    return sql`${col} >= ${minSap}`;
  }

  if (operator === "equals") {
    const minSap = EPC_TO_SAP_MIN[letter as keyof typeof EPC_TO_SAP_MIN];
    const maxSap = EPC_TO_SAP_MAX[letter as keyof typeof EPC_TO_SAP_MAX];
    if (minSap === undefined || maxSap === undefined) return null;
    return sql`${col} BETWEEN ${minSap} AND ${maxSap}`;
  }

  if (operator === "epc_less_than") {
    // Worse than the given letter — SAP below the band's minimum
    const minSap = EPC_TO_SAP_MIN[letter as keyof typeof EPC_TO_SAP_MIN];
    if (minSap === undefined) return null;
    return sql`${col} < ${minSap}`;
  }

  if (operator === "epc_greater_than") {
    // Better than the given letter — SAP above the band's maximum
    const maxSap = EPC_TO_SAP_MAX[letter as keyof typeof EPC_TO_SAP_MAX];
    if (maxSap === undefined) return null;
    return sql`${col} > ${maxSap}`;
  }

  if (operator === "epc_one_of") {
    const letters = value.split(",").map((l) => l.trim()) as EpcLetter[];
    const ranges = letters
      .map((l) => {
        const minSap = EPC_TO_SAP_MIN[l as keyof typeof EPC_TO_SAP_MIN];
        const maxSap = EPC_TO_SAP_MAX[l as keyof typeof EPC_TO_SAP_MAX];
        if (minSap === undefined || maxSap === undefined) return null;
        return sql`(${col} BETWEEN ${minSap} AND ${maxSap})`;
      })
      .filter((x): x is ReturnType<typeof sql> => x !== null);
    if (ranges.length === 0) return null;
    return sql`(${sql.join(ranges, sql` OR `)})`;
  }

  return null;
}

/** An ILIKE pattern for a needle: prefix ("cavity%") or substring ("%mains gas%"). */
function descriptorLikePattern(needle: string, match: DescriptorMatch): string {
  return match === "prefix" ? `${needle}%` : `%${needle}%`;
}

/**
 * WHERE fragment matching the free-text wall/roof/heating column against the
 * selected coarse buckets (mirrors classifyDescriptor). A non-empty bucket
 * matches any of its needles; the "Unknown" bucket matches the complement —
 * rows matching NO known needle (which includes the resolved 'Unknown'
 * terminal). NULL is impossible here (resolvedXSql COALESCEs to 'Unknown').
 */
function buildDescriptorBucketCondition(
  col: ReturnType<typeof sql>,
  buckets: DescriptorBucket[],
  match: DescriptorMatch,
  selectedLabels: string[],
): ReturnType<typeof sql> | null {
  const allNeedles = buckets.flatMap((b) => b.needles);
  const parts: ReturnType<typeof sql>[] = [];

  for (const label of selectedLabels) {
    const bucket = buckets.find((b) => b.label === label);
    if (!bucket) continue;
    if (bucket.needles.length === 0) {
      // "Unknown" = the complement of every known needle.
      if (allNeedles.length === 0) return null;
      const nots = allNeedles.map(
        (n) => sql`${col} NOT ILIKE ${descriptorLikePattern(n, match)}`,
      );
      parts.push(sql`(${sql.join(nots, sql` AND `)})`);
    } else {
      const likes = bucket.needles.map(
        (n) => sql`${col} ILIKE ${descriptorLikePattern(n, match)}`,
      );
      parts.push(sql`(${sql.join(likes, sql` OR `)})`);
    }
  }

  if (parts.length === 0) return null;
  if (parts.length === 1) return parts[0];
  return sql`(${sql.join(parts, sql` OR `)})`;
}

function buildConditionSql(filter: PropertyFilter): ReturnType<typeof sql> | null {
  switch (filter.field) {
    case "address":
      if (filter.operator === "contains") {
        return sql`p.address ILIKE ${"%" + filter.value + "%"}`;
      }
      return null;

    case "postcode":
      if (filter.operator === "starts_with") {
        return sql`p.postcode ILIKE ${filter.value + "%"}`;
      }
      return null;

    case "propertyRef":
      if (filter.operator === "contains") {
        return sql`p.landlord_property_id ILIKE ${"%" + filter.value + "%"}`;
      }
      return null;

    case "currentEpc":
      // "Current" is the effective baseline (ADR-0002), not the null row column.
      return buildEpcSapCondition(effectiveSapSql, filter.operator, filter.value);

    case "lodgedEpc":
      return buildEpcSapCondition(lodgedSapSql, filter.operator, filter.value);

    case "expectedEpc": {
      if (filter.operator === "epc_at_least") {
        const minSap = EPC_TO_SAP_MIN[filter.value as keyof typeof EPC_TO_SAP_MIN];
        if (minSap === undefined) return null;
        return sql`pl.post_sap_points IS NOT NULL AND pl.post_sap_points >= ${minSap}`;
      }
      return null;
    }

    case "epcExpiryDate": {
      // Branches on the cutoff: new-approach properties derive the lodgement
      // date from the lodged epc_property row. See epcSources.ts.
      const lodgementExpr = lodgementDateSql(sql`epc`);
      const expiryExpr = sql`(${lodgementExpr} + INTERVAL '10 years')`;
      const guard = sql`${lodgementExpr} IS NOT NULL`;

      if (filter.operator === "date_before") {
        return sql`${guard} AND ${expiryExpr} < ${filter.value}::date`;
      }
      if (filter.operator === "date_after") {
        return sql`${guard} AND ${expiryExpr} > ${filter.value}::date`;
      }
      if (filter.operator === "date_equals") {
        return sql`${guard} AND DATE(${expiryExpr}) = ${filter.value}::date`;
      }
      if (filter.operator === "date_preset") {
        switch (filter.value) {
          case "expired":
            return sql`${isExpiredSql(sql`epc`)} = true`;
          case "expires_this_year":
            return sql`${guard} AND EXTRACT(YEAR FROM ${expiryExpr}) = EXTRACT(YEAR FROM CURRENT_DATE)`;
          case "expires_within_1_year":
            return sql`${guard} AND ${expiryExpr} BETWEEN CURRENT_DATE AND (CURRENT_DATE + INTERVAL '1 year')`;
          case "expires_within_2_years":
            return sql`${guard} AND ${expiryExpr} BETWEEN CURRENT_DATE AND (CURRENT_DATE + INTERVAL '2 years')`;
          default:
            return null;
        }
      }
      return null;
    }

    case "propertyType":
    case "builtForm":
    case "yearBuilt":
    case "provenance":
    case "mainfuel": {
      if (filter.operator !== "enum_one_of") return null;

      let selectedLabels: string[];
      try {
        selectedLabels = JSON.parse(filter.value);
      } catch {
        return null;
      }
      if (selectedLabels.length === 0) return null;

      const options = ENUM_FIELD_DB_OPTIONS[filter.field];
      const colMap: Record<string, ReturnType<typeof sql>> = {
        // Override-resolved to match the column + the Run filter (ADR-0012).
        propertyType: resolvedPropertyTypeSql,
        builtForm:    resolvedBuiltFormSql,
        yearBuilt:    resolvedConstructionAgeSql,
        provenance:   provenanceSignalSql,
        // Override-resolved (ADR-0012): the new EPC graph has no main fuel, so a
        // new-approach property matches a fuel filter only via a landlord override;
        // otherwise it resolves to 'Unknown'.
        mainfuel:     resolvedMainFuelSql(sql`epc`),
      };
      const col = colMap[filter.field];

      // Flatten all dbValues for selected labels
      const allDbValues: string[] = [];
      let includeNull = false;

      for (const label of selectedLabels) {
        const opt = options.find((o) => o.label === label);
        if (!opt) continue;
        for (const v of opt.dbValues) {
          if (v === "__null__") {
            includeNull = true;
          } else {
            allDbValues.push(v);
          }
        }
      }

      const parts: ReturnType<typeof sql>[] = [];
      if (includeNull) {
        parts.push(sql`${col} IS NULL`);
      }
      if (allDbValues.length > 0) {
        // Build IN clause with each value as a separate param
        const placeholders = allDbValues.map((v) => sql`${v}`);
        parts.push(sql`${col} IN (${sql.join(placeholders, sql`, `)})`);
      }

      if (parts.length === 0) return null;
      if (parts.length === 1) return parts[0];
      return sql`(${sql.join(parts, sql` OR `)})`;
    }

    case "floorArea": {
      const n = parseFloat(filter.value);
      if (isNaN(n)) return null;
      const area = totalFloorAreaSql(sql`epc`);
      if (filter.operator === "num_gte") return sql`${area} >= ${n}`;
      if (filter.operator === "num_lte") return sql`${area} <= ${n}`;
      if (filter.operator === "num_equals") return sql`${area} = ${n}`;
      return null;
    }

    case "co2Emissions": {
      const n = parseFloat(filter.value);
      if (isNaN(n)) return null;
      const carbon = carbonSql(sql`epc`);
      if (filter.operator === "num_gte") return sql`${carbon} >= ${n}`;
      if (filter.operator === "num_lte") return sql`${carbon} <= ${n}`;
      if (filter.operator === "num_equals") return sql`${carbon} = ${n}`;
      return null;
    }

    case "tags": {
      // Value is a JSON array of selected tag ids, optionally with the synthetic
      // "__untagged__" bucket. Any-of within tags (ADR-0013). EXISTS on
      // property_tag is PK-indexed by property_id — no join to add to the count.
      if (filter.operator !== "enum_one_of") return null;
      let selected: string[];
      try {
        selected = JSON.parse(filter.value);
      } catch {
        return null;
      }
      if (selected.length === 0) return null;

      const untagged = selected.includes("__untagged__");
      const ids = selected.filter((s) => /^\d+$/.test(s));
      const parts: ReturnType<typeof sql>[] = [];
      if (ids.length > 0) {
        const placeholders = ids.map((id) => sql`${id}::bigint`);
        parts.push(
          sql`EXISTS (SELECT 1 FROM property_tag pt WHERE pt.property_id = p.id AND pt.tag_id IN (${sql.join(placeholders, sql`, `)}))`,
        );
      }
      if (untagged) {
        parts.push(
          sql`NOT EXISTS (SELECT 1 FROM property_tag pt WHERE pt.property_id = p.id)`,
        );
      }
      if (parts.length === 0) return null;
      if (parts.length === 1) return parts[0];
      return sql`(${sql.join(parts, sql` OR `)})`;
    }

    case "wallType":
    case "roofType":
    case "heatingSystem": {
      // Free-text descriptors: bucket the resolved value by prefix/substring
      // needles (ADR-0012), mirroring classifyDescriptor. The resolved SQL folds
      // override → EPC element description → legacy → 'Unknown'.
      if (filter.operator !== "enum_one_of") return null;

      let selectedLabels: string[];
      try {
        selectedLabels = JSON.parse(filter.value);
      } catch {
        return null;
      }
      if (selectedLabels.length === 0) return null;

      const { buckets, match } = DESCRIPTOR_FILTER_CONFIG[filter.field];
      const colMap: Record<string, ReturnType<typeof sql>> = {
        wallType:      resolvedWallTypeSql(sql`epc`),
        roofType:      resolvedRoofTypeSql(sql`epc`),
        heatingSystem: resolvedHeatingSystemSql(sql`epc`),
      };
      return buildDescriptorBucketCondition(
        colMap[filter.field],
        buckets,
        match,
        selectedLabels,
      );
    }
  }
  return null;
}

function buildWhereClause(filterGroups: FilterGroups): ReturnType<typeof sql> {
  const groupFragments: ReturnType<typeof sql>[] = [];

  for (const group of filterGroups) {
    const condFragments = group.conditions
      .map(buildConditionSql)
      .filter((x): x is ReturnType<typeof sql> => x !== null);

    if (condFragments.length === 0) continue;

    if (condFragments.length === 1) {
      groupFragments.push(condFragments[0]);
    } else {
      groupFragments.push(sql`(${sql.join(condFragments, sql` AND `)})`);
    }
  }

  return groupFragments.length > 0
    ? sql`AND (${sql.join(groupFragments, sql` OR `)})`
    : sql``;
}

// Filter fields whose SQL references the EPC graph (epc/bp/epl/epp) vs the
// default-plan LATERAL. The count query only needs a join when an active filter
// references it — otherwise joining is pure cost. The `pl` LATERAL in particular
// runs once per property (31k+ correlated plan lookups for a large portfolio),
// which alone pushed the unfiltered count past Vercel's 15s limit.
const EPC_JOIN_FILTER_FIELDS = new Set<FilterField>([
  "currentEpc",
  "lodgedEpc",
  "provenance",
  "co2Emissions",
  "floorArea",
  "epcExpiryDate",
  "mainfuel",
  // Override-resolved (ADR-0012): needs the EPC graph joins + the override join.
  "propertyType",
  "builtForm",
  "yearBuilt",
  // Free-text descriptors (ADR-0012): resolved SQL reads the EPC element
  // description subquery + the override join.
  "wallType",
  "roofType",
  "heatingSystem",
]);
const PLAN_JOIN_FILTER_FIELDS = new Set<FilterField>(["expectedEpc"]);

function filterFieldsInUse(filterGroups: FilterGroups): Set<FilterField> {
  const fields = new Set<FilterField>();
  for (const group of filterGroups) {
    for (const cond of group.conditions) fields.add(cond.field);
  }
  return fields;
}

export async function getPropertiesCount(
  portfolioId: string,
  filterGroups: FilterGroups = []
): Promise<number> {
  const combinedWhere = buildWhereClause(filterGroups);
  const fields = filterFieldsInUse(filterGroups);

  // Only join what an active filter needs. COUNT is unaffected by the LEFT JOINs
  // otherwise (none multiply rows), so omitting them is purely a speed-up.
  const needsEpcJoins = [...fields].some((f) => EPC_JOIN_FILTER_FIELDS.has(f));
  const needsPlanJoin = [...fields].some((f) => PLAN_JOIN_FILTER_FIELDS.has(f));

  const epcJoins = needsEpcJoins
    ? sql`LEFT JOIN property_details_epc epc ON epc.property_id = p.id
    ${newApproachJoins}
    ${propertyTypeOverrideJoin}
    ${builtFormOverrideJoin}
    ${constructionAgeOverrideJoin}
    ${mainFuelOverrideJoin}
    ${wallTypeOverrideJoin}
    ${roofTypeOverrideJoin}
    ${mainHeatingOverrideJoin}`
    : sql``;
  const planJoin = needsPlanJoin
    ? sql`LEFT JOIN LATERAL (
      SELECT id, post_sap_points FROM plan
      WHERE property_id = p.id
        AND portfolio_id = p.portfolio_id
        AND is_default = true
      ORDER BY created_at DESC
      LIMIT 1
    ) pl ON true`
    : sql``;

  const result = await db.execute<{ count: string }>(sql`
    SELECT COUNT(DISTINCT p.id)::int AS count
    FROM property p
    ${epcJoins}
    ${planJoin}
    WHERE p.portfolio_id = ${portfolioId}
    -- Unmatched (no-UPRN) properties live in the "Needs attention" tab until
    -- resolved, so they're excluded from the main table + its count.
    AND p.uprn IS NOT NULL
    ${combinedWhere}
  `);

  return parseInt(result.rows[0]?.count ?? "0", 10);
}

/**
 * The ids of every Property matching a filter selection — used by filter-driven
 * Bulk tag assignment ("tag all N matching the current filter", ADR-0013). Same
 * resolution + conditional joins as getPropertiesCount, but returns ids across
 * the whole matching set, not a display page. Mirrors the table's `uprn IS NOT
 * NULL` exclusion so the count the user saw and the set that gets tagged agree.
 */
export async function getFilteredPropertyIds(
  portfolioId: string,
  filterGroups: FilterGroups = [],
): Promise<bigint[]> {
  const combinedWhere = buildWhereClause(filterGroups);
  const fields = filterFieldsInUse(filterGroups);
  const needsEpcJoins = [...fields].some((f) => EPC_JOIN_FILTER_FIELDS.has(f));
  const needsPlanJoin = [...fields].some((f) => PLAN_JOIN_FILTER_FIELDS.has(f));

  const epcJoins = needsEpcJoins
    ? sql`LEFT JOIN property_details_epc epc ON epc.property_id = p.id
    ${newApproachJoins}`
    : sql``;
  const planJoin = needsPlanJoin
    ? sql`LEFT JOIN LATERAL (
      SELECT id, post_sap_points FROM plan
      WHERE property_id = p.id
        AND portfolio_id = p.portfolio_id
        AND is_default = true
      ORDER BY created_at DESC
      LIMIT 1
    ) pl ON true`
    : sql``;

  const result = await db.execute<{ id: string }>(sql`
    SELECT DISTINCT p.id AS id
    FROM property p
    ${epcJoins}
    ${planJoin}
    WHERE p.portfolio_id = ${portfolioId}
    AND p.uprn IS NOT NULL
    ${combinedWhere}
  `);
  return result.rows.map((r) => BigInt(r.id));
}

export async function getProperties(
  portfolioId: string,
  limit: number = 1000,
  offset: number = 0,
  filterGroups: FilterGroups = []
): Promise<PropertyWithRelations[]> {
  // We need to perform the query like this because the nested query is not supported in the ORM right now

  const combinedWhere = buildWhereClause(filterGroups);

  const result =
    await db.execute<PropertyWithRelations>(sql<PropertyWithRelations>`
    SELECT
      p.id AS id,
      p.portfolio_id AS "portfolioId",
      p.address AS address,
      p.postcode AS postcode,
      p.status AS status,
      p.creation_status AS "creationStatus",
      -- "Current" is the effective (re-baselined) figure — the canonical current
      -- per ADR-0002. The lodged certificate is a separate column. New-approach
      -- properties have null headline columns on the property row, so both source
      -- from property_baseline_performance.
      ${effectiveEpcBandSql} AS "currentEpcRating",
      ${effectiveSapSql} AS "currentSapPoints",
      ${lodgedEpcBandSql} AS "lodgedEpcRating",
      ${provenanceSignalSql} AS "provenanceSignal",
      -- "Expected EPC" is the default plan's modelled post-retrofit rating, not
      -- current + Σ recommendation SAP (which double-counts across plans and
      -- overshoots). See expectedEpc.ts / ADR-0002.
      NULL AS "targetEpc",
      pl.post_epc_rating AS "postEpcRating",
      pl.post_sap_points AS "postSapPoints",
      pl.id AS "planId",
      -- funding_package is no longer read here (the proposals table fetches it
      -- separately). Kept as NULL to preserve the PropertyWithRelations shape.
      NULL AS "fundingScheme",
      COALESCE(rec.sap_points, 0) AS "totalRecommendationSapPoints",
      COALESCE(rec.cost, 0) AS "totalRecommendationCost",
      p.landlord_property_id AS "landlordPropertyId",
      p.original_sap_points AS "originalSapPoints",
      -- Property Type is resolved through the landlord-override precedence
      -- (override → EPC-derived → 'Unknown'), matching the modelling Run filter.
      -- See resolvePropertyType / ADR-0012.
      ${resolvedPropertyTypeSql} AS "propertyType",
      ${resolvedBuiltFormSql} AS "builtForm",
      -- Construction Age is band-native and override-resolved (ADR-0012); the
      -- "yearBuilt" key is retained to avoid a wide rename. See resolveConstructionAge.
      ${resolvedConstructionAgeSql} AS "yearBuilt",
      ${lodgementDateSql(sql`epc`)}::text AS "epcLodgementDate",
      ${isExpiredSql(sql`epc`)} AS "epcIsExpired",
      ${totalFloorAreaSql(sql`epc`)} AS "totalFloorArea",
      ${carbonSql(sql`epc`)} AS "co2Emissions",
      -- Main Fuel: override → EPC-derived → 'Unknown'. The new EPC graph has no
      -- main fuel, so overrides are the only new-approach source (ADR-0012).
      ${resolvedMainFuelSql(sql`epc`)} AS mainfuel,
      -- Wall / roof / heating: override → EPC element description → legacy → 'Unknown'
      -- (ADR-0012). Per-part components (wall/roof) read the main building part.
      ${resolvedWallTypeSql(sql`epc`)} AS "wallType",
      ${resolvedRoofTypeSql(sql`epc`)} AS "roofType",
      ${resolvedHeatingSystemSql(sql`epc`)} AS "heatingSystem",
      p.lexiscore AS lexiscore,
      -- Portfolio Tags on this property, name-ordered, as JSON (ADR-0013). A
      -- correlated subquery keeps the main query at one row per property (a JOIN
      -- would multiply rows and break the LATERAL aggregates above).
      COALESCE((
        SELECT json_agg(
                 json_build_object('id', t.id::text, 'name', t.name, 'colour', t.colour)
                 ORDER BY t.name
               )
        FROM property_tag pt
        JOIN portfolio_tag t ON t.id = pt.tag_id
        WHERE pt.property_id = p.id
      ), '[]'::json) AS tags
    FROM property p
    -- LATERAL one-row lookups keep this query at "N properties × index probe"
    -- instead of hash-joining the multi-million-row plan/recommendation tables.
    LEFT JOIN LATERAL (
      SELECT id, post_sap_points, post_epc_rating FROM plan
      WHERE property_id = p.id
        AND portfolio_id = p.portfolio_id
        AND is_default = true
      ORDER BY created_at DESC
      LIMIT 1
    ) pl ON true
    -- Active (default, not-yet-installed) recommendations for the property,
    -- read denormalised. We aggregate by recommendation.property_id — the only
    -- indexed access path (there is NO index on recommendation.plan_id, and the
    -- table has tens of millions of rows) — and rely on the partial index on
    -- (default, already_installed). The retired plan_recommendations join table
    -- is intentionally not used.
    LEFT JOIN LATERAL (
      SELECT
        COALESCE(SUM(sap_points), 0)     AS sap_points,
        COALESCE(SUM(estimated_cost), 0) AS cost
      FROM recommendation
      WHERE property_id = p.id
        -- Scope to the default plan. property_id is the indexed access path, so
        -- this only filters the property's own (tiny) rec set in memory — no
        -- unindexed plan_id scan — while stopping the SUM double-counting a
        -- measure once per plan when a property has several plans.
        AND plan_id = pl.id
        AND "default" = true
        AND already_installed = false
    ) rec ON true
    LEFT JOIN property_details_epc epc
      ON epc.property_id = p.id
    ${newApproachJoins}
    ${propertyTypeOverrideJoin}
    ${builtFormOverrideJoin}
    ${constructionAgeOverrideJoin}
    ${mainFuelOverrideJoin}
    ${wallTypeOverrideJoin}
    ${roofTypeOverrideJoin}
    ${mainHeatingOverrideJoin}
    WHERE p.portfolio_id = ${portfolioId}
    -- Unmatched (no-UPRN) properties live in the "Needs attention" tab until
    -- resolved, so they're excluded from the main table.
    AND p.uprn IS NOT NULL
    ${combinedWhere}
    LIMIT ${limit} OFFSET ${offset};
  `);

  const data: PropertyWithRelations[] = result.rows;

  return data;
}

interface UnaggregatedPortfolioPlanRecommendation {
  quantity: number | null; // Allow null for cases where no materials are associated
  quantityUnit: string | null;
  estimatedCost: number;
  materialType: string | null;
  propertyId?: string;
  numberOfProperties?: number; // Optional field to hold the count of unique propertyIds
  measureType: string | null;
  measureTypeCount: number | undefined; // Optional field to hold the count of unique measureTypes
}

function aggregateRecommendations(
  data: UnaggregatedPortfolioPlanRecommendation[]
): PortfolioPlanRecommendation[] {
  const grouped: {
    [key: string]: PortfolioPlanRecommendation & {
      propertyIds: Set<string>;
      measureTypes: Set<string>;
    };
  } = {};

  data.forEach((item) => {
    // Use the combination of quantityUnit and materialType (or measureType if materialType is missing) as a unique key
    const key = item.materialType
      ? `${item.quantityUnit}_${item.materialType}`
      : `${item.quantityUnit}_${item.measureType}`;

    if (!grouped[key]) {
      grouped[key] = {
        ...item,
        numberOfProperties: 0, // Initialize to 0
        propertyIds: item.propertyId ? new Set([item.propertyId]) : new Set(),
        measureTypes: item.measureType
          ? new Set([item.measureType])
          : new Set(),
      };
    } else {
      // if quantity is null previously, set to 0
      grouped[key].quantity =
        (grouped[key].quantity ?? 0) + (item.quantity ?? 0);
      grouped[key].estimatedCost += item.estimatedCost;

      if (item.propertyId) {
        grouped[key].propertyIds.add(item.propertyId);
      }

      if (item.measureType) {
        grouped[key].measureTypes.add(item.measureType);
      }
    }
  });

  // Round the results to 2 decimal places, compute uniquePropertyCount, and count unique measureTypes
  for (const key in grouped) {
    grouped[key].quantity = parseFloat(
      grouped[key].quantity?.toFixed(2) || "0.00"
    );
    grouped[key].estimatedCost = parseFloat(
      grouped[key].estimatedCost.toFixed(2)
    );
    grouped[key].numberOfProperties = grouped[key].propertyIds.size;
    grouped[key].measureTypeCount = grouped[key].measureTypes.size;

    // Cleanup temporary properties
    delete (grouped[key] as any).propertyIds; // using type assertion to bypass the TS error
    delete (grouped[key] as any).measureTypes; // using type assertion to bypass the TS error
  }

  return Object.values(grouped);
}

export async function getPortfolioMeasures(portfolioId: string) {
  // Step 1: Get all default plans for the portfolioId
  const plans = await db.query.plan.findMany({
    where: and(
      eq(property.portfolioId, BigInt(portfolioId)),
      eq(plan.isDefault, true)
    ),
  });

  const planIds = plans.map((plan) => plan.id);

  // Step 2: Get recommendations for the plans
  const recommendations = await db.query.planRecommendations.findMany({
    where: inArray(planRecommendations.planId, planIds),
  });

  const recommendationIds = recommendations.map(
    (recommendation) => recommendation.id
  );

  // Step 3: Get data for recommendations, including materials and measureTypes
  const data = await db.query.recommendation.findMany({
    where: and(
      inArray(recommendation.id, recommendationIds),
      eq(recommendation.default, true)
    ),
    columns: { propertyId: true, measureType: true },
    with: {
      recommendationMaterials: {
        with: {
          material: {
            columns: {
              type: true,
            },
          },
        },
        columns: {
          quantity: true,
          quantityUnit: true,
          estimatedCost: true,
        },
      },
    },
  });

  // Step 4: Handle recommendations with or without materials
  const unnestedRecommendations: UnnestedRecommendation[] = data.reduce(
    (acc: UnnestedRecommendation[], recommendation) => {
      if (recommendation.recommendationMaterials.length === 0) {
        // Handle case where no materials are associated
        acc.push({
          quantity: 0,
          quantityUnit: null,
          estimatedCost: 0,
          materialType: null,
          propertyId: String(recommendation.propertyId),
          measureType: recommendation.measureType, // Ensure measureType is included
          measureTypeCount: undefined, // Initialize to undefined
        });
      } else {
        // Process recommendations with materials
        const materials = recommendation.recommendationMaterials.map(
          (material) => ({
            quantity: material.quantity,
            quantityUnit: material.quantityUnit,
            estimatedCost: material.estimatedCost,
            materialType: material.material.type,
            propertyId: String(recommendation.propertyId),
            measureType: recommendation.measureType, // Include measureType
            measureTypeCount: undefined, // Initialize to undefined
          })
        );
        acc.push(...materials);
      }
      return acc;
    },
    []
  );

  // Step 5: Aggregate the recommendations
  const aggregated = aggregateRecommendations(unnestedRecommendations);

  return aggregated;
}
