import {
  bigserial,
  text,
  timestamp,
  pgTable,
  real,
  pgEnum,
  integer,
  bigint,
  unique,
} from "drizzle-orm/pg-core";
import { user } from "./users";
import { InferModel } from "drizzle-orm";

export const PortfolioStatus: [string, ...string[]] = [
  "scoping",
  "survey",
  "assessment",
  "tendering",
  "project underway",
  "completion; status: on track",
  "completion; status: delayed",
  "completion; status: at risk",
  "completion; status: completed",
  "needs review",
];

export const PortfolioGoal: [string, ...string[]] = [
  "Valuation Improvement",
  "Increasing EPC",
  "Reducing CO2 emissions",
  "Energy Savings",
  "None",
];
export type PortfolioGoalType = (typeof PortfolioGoal)[number];
export const PORTFOLIO_GOALS = {
  EPC: "Increasing EPC",
  VALUATION: "Valuation Improvement",
  CO2: "Reducing CO2 emissions",
  ENERGY: "Energy Savings",
  NONE: "None",
} satisfies Record<string, PortfolioGoalType>;

export const PortfolioRole: [string, ...string[]] = [
  "creator",
  "admin",
  "read",
  "write",
];

export const statusEnum = pgEnum("status", PortfolioStatus);
export const goalEnum = pgEnum("goal", PortfolioGoal);
export const roleEnum = pgEnum("role", PortfolioRole);

export const portfolio = pgTable("portfolio", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  name: text("name").notNull(),
  budget: real("budget"),
  status: statusEnum("status").notNull(),
  goal: goalEnum("goal").notNull(),
  cost: real("cost"),
  numberOfProperties: integer("number_of_properties"),
  co2EquivalentSavings: real("co2_equivalent_savings"), // Unit is always tonnes so we don't need to store unit
  energySavings: real("energy_savings"), // Unit is always kWh so we don't need to store unit
  energyCostSavings: real("energy_cost_savings"), // Unit is always £ so we don't need to store unit for the moment
  propertyValuationIncrease: real("property_valuation_increase"), // Unit is always £ so we don't need to store unit for the moment
  rentalYieldIncrease: real("rental_yield_increase"), // Unit is always £ so we don't need to store unit for the moment
  totalWorkHours: real("total_work_hours"),
  labourDays: real("labour_days"),
  createdAt: timestamp("created_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
  // Aggregates added for the summary tab
  epcBreakdownPreRetrofit: text("epc_breakdown_pre_retrofit"),
  epcBreakdownPostRetrofit: text("epc_breakdown_post_retrofit"),
  nUnitsToRetrofit: integer("n_units_to_retrofit"),
  co2PerUnitPreRetrofit: text("co2_per_unit_pre_retrofit"),
  co2PerUnitPostRetrofit: text("co2_per_unit_post_retrofit"),
  energyBillPerUnitPreRetrofit: text("energy_bill_per_unit_pre_retrofit"),
  energyBillPerUnitPostRetrofit: text("energy_bill_per_unit_post_retrofit"),
  energyConsumptionPerUnitPreRetrofit: text(
    "energy_consumption_per_unit_pre_retrofit",
  ),
  energyConsumptionPerUnitPostRetrofit: text(
    "energy_consumption_per_unit_post_retrofit",
  ),
  valuationImprovementPerUnit: text("valuation_improvement_per_unit"),
  costPerUnit: text("cost_per_unit"),
  costPerCo2Saved: text("cost_per_co2_saved"),
  costPerSapPoint: text("cost_per_sap_point"),
  valuationReturnOnInvestment: text("valuation_return_on_investment"),
});

export const portfolioUsers = pgTable("portfolioUsers", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  // Define the foreign key constraints using references from Drizzle, from user_id to the users table
  userId: bigint("user_id", { mode: "bigint" })
    .notNull()
    .references(() => user.id),
  // Define the foreign key constraints using references from Drizzle, from portfolio_id to the portfolios table
  portfolioId: bigint("portfolio_id", { mode: "bigint" })
    .notNull()
    .references(() => portfolio.id),
  role: roleEnum("role").notNull(),
  createdAt: timestamp("created_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
});

// Pending invitations to portfolios for emails that don't yet correspond to a
// user. Once the invitee signs in, the signIn callback consumes the row and
// creates a portfolioUsers entry atomically. Existing users skip this table and
// get a portfolioUsers row written at invite time.
export const portfolioInvitations = pgTable(
  "portfolioInvitations",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id, { onDelete: "cascade" }),
    email: text("email").notNull(),
    role: roleEnum("role").notNull(),
    invitedByUserId: bigint("invited_by_user_id", { mode: "bigint" })
      .notNull()
      .references(() => user.id),
    createdAt: timestamp("created_at", {
      precision: 6,
      withTimezone: true,
    })
      .defaultNow()
      .notNull(),
  },
  (t) => [unique("portfolio_invitations_portfolio_email_unique").on(t.portfolioId, t.email)],
);

export const PortfolioCapability: [string, ...string[]] = [
  "approver",
  "contractor",
];
export type PortfolioCapabilityType = "approver" | "contractor";

export const portfolioCapabilityEnum = pgEnum(
  "portfolio_capability",
  PortfolioCapability as [string, ...string[]],
);

export const portfolioCapabilities = pgTable(
  "portfolio_capabilities",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    userId: bigint("user_id", { mode: "bigint" })
      .notNull()
      .references(() => user.id),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id),
    capability: portfolioCapabilityEnum("capability").notNull(),
    createdAt: timestamp("created_at", { precision: 6, withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { precision: 6, withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (table) => [unique().on(table.userId, table.portfolioId, table.capability)],
);

export type Portfolio = InferModel<typeof portfolio, "select">;
export type NewPortfolio = InferModel<typeof portfolio, "insert">;
export type PortfolioUsers = InferModel<typeof portfolioUsers, "select">;
export type NewPortfolioUsers = InferModel<typeof portfolioUsers, "insert">;
export type PortfolioInvitation = InferModel<
  typeof portfolioInvitations,
  "select"
>;
export type NewPortfolioInvitation = InferModel<
  typeof portfolioInvitations,
  "insert"
>;
export type PortfolioCapabilities = InferModel<
  typeof portfolioCapabilities,
  "select"
>;
