/**
 * Reports & Analytics schema — custom analytics dashboards (wave A).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - dashboards:        per-user named dashboard layouts
 *  - dashboard_widgets: widget placement + config on a 12-column grid
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Plain btree indexes via drizzle index()
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  boolean,
  timestamp,
  jsonb,
  index,
  uniqueIndex,
  check,
} from 'drizzle-orm/pg-core'
import { eq, sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

export const dashboards = pgTable(
  'dashboards',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    isDefault: boolean('is_default').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantUserIdx: index('idx_dashboards_tenant_user').on(t.tenantId, t.userId),
    defaultUniq: uniqueIndex('idx_dashboards_tenant_user_default_uniq')
      .on(t.tenantId, t.userId)
      .where(eq(t.isDefault, true)),
  }),
)

export type DashboardRow = typeof dashboards.$inferSelect
export type NewDashboard = typeof dashboards.$inferInsert

export const dashboardWidgets = pgTable(
  'dashboard_widgets',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dashboardId: uuid('dashboard_id')
      .notNull()
      .references(() => dashboards.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    widgetType: text('widget_type').notNull(),
    positionX: integer('position_x').notNull(),
    positionY: integer('position_y').notNull(),
    width: integer('width').notNull().default(1),
    height: integer('height').notNull().default(1),
    config: jsonb('config').notNull().default(sql`'{}'::jsonb`),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    dashboardIdx: index('idx_dashboard_widgets_dashboard').on(t.dashboardId),
    widgetTypeCheck: check(
      'dashboard_widgets_widget_type_check',
      sql`${t.widgetType} IN (
        'revenue_kpi','invoice_status_pipeline','open_invoices_aging',
        'time_by_project','time_by_team_member','billable_vs_nonbillable',
        'customer_revenue','expense_breakdown','leads_funnel',
        'lead_pipeline_by_stage','ticket_resolution_time','ticket_volume_by_category'
      )`,
    ),
  }),
)

export type DashboardWidgetRow = typeof dashboardWidgets.$inferSelect
export type NewDashboardWidget = typeof dashboardWidgets.$inferInsert