import S3 from "aws-sdk/clients/s3";
import {
  Recommendation,
  recommendation,
  planRecommendations,
  plan,
  Plan,
  installedMeasure,
  scenario,
  ScenarioSelect,
} from "@/app/db/schema/recommendations";
import { db } from "@/app/db/db";
import { surveyDB } from "@/app/db/surveyDB/connection";
import {
  Feature,
  GeneralFeature,
  PropertyDetailsSpatial,
  PropertyMeta,
  propertyDetailsSpatial,
  NonIntrusiveSurveyData,
  nonInstrusiveSurvey,
} from "@/app/db/schema/property";
import {
  ConditionReport,
  resolveConditionReport,
  resolvePropertyMeta,
} from "@/lib/services/epcSources";
import { getRating, serializeBigInt } from "@/app/utils";
import { eq, desc, and } from "drizzle-orm";
import {
  energyAssessment,
  EnergyAssessment,
  energyAssessmentDocuments,
  EnergyAssessmentDocumentWithScenario,
} from "@/app/db/schema/energy_assessments";
import {
  fundingPackage,
  FundingPackageWithMeasures,
} from "@/app/db/schema/funding";
import {
  getUploadedFile,
  uploadedFiles,
  DB_REPORT_TYPES,
} from "@/app/db/surveyDB/schema/surveyDB";

export type InstalledMeasureSummary = {
  measureType: string;
  installedAt: Date | null;
  sapPoints: number | null;
  carbonSavings: number | null;
  kwhSavings: number | null;
  billSavings: number | null;
};

export async function getEnergyAssessmentFromS3(s3Uri: string): Promise<any> {
  const url = new URL(s3Uri);

  const bucketMatch = url.hostname.match(/^(.+)\.s3/);
  const bucket = bucketMatch?.[1];
  const key = url.pathname.startsWith("/")
    ? url.pathname.slice(1)
    : url.pathname;

  if (!bucket || !key) {
    throw new Error("Could not extract bucket or key from URI");
  }

  const s3 = new S3({
    region: "eu-west-2",
    accessKeyId: process.env.RETROFIT_ENERGY_ASSESSMENTS_AWS_ACCESS_KEY,
    secretAccessKey: process.env.ENERGY_ASSESSMENTS_AWS_SECRET,
  });

  const result = await s3
    .getObject({
      Bucket: bucket,
      Key: key,
    })
    .promise();

  const body = result.Body?.toString("utf-8");
  return body ? JSON.parse(body) : null;
}

type RecommendationList = {
  recommendation: Recommendation;
}[];

export async function getPlanFunding(
  planId: string
): Promise<FundingPackageWithMeasures[]> {
  const data = await db.query.fundingPackage.findMany({
    where: eq(fundingPackage.planId, BigInt(planId)),
    with: {
      fundingPackageMeasures: true,
    },
  });

  if (!data) {
    throw new Error("Network response was not ok");
  }

  return data;
}

export async function getDocument({
  uprn,
  documentType,
}: {
  uprn: string;
  documentType: (typeof DB_REPORT_TYPES)[number];
}): Promise<getUploadedFile> {
  // We get the latest entry for the given UPRN and document type, by s3JsonUploadTimestamp
  const data = await surveyDB.query.uploadedFiles.findFirst({
    where: and(
      eq(uploadedFiles.uprn, String(uprn)),
      eq(uploadedFiles.docType, documentType)
    ),
    orderBy: (uploadedFiles, { desc }) => [
      desc(uploadedFiles.s3JsonUploadTimestamp),
    ],
  });
  // We may not have an uploaded document so we return an empty array
  if (!data) {
    return {} as getUploadedFile;
  }
  return data;
}

export async function getEnergyAssessment(
  uprn: number
): Promise<EnergyAssessment> {
  const data = await db.query.energyAssessment.findFirst({
    where: eq(energyAssessment.uprn, BigInt(uprn)),
  });

  if (!data) {
    // If there's no data, we return an empty array. This signififies that no energy assessment has been conducted
    return {} as EnergyAssessment;
  }

  return data;
}

export async function getEnergyAssessmentDocuments(
  energyAssessmentId: bigint
): Promise<EnergyAssessmentDocumentWithScenario[]> {
  const data = await db.query.energyAssessmentDocuments.findMany({
    where: eq(
      energyAssessmentDocuments.energyAssessmentId,
      BigInt(energyAssessmentId)
    ),
    with: {
      scenario: true,
    },
  });

  if (!data) {
    throw new Error("Network response was not ok");
  }

  return data;
}

export async function getRecommendations(
  planId: string
): Promise<Recommendation[]> {
  // Recommendations link to their plan via recommendation.plan_id (the
  // plan_recommendations join table is retired). recommendation.plan_id is NOT
  // indexed and the table has tens of millions of rows, so we key off the plan's
  // property_id (indexed) and filter plan_id in memory. We drop already-installed
  // measures, matching the previous behaviour.
  const planRow = await db.query.plan.findFirst({
    columns: { propertyId: true },
    where: eq(plan.id, BigInt(planId)),
  });
  if (!planRow?.propertyId) {
    return [];
  }

  const recommendations = await db.query.recommendation.findMany({
    where: and(
      eq(recommendation.propertyId, planRow.propertyId),
      eq(recommendation.planId, BigInt(planId)),
      eq(recommendation.alreadyInstalled, false)
    ),
  });

  return recommendations;
}

export async function getPlanMeta(planId: string): Promise<Plan> {
  const data = await db.query.plan.findFirst({
    where: eq(plan.id, BigInt(planId)),
  });

  if (!data) {
    throw new Error("Network response was not ok");
  }

  return data;
}

type PlanRelation = Plan & {
  planRecommendations: {
    recommendation: {
      estimatedCost: number;
      sapPoints: number;
      default: boolean;
    };
  }[];
};

export async function getPlans(propertyId: string): Promise<PlanRelation[]> {
  const data = await db.query.plan.findMany({
    where: eq(plan.propertyId, BigInt(propertyId)),
    orderBy: [desc(plan.createdAt)],
    // with: {
    //   planRecommendations: {
    //     columns: {},
    //     with: {
    //       recommendation: {
    //         columns: {
    //           estimatedCost: true,
    //           sapPoints: true,
    //           default: true,
    //         },
    //       },
    //     },
    //   },
    // },
  });

  if (!data) {
    throw new Error("Network response was not ok");
  }

  return data as PlanRelation[];
}

export async function getPropertyMeta(
  propertyId: string
): Promise<PropertyMeta> {
  // Resolve in-process instead of self-fetching /api/property-meta. The HTTP
  // round-trip went to process.env.URL, which can point at a different
  // deployment (e.g. production) and was serving stale/old-code responses — so
  // the backfilled headline SAP/EPC never reached the page. We JSON round-trip
  // through serializeBigInt to keep the exact shape the fetch used to return
  // (bigints as strings, dates as ISO strings).
  const meta = await resolvePropertyMeta(propertyId);
  if (!meta) {
    throw new Error("Network response was not ok");
  }
  return JSON.parse(JSON.stringify(meta, serializeBigInt)) as PropertyMeta;
}

export async function getConditionReport(
  propertyId: string
): Promise<ConditionReport> {
  // Branches on the new-approach cutoff: legacy properties read
  // property_details_epc; new-approach ones read the epc_property graph +
  // property_baseline_performance. See src/lib/services/epcSources.ts.
  return resolveConditionReport(propertyId);
}

export async function getSpatialData(
  uprn: number
): Promise<PropertyDetailsSpatial> {
  const data = await db.query.propertyDetailsSpatial.findFirst({
    where: eq(propertyDetailsSpatial.uprn, BigInt(uprn)),
  });

  if (!data) {
    throw new Error("Network response was not ok");
  }

  return data;
}

export async function getNonIntrusiveSurvey(
  uprn: number
): Promise<NonIntrusiveSurveyData> {
  // Query to find the newest survey for a given UPRN
  const data = await db.query.nonInstrusiveSurvey.findMany({
    where: eq(nonInstrusiveSurvey.uprn, BigInt(uprn)),
    orderBy: desc(nonInstrusiveSurvey.surveyDate),
    columns: { surveyDate: true, surveyor: true, uprn: true },
    limit: 1,
    with: {
      notes: {
        columns: {
          title: true,
          note: true,
        },
      },
    },
  });
  return data[0];
}

export function formatGeneralFeatures(
  conditionReportData: ConditionReport,
  propertyType: string
): GeneralFeature[] {
  // if a property is a flat/maisonette, we show heat loss coridoor information, otherwise we won't show it

  const flatOnlyFeatures = [
    {
      feature: "Heat loss corridor",
      description: conditionReportData.heatLossCorridor ? "Yes" : "No",
    },
    {
      feature: "Heat loss corridor length",
      description:
        conditionReportData.unheatedCorridorLength || "No heat loss corridor",
    },
    {
      feature: "Number of storeys",
      description: conditionReportData.numberStoreys ?? "Unknown",
    },
  ];

  const generealFeatures: GeneralFeature[] = [
    {
      feature: "Floor Height",
      description: conditionReportData.floorHeight ?? "Unknown",
    },
    {
      feature: "Number of heated rooms",
      description: conditionReportData.numberHeatedRooms ?? "Unknown",
    },
    {
      feature: "Number of open fire places",
      // ?? so a genuine 0 (no fireplaces) shows as 0, not "Unknown".
      description: conditionReportData.numberOpenFireplaces ?? "Unknown",
    },
    {
      feature: "Number of extensions",
      description: conditionReportData.numberExtensions ?? "Unknown",
    },
    {
      feature: "Mains gas",
      description: conditionReportData.mainsGas ? "Yes" : "No",
    },
    {
      feature: "Energy tariff",
      description: conditionReportData.energyTariff || "Unknown",
    },
    ...(propertyType === "Flat" || propertyType === "Maisonette"
      ? flatOnlyFeatures
      : []),
  ];

  return generealFeatures;
}

export function formatRetrofitFeatures(
  conditionReportData: ConditionReport
): Feature[] {
  // Helper function to convert rating number to string

  const retrofitFeatures: Feature[] = [
    {
      feature: "Wall",
      description: conditionReportData.walls || "Unknown",
      rating: getRating(conditionReportData.wallsRating),
    },
    {
      feature: "Roof",
      description: conditionReportData.roof || "Unknown",
      rating: getRating(conditionReportData.roofRating),
    },
    {
      feature: "Windows",
      description: conditionReportData.windows || "Unknown",
      rating: getRating(conditionReportData.windowsRating),
    },
    {
      feature: "Heating",
      description: conditionReportData.heating || "Unknown",
      rating: getRating(conditionReportData.heatingRating),
    },
    {
      feature: "Heating Control",
      description: conditionReportData.heatingControls || "Unknown",
      rating: getRating(conditionReportData.heatingControlsRating),
    },
    {
      feature: "Hot Water",
      description: conditionReportData.hotWater || "Unknown",
      rating: getRating(conditionReportData.hotWaterRating),
    },
    {
      feature: "Lighting",
      description: conditionReportData.lighting || "Unknown",
      rating: getRating(conditionReportData.lightingRating),
    },
    {
      feature: "Floor",
      description: conditionReportData.floor || "Unknown",
      rating: getRating(conditionReportData.floorRating),
    },
    {
      feature: "Ventilation",
      description: conditionReportData.ventilation || "Unknown",
      rating: "N/A",
    },
    {
      feature: "Main Fuel",
      description: conditionReportData.mainfuel || "Unknown",
      rating: "N/A",
    },
    {
      feature: "Solar Photo Voltaic",
      description:
        conditionReportData.solarPv !== 0 && conditionReportData.solarPv != null
          ? "Present in the property"
          : "Not present in the property",
      rating: "N/A",
    },
    {
      feature: "Solar Hot Water",
      description: conditionReportData.solarHotWater
        ? "Heating is solar powered"
        : "Heating is not solar powered",
      rating: "N/A",
    },
    {
      feature: "Wind Turbines",
      description:
        conditionReportData.windTurbine != null
          ? `${conditionReportData.windTurbine} wind turbines present`
          : "No wind turbines present",
      rating: "N/A",
    },
  ];

  return retrofitFeatures;
}

export function formatHeatDemandFeatures(
  conditionReportData: ConditionReport
): GeneralFeature[] {
  return [
    {
      feature: "Estimated energy consumption",
      description: conditionReportData.currentEnergyDemand
        ? `${conditionReportData.currentEnergyDemand.toFixed(0)} kWh/year`
        : "Unknown",
    },
    {
      feature: "EPC primary energy consumption",
      description: conditionReportData.primaryEnergyConsumption
        ? `${conditionReportData.primaryEnergyConsumption} kWh/m2/year`
        : "Unknown",
    },
    {
      feature: "EPC CO2 emissions",
      description: conditionReportData.co2Emissions
        ? `${conditionReportData.co2Emissions} tonnes/year`
        : "Unknown",
    },
    // These features haven't been implemented yet
    // {
    //   feature: "Electricity Supplier",
    //   description: conditionReportData.electricitySupplier || "unknown",
    // },
    // {
    //   feature: "Gas Supplier",
    //   description: conditionReportData.gasSupplier || "unknown",
    // },
    // {
    //   feature: "Meter reading total energy consumption",
    //   description: conditionReportData.totalEnergyConsumption
    //     ? `${conditionReportData.totalEnergyConsumption} kWh/m2/year`
    //     : "unknown",
    // },
    // {
    //   feature: "Meter reading electricity consumption",
    //   description: conditionReportData.electricityConsumption
    //     ? `${conditionReportData.electricityConsumption} kWh/m2/year`
    //     : "unknown",
    // },
    // {
    //   feature: "Meter reading gas consumption",
    //   description: conditionReportData.gasConsumption
    //     ? `${conditionReportData.gasConsumption} kWh/m2/year`
    //     : "unknown",
    // },
  ];
}

export async function getScenario(planId: string): Promise<ScenarioSelect | null> {
  const planData = await db.query.plan.findFirst({
    where: eq(plan.id, BigInt(planId)),
    columns: { scenarioId: true },
  });
  if (!planData?.scenarioId) return null;
  const data = await db.query.scenario.findFirst({
    where: eq(scenario.id, planData.scenarioId),
  });
  return data ?? null;
}

export async function getInstalledMeasuresByUprn(
  uprn: number
): Promise<InstalledMeasureSummary[]> {
  const data = await db
    .select({
      measureType: installedMeasure.measureType,
      installedAt: installedMeasure.installedAt,
      sapPoints: installedMeasure.sapPoints,
      carbonSavings: installedMeasure.carbonSavings,
      kwhSavings: installedMeasure.kwhSavings,
      billSavings: installedMeasure.billSavings,
    })
    .from(installedMeasure)
    .where(
      and(
        eq(installedMeasure.uprn, BigInt(uprn)),
        eq(installedMeasure.isActive, true)
      )
    );

  return data;
}
