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

// Per-user folders for organising portfolios on the home page. Folders are
// personal: two users on the same portfolio each keep their own grouping and
// ordering. Reordering renumbers position (0..n-1) in a single transaction.
export const userPortfolioFolders = pgTable(
  "user_portfolio_folders",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    userId: bigint("user_id", { mode: "bigint" })
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    position: integer("position").notNull(),
    createdAt: timestamp("created_at", {
      precision: 6,
      withTimezone: true,
    })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", {
      precision: 6,
      withTimezone: true,
    })
      .defaultNow()
      .notNull(),
  },
  (t) => [
    // Composite target for the same-owner FK on userPortfolioConfig.
    unique("user_portfolio_folders_id_user_unique").on(t.id, t.userId),
    index("idx_user_portfolio_folders_user_position").on(t.userId, t.position),
  ],
);

// One row per (user, portfolio): that user's personal configuration for the
// portfolio. Rows are created lazily on first write (upsert on the unique
// pair); no row means "all defaults". Future per-user concerns (archived,
// last_viewed_at, notification prefs) become columns here — this table is the
// user-config layer, distinct from portfolioUsers which is access control.
export const userPortfolioConfig = pgTable(
  "user_portfolio_config",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    userId: bigint("user_id", { mode: "bigint" })
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id, { onDelete: "cascade" }),
    // Starred iff non-null; the timestamp gives "recently starred" ordering.
    starredAt: timestamp("starred_at", {
      precision: 6,
      withTimezone: true,
    }),
    folderId: bigint("folder_id", { mode: "bigint" }),
    createdAt: timestamp("created_at", {
      precision: 6,
      withTimezone: true,
    })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", {
      precision: 6,
      withTimezone: true,
    })
      .defaultNow()
      .notNull(),
  },
  (t) => [
    unique("user_portfolio_config_user_portfolio_unique").on(
      t.userId,
      t.portfolioId,
    ),
    // A portfolio can only be filed into a folder owned by the same user: the
    // FK pairs folder_id with user_id against (id, user_id) on folders.
    // Deliberately NO ACTION on delete: composite FKs can't SET NULL just
    // folder_id (it would null user_id too), so folder deletion must unfile
    // its portfolios (SET folder_id = NULL) and delete in one transaction. A
    // raw DELETE bypassing that fails loudly instead of corrupting rows.
    foreignKey({
      columns: [t.folderId, t.userId],
      foreignColumns: [userPortfolioFolders.id, userPortfolioFolders.userId],
      name: "user_portfolio_config_folder_owner_fk",
    }),
    index("idx_user_portfolio_config_folder").on(t.folderId),
  ],
);

export type UserPortfolioFolder = InferModel<
  typeof userPortfolioFolders,
  "select"
>;
export type NewUserPortfolioFolder = InferModel<
  typeof userPortfolioFolders,
  "insert"
>;
export type UserPortfolioConfig = InferModel<
  typeof userPortfolioConfig,
  "select"
>;
export type NewUserPortfolioConfig = InferModel<
  typeof userPortfolioConfig,
  "insert"
>;
