/**
 * Marketing & Leads Pipeline schema — marketing-leads-pipeline.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - pipeline_stages:       per-tenant custom Kanban column definitions
 *  - leads:                 core lead rows with fractional-index stage_position
 *  - lead_activities:       immutable audit trail of all lead events
 *  - lead_forms:            embeddable lead capture forms (Business+ tier)
 *  - lead_form_submissions: raw submission payloads linked to leads
 *  - lead_webhooks:         inbound webhook integrations (FB, Google, Zapier, etc.)
 *
 * Plain btree indexes are declared here via drizzle index().
 * Partial / expression indexes are returned in manifest.raw_ddl
 * (drizzle-kit snapshot truncation bug — same convention as tasks.ts).
 *
 * Enum-like columns use text() + CHECK constraints, never pgEnum.
 * stage_position uses numeric() — same fractional-indexing pattern as tasks-board-engine.
 * JSONB columns (source_metadata, fields, style, field_mapping, payload) use jsonb().
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  numeric,
  integer,
  jsonb,
  timestamp,
  index,
  check,
  uniqueIndex,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'

// ── pipeline_stages ───────────────────────────────────────────────────────────

/**
 * Per-tenant custom stage definitions.
 * Default stages (NEW/CONTACTED/QUALIFIED/PROPOSAL/WON/LOST) are seeded on tenant creation.
 * `position` is a contiguous integer for ordering; fractional indexing not needed here (drag-to-reorder is a simple swap).
 */
export const pipelineStages = pgTable(
  'pipeline_stages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // slug key that leads.stage stores (e.g. 'NEW', 'CONTACTED')
    slug: text('slug').notNull(),
    color: text('color'),
    position: numeric('position').notNull(),
    // System stages cannot be deleted
    isSystem: boolean('is_system').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantSlugUniq: uniqueIndex('idx_pipeline_stages_tenant_slug_uniq').on(t.tenantId, t.slug),
    tenantPositionIdx: index('idx_pipeline_stages_tenant_position').on(t.tenantId, t.position),
  }),
)

export type PipelineStageRow = typeof pipelineStages.$inferSelect
export type NewPipelineStage = typeof pipelineStages.$inferInsert

// ── leads ─────────────────────────────────────────────────────────────────────

export const leads = pgTable(
  'leads',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    email: text('email'),
    phone: text('phone'),
    company: text('company'),
    notes: text('notes'),
    // 'NEW' | 'CONTACTED' | 'QUALIFIED' | 'PROPOSAL' | 'WON' | 'LOST'
    stage: text('stage').notNull().default('NEW'),
    // Fractional position within stage column (same pattern as tasks-board-engine)
    stagePosition: numeric('stage_position').notNull(),
    // 'manual' | 'form' | 'webhook' | 'facebook' | 'google' | 'linkedin' |
    // 'instagram' | 'zapier' | 'make' | 'referral' | 'cold_outreach'
    source: text('source').notNull().default('manual'),
    sourceMetadata: jsonb('source_metadata'),
    assignedTo: uuid('assigned_to').references(() => users.id, { onDelete: 'set null' }),
    // Set when lead is converted to customer
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),
    // Set when contract is linked (spec 48)
    contractId: uuid('contract_id'),
    lostReason: text('lost_reason'),
    estimatedValue: numeric('estimated_value'),
    utmSource: text('utm_source'),
    utmMedium: text('utm_medium'),
    utmCampaign: text('utm_campaign'),
    utmContent: text('utm_content'),
    utmTerm: text('utm_term'),
    // lead-qualification-scoring (wave-14): score 0–100 + when it was last calculated
    score: integer('score').notNull().default(0),
    scoreUpdatedAt: timestamp('score_updated_at', { withTimezone: true }),
    // lead-lost-re-engagement (wave-14): scheduled re-engagement date + notification sentinel
    reengagementAt: timestamp('reengagement_at', { withTimezone: true }),
    reengagementNotifiedAt: timestamp('reengagement_notified_at', { withTimezone: true }),
    // Soft-delete
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    stageCheck: check(
      'leads_stage_check',
      sql`${t.stage} IN ('NEW','CONTACTED','QUALIFIED','PROPOSAL','WON','LOST')`,
    ),
    sourceCheck: check(
      'leads_source_check',
      sql`${t.source} IN ('manual','form','webhook','facebook','google','linkedin','instagram','zapier','make','referral','cold_outreach')`,
    ),
    tenantCreatedIdx: index('idx_leads_tenant_created').on(t.tenantId, t.createdAt),
    // Partial/expression indexes returned in manifest.raw_ddl:
    //   idx_leads_tenant_stage      ON leads(tenant_id, stage, stage_position)
    //   idx_leads_assigned          ON leads(tenant_id, assigned_to) WHERE assigned_to IS NOT NULL
    //   idx_leads_customer          ON leads(tenant_id, customer_id) WHERE customer_id IS NOT NULL
    //   idx_leads_reengagement_due  ON leads(tenant_id, reengagement_at) WHERE stage='LOST' AND reengagement_notified_at IS NULL
  }),
)

export type LeadRow = typeof leads.$inferSelect
export type NewLead = typeof leads.$inferInsert

// ── lead_activities ───────────────────────────────────────────────────────────

export const leadActivities = pgTable(
  'lead_activities',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    leadId: uuid('lead_id')
      .notNull()
      .references(() => leads.id, { onDelete: 'cascade' }),
    // NULL for system-generated activity
    userId: uuid('user_id').references(() => users.id, { onDelete: 'set null' }),
    // 'note' | 'email_sent' | 'call_logged' | 'stage_changed' | 'form_submitted' |
    // 'webhook_received' | 'converted' | 'contract_linked'
    type: text('type').notNull(),
    content: text('content'),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    typeCheck: check(
      'lead_activities_type_check',
      sql`${t.type} IN ('note','email_sent','call_logged','stage_changed','form_submitted','webhook_received','converted','contract_linked')`,
    ),
    // Partial/expression indexes returned in manifest.raw_ddl:
    //   idx_lead_activities_lead ON lead_activities(lead_id, created_at DESC)
    tenantLeadIdx: index('idx_lead_activities_tenant_lead').on(t.tenantId, t.leadId),
  }),
)

export type LeadActivityRow = typeof leadActivities.$inferSelect
export type NewLeadActivity = typeof leadActivities.$inferInsert

// ── lead_forms ────────────────────────────────────────────────────────────────

export const leadForms = pgTable(
  'lead_forms',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // Used in embed URL: /f/{slug}. Unique per tenant, not globally.
    slug: text('slug').notNull(),
    // FieldConfig[] — see spec for shape
    fields: jsonb('fields').notNull(),
    // Redirect URL after submission; null = show inline thanks
    redirectUrl: text('redirect_url'),
    // Email to notify on new submission; defaults to tenant owner email
    notifyEmail: text('notify_email'),
    isActive: boolean('is_active').notNull().default(true),
    // { primaryColor: string, logoR2Key: string, fontFamily: string }
    style: jsonb('style'),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // slug unique per tenant (not globally — see architecture decision)
    tenantSlugUniq: uniqueIndex('idx_lead_forms_tenant_slug_uniq').on(t.tenantId, t.slug),
    // Partial/expression indexes returned in manifest.raw_ddl:
    //   idx_lead_forms_slug ON lead_forms(slug, tenant_id)
  }),
)

export type LeadFormRow = typeof leadForms.$inferSelect
export type NewLeadForm = typeof leadForms.$inferInsert

// ── lead_form_submissions ─────────────────────────────────────────────────────

export const leadFormSubmissions = pgTable(
  'lead_form_submissions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    formId: uuid('form_id')
      .notNull()
      .references(() => leadForms.id, { onDelete: 'cascade' }),
    leadId: uuid('lead_id')
      .notNull()
      .references(() => leads.id, { onDelete: 'cascade' }),
    // Raw form field values keyed by field id
    payload: jsonb('payload').notNull(),
    ip: text('ip'),
    userAgent: text('user_agent'),
    referrer: text('referrer'),
    utmSource: text('utm_source'),
    utmMedium: text('utm_medium'),
    utmCampaign: text('utm_campaign'),
    utmContent: text('utm_content'),
    utmTerm: text('utm_term'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    formIdx: index('idx_lead_form_submissions_form').on(t.formId, t.createdAt),
    leadIdx: index('idx_lead_form_submissions_lead').on(t.leadId),
  }),
)

export type LeadFormSubmissionRow = typeof leadFormSubmissions.$inferSelect
export type NewLeadFormSubmission = typeof leadFormSubmissions.$inferInsert

// ── lead_webhooks ─────────────────────────────────────────────────────────────

export const leadWebhooks = pgTable(
  'lead_webhooks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // 'facebook' | 'google' | 'linkedin' | 'instagram' | 'zapier' | 'make' | 'generic'
    source: text('source').notNull(),
    // HMAC signing secret; AES-256-GCM encrypted at rest via INTEGRATION_ENCRYPTION_KEY
    secret: text('secret').notNull(),
    // Map inbound JSON paths → leads columns; see FieldMapping shape in spec
    fieldMapping: jsonb('field_mapping'),
    isActive: boolean('is_active').notNull().default(true),
    lastReceivedAt: timestamp('last_received_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    sourceCheck: check(
      'lead_webhooks_source_check',
      sql`${t.source} IN ('facebook','google','linkedin','instagram','zapier','make','generic')`,
    ),
    tenantIdx: index('idx_lead_webhooks_tenant').on(t.tenantId),
  }),
)

export type LeadWebhookRow = typeof leadWebhooks.$inferSelect
export type NewLeadWebhook = typeof leadWebhooks.$inferInsert