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

// One row per survey request from a portfolio user. A deal can accumulate
// multiple requests over time; query by hubspotDealId ordered by requestedAt
// desc to get the latest.
export const surveyRequests = pgTable(
  "survey_requests",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    hubspotDealId: text("hubspot_deal_id").notNull(),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id),
    notes: text("notes").notNull(),
    surveyType: text("survey_type"),
    // 'pending' | 'fulfilled'
    status: text("status").notNull().default("pending"),
    requestedBy: bigint("requested_by", { mode: "bigint" })
      .notNull()
      .references(() => user.id),
    requestedAt: timestamp("requested_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    fulfilledAt: timestamp("fulfilled_at", { withTimezone: true }),
  },
  (table) => [
    index("idx_survey_requests_deal_id").on(table.hubspotDealId),
    index("idx_survey_requests_portfolio_id").on(table.portfolioId),
  ],
);

export type SurveyRequest = typeof surveyRequests.$inferSelect;
