/**
 * Customers module schema — four tables for tenant-scoped customer account management.
 * Postgres / Neon via Hyperdrive.
 *
 * - customers:              base customer records (soft-archiveable)
 * - customer_contacts:      contacts per customer (one marked primary)
 * - customer_portal_users:  portal access grants linking contacts → auth users
 * - customer_communications: unified timeline of all customer interactions
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  index,
  uniqueIndex,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── customers ─────────────────────────────────────────────────────────────────

export const customers = pgTable(
  'customers',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    taxId: text('tax_id'),
    company: text('company'),
    email: text('email'),
    phone: text('phone'),
    address: jsonb('address').$type<{
      street?: string
      city?: string
      state?: string
      zip?: string
      country?: string
    }>(),
    notes: text('notes'),
    status: text('status').notNull().default('active'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantStatusIdx: index('idx_customers_tenant_status').on(t.tenantId, t.status, t.createdAt),
    tenantNameIdx: index('idx_customers_tenant_name').on(t.tenantId, t.name),
    statusCheck: check(
      'customers_status_check',
      sql`${t.status} IN ('active', 'archived')`,
    ),
    // app-shell: GIN full-text index `customers_search_gin_idx` lives ONLY in the
    // migration SQL (0002_wave3_core.sql), NOT in this drizzle index() builder.
    // drizzle-kit's snapshot serializer truncates sql-expression indexes (drops the
    // closing paren), so declaring it here makes every `drizzle-kit generate` re-emit
    // it into the next wave's migration → cumulative replay fails "already exists".
    // Expression/GIN/partial indexes are raw-SQL-only by convention.
  }),
)

export type CustomerRow = typeof customers.$inferSelect
export type NewCustomer = typeof customers.$inferInsert

// ── customer_contacts ─────────────────────────────────────────────────────────

export const customerContacts = pgTable(
  'customer_contacts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    email: text('email').notNull(),
    phone: text('phone'),
    // 'primary' | 'billing' | 'technical' | custom free-text
    role: text('role'),
    isPrimary: boolean('is_primary').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    customerIdx: index('idx_customer_contacts_customer').on(t.tenantId, t.customerId),
  }),
)

export type CustomerContactRow = typeof customerContacts.$inferSelect
export type NewCustomerContact = typeof customerContacts.$inferInsert

// ── customer_portal_users ─────────────────────────────────────────────────────

export const customerPortalUsers = pgTable(
  'customer_portal_users',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    contactId: uuid('contact_id')
      .notNull()
      .references(() => customerContacts.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    portalRole: text('portal_role').notNull().default('customer_viewer'),
    status: text('status').notNull().default('active'),
    // wave-10 leaf 7: portal module access (customer-portal-access-control)
    modulesEnabled: jsonb('modules_enabled').$type<string[]>().notNull().default(sql`'[]'::jsonb`),
    invitedAt: timestamp('invited_at', { withTimezone: true }),
    acceptedAt: timestamp('accepted_at', { withTimezone: true }),
    // customer-portal-settings-ui (spec 136, wave 11): last login for Active portal users list
    lastLoginAt: timestamp('last_login_at', { withTimezone: true }),
    // tenant-portals (wave 9): portal locale; NULL = use tenant default
    locale: text('locale'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    customerIdx: index('idx_customer_portal_users_customer').on(t.tenantId, t.customerId),
    statusCheck: check(
      'customer_portal_users_status_check',
      sql`${t.status} IN ('active', 'frozen')`,
    ),
    localeCheck: check(
      'customer_portal_users_locale_check',
      sql`${t.locale} IS NULL OR ${t.locale} IN ('he', 'en')`,
    ),
  }),
)

export type CustomerPortalUserRow = typeof customerPortalUsers.$inferSelect
export type NewCustomerPortalUser = typeof customerPortalUsers.$inferInsert

// ── customer_communications ────────────────────────────────────────────────────

export const customerCommunications = pgTable(
  'customer_communications',
  {
    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' }),
    direction: text('direction').notNull(),
    channel: text('channel').notNull(),
    subject: text('subject'),
    body: text('body'),
    fromAddress: text('from_address'),
    toAddress: text('to_address'),
    relatedId: uuid('related_id'),
    relatedType: text('related_type'),
    sentAt: timestamp('sent_at', { withTimezone: true }).notNull().defaultNow(),
    // NULL for system-generated
    createdBy: uuid('created_by').references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    commsCustomerIdx: index('idx_customer_comms_customer').on(
      t.tenantId,
      t.customerId,
      t.sentAt.desc(),
    ),
    directionCheck: check(
      'customer_communications_direction_check',
      sql`${t.direction} IN ('outbound', 'inbound', 'internal')`,
    ),
    channelCheck: check(
      'customer_communications_channel_check',
      sql`${t.channel} IN ('email', 'telegram', 'ticket', 'note', 'system')`,
    ),
  }),
)

export type CustomerCommunicationRow = typeof customerCommunications.$inferSelect
export type NewCustomerCommunication = typeof customerCommunications.$inferInsert

// ── customer_merge_suggestions ────────────────────────────────────────────────
// P050 customer-dedup-merge: candidate pairs detected by the dedup engine.
// reason: 'email_match' — identical normalized email
//         'name_similarity' — trigram similarity ≥ 0.8 on (name, company)
// status lifecycle: pending → merged | dismissed
// matchMetadata stores the raw similarity scores and matched fields.
// Partial index idx_cms_tenant (WHERE status = 'pending') lives in raw_ddl only.

export const customerMergeSuggestions = pgTable(
  'customer_merge_suggestions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // The "winner" (survives the merge — FKs reassigned to this record)
    primaryCustomerId: uuid('primary_customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    // The "loser" (archived after merge)
    duplicateCustomerId: uuid('duplicate_customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'cascade' }),
    reason: text('reason').notNull(),
    // JSONB: { emailMatch?: boolean, similarityScore?: number, matchedFields?: string[] }
    matchMetadata: jsonb('match_metadata').$type<{
      emailMatch?: boolean
      similarityScore?: number
      matchedFields?: string[]
    }>(),
    status: text('status').notNull().default('pending'),
    resolvedBy: uuid('resolved_by').references(() => users.id, { onDelete: 'set null' }),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // unique btree on (tenant, primary, duplicate) — required for ON CONFLICT DO NOTHING in scanForDuplicates
    tenantPairUniq: uniqueIndex('idx_cms_tenant_pair').on(
      t.tenantId,
      t.primaryCustomerId,
      t.duplicateCustomerId,
    ),
    statusCheck: check(
      'customer_merge_suggestions_status_check',
      sql`${t.status} IN ('pending', 'merged', 'dismissed')`,
    ),
    reasonCheck: check(
      'customer_merge_suggestions_reason_check',
      sql`${t.reason} IN ('email_match', 'name_similarity')`,
    ),
    // Partial index idx_cms_tenant WHERE status = 'pending' lives in raw_ddl only
    // (drizzle snapshot truncation — see customers table comment above).
  }),
)

export type CustomerMergeSuggestionRow = typeof customerMergeSuggestions.$inferSelect
export type NewCustomerMergeSuggestion = typeof customerMergeSuggestions.$inferInsert