import {
  bigserial,
  text,
  timestamp,
  pgTable,
  bigint,
  index,
} from "drizzle-orm/pg-core";
import { user } from "./users";
import { portfolio } from "./portfolio";
import { InferModel } from "drizzle-orm";

// One row per removal request. A property can have multiple requests over time
// (e.g. request → declined → new request). Query by hubspotDealId to get history.
export const propertyRemovalRequests = pgTable(
  "property_removal_requests",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    hubspotDealId: text("hubspot_deal_id").notNull(),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id),
    reason: text("reason").notNull(),
    // 'removal' | 're_addition'
    type: text("type").notNull().default("removal"),
    // 'pending' | 'approved' | 'declined'
    status: text("status").notNull().default("pending"),
    requestedBy: bigint("requested_by", { mode: "bigint" })
      .notNull()
      .references(() => user.id),
    requestedAt: timestamp("requested_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    reviewedBy: bigint("reviewed_by", { mode: "bigint" }).references(
      () => user.id,
    ),
    reviewedAt: timestamp("reviewed_at", { withTimezone: true }),
    originalBatch: text("original_batch"),
  },
  (table) => [
    index("idx_removal_requests_deal_id").on(table.hubspotDealId),
    index("idx_removal_requests_portfolio_id").on(table.portfolioId),
  ],
);

export type PropertyRemovalRequest = InferModel<
  typeof propertyRemovalRequests,
  "select"
>;
