/**
 * Activity timeline tables — activity-timeline (wave-12).
 *
 * Four per-entity activity log tables: invoice_activities, project_activities,
 * customer_activities, vendor_activities.
 *
 * Design decisions:
 * - Per-entity tables (not a single unified table) for FK CASCADE DELETE and
 *   entity-specific indexes.
 * - event_type and actor_type are text() + CHECK constraints, never pgEnum.
 * - GIN / DESC composite indexes are raw SQL only (appended to migration).
 * - deleted_at enables 15-minute author-only soft-delete of notes.
 */
import {
  pgTable,
  uuid,
  text,
  jsonb,
  timestamp,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { invoices } from './invoices'
import { projects } from './projects'
import { customers } from './customers'
import { vendors } from './vendors'
import { users } from './users'

// ── invoice_activities ────────────────────────────────────────────────────────

export const invoiceActivities = pgTable(
  'invoice_activities',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    invoiceId: uuid('invoice_id')
      .notNull()
      .references(() => invoices.id, { onDelete: 'cascade' }),
    actorId: uuid('actor_id').references(() => users.id),
    actorType: text('actor_type').notNull().default('user'),
    eventType: text('event_type').notNull(),
    metadata: jsonb('metadata').notNull().default({}),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
  },
  (t) => ({
    actorTypeCheck: check(
      'invoice_activities_actor_type_check',
      sql`${t.actorType} IN ('user','system','customer')`,
    ),
    eventTypeCheck: check(
      'invoice_activities_event_type_check',
      sql`${t.eventType} IN ('created','sent','approved','rejected','tax_issued','paid','voided','credit_noted','note_added','email_sent','viewed_by_customer','payment_initiated')`,
    ),
  }),
)

export type InvoiceActivityRow = typeof invoiceActivities.$inferSelect
export type NewInvoiceActivity = typeof invoiceActivities.$inferInsert

// ── project_activities ────────────────────────────────────────────────────────

export const projectActivities = pgTable(
  'project_activities',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),
    actorId: uuid('actor_id').references(() => users.id),
    actorType: text('actor_type').notNull().default('user'),
    eventType: text('event_type').notNull(),
    metadata: jsonb('metadata').notNull().default({}),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
  },
  (t) => ({
    actorTypeCheck: check(
      'project_activities_actor_type_check',
      sql`${t.actorType} IN ('user','system','customer')`,
    ),
    eventTypeCheck: check(
      'project_activities_event_type_check',
      sql`${t.eventType} IN ('created','status_changed','member_added','member_removed','note_added','invoice_linked','time_logged')`,
    ),
  }),
)

export type ProjectActivityRow = typeof projectActivities.$inferSelect
export type NewProjectActivity = typeof projectActivities.$inferInsert

// ── customer_activities ───────────────────────────────────────────────────────

export const customerActivities = pgTable(
  'customer_activities',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    actorId: uuid('actor_id').references(() => users.id),
    actorType: text('actor_type').notNull().default('user'),
    eventType: text('event_type').notNull(),
    metadata: jsonb('metadata').notNull().default({}),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
  },
  (t) => ({
    actorTypeCheck: check(
      'customer_activities_actor_type_check',
      sql`${t.actorType} IN ('user','system','customer')`,
    ),
    eventTypeCheck: check(
      'customer_activities_event_type_check',
      sql`${t.eventType} IN ('created','updated','archived','note_added','portal_user_invited','portal_user_activated','lead_converted')`,
    ),
  }),
)

export type CustomerActivityRow = typeof customerActivities.$inferSelect
export type NewCustomerActivity = typeof customerActivities.$inferInsert

// ── vendor_activities ─────────────────────────────────────────────────────────

export const vendorActivities = pgTable(
  'vendor_activities',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id, { onDelete: 'cascade' }),
    actorId: uuid('actor_id').references(() => users.id),
    actorType: text('actor_type').notNull().default('user'),
    eventType: text('event_type').notNull(),
    metadata: jsonb('metadata').notNull().default({}),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
  },
  (t) => ({
    actorTypeCheck: check(
      'vendor_activities_actor_type_check',
      sql`${t.actorType} IN ('user','system','customer')`,
    ),
    eventTypeCheck: check(
      'vendor_activities_event_type_check',
      sql`${t.eventType} IN ('created','updated','withholding_cert_uploaded','withholding_cert_expiring','archived','note_added')`,
    ),
  }),
)

export type VendorActivityRow = typeof vendorActivities.$inferSelect
export type NewVendorActivity = typeof vendorActivities.$inferInsert