import { relations } from 'drizzle-orm';
import {
  date,
  index,
  integer,
  numeric,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';
import { users } from './auth';
import { portfolioTransactionTypeEnum } from './enums';

const financial = (name: string) => numeric(name, { precision: 38, scale: 18 });

export const portfolios = pgTable(
  'portfolios',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    ownerId: uuid('owner_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    name: varchar('name', { length: 120 }).notNull(),
    note: text('note'),
    version: integer('version').notNull().default(1),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('portfolios_owner_id_idx').on(table.ownerId),
    uniqueIndex('portfolios_owner_name_unique').on(table.ownerId, table.name),
  ],
);

export const portfolioTransactions = pgTable(
  'portfolio_transactions',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    portfolioId: uuid('portfolio_id')
      .notNull()
      .references(() => portfolios.id, { onDelete: 'cascade' }),
    type: portfolioTransactionTypeEnum('type').notNull(),
    date: date('date', { mode: 'string' }).notNull(),
    asset: varchar('asset', { length: 30 }),
    quantity: financial('quantity'),
    price: financial('price'),
    currency: varchar('currency', { length: 12 }),
    feeQuantity: financial('fee_quantity'),
    feeAsset: varchar('fee_asset', { length: 30 }),
    cashAmount: financial('cash_amount'),
    cashCurrency: varchar('cash_currency', { length: 12 }),
    note: text('note'),
    version: integer('version').notNull().default(1),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('portfolio_transactions_portfolio_date_idx').on(table.portfolioId, table.date),
    index('portfolio_transactions_asset_idx').on(table.asset),
  ],
);

export const portfoliosRelations = relations(portfolios, ({ one, many }) => ({
  owner: one(users, {
    fields: [portfolios.ownerId],
    references: [users.id],
  }),
  transactions: many(portfolioTransactions),
}));

export const portfolioTransactionsRelations = relations(portfolioTransactions, ({ one }) => ({
  portfolio: one(portfolios, {
    fields: [portfolioTransactions.portfolioId],
    references: [portfolios.id],
  }),
}));

export type Portfolio = typeof portfolios.$inferSelect;
export type PortfolioTransaction = typeof portfolioTransactions.$inferSelect;