/**
 * Proposals & Campaigns schema — marketing-catalogs-campaigns (wave-9 leaf 3).
 * wave-10 leaf 6 additions:
 *  - proposals.contractId: bidirectional link when proposal-to-contract runs
 *  - email_sequences: automated drip sequences
 *  - email_sequence_steps: per-step content & delay
 *  - email_sequence_enrollments: per-contact enrollment tracking
 * wave-11 leaf C additions (proposal-editor):
 *  - proposals.content: structured JSONB content (ProposalContent)
 *  - proposals.name: display name / subject (was title)
 *  - proposals.publicToken: opaque URL token for public /p/{token} view
 *  - proposals.viewCount, firstViewedAt, lastViewedAt: public view tracking
 *  - proposals.expiresAt: expiry date for proposal acceptance
 *  - proposals.locale: per-proposal locale for public page dir/lang
 *  - proposals.totalAmount: denormalized total_amount from content
 *  - proposals.createdBy: staff user who created the proposal
 *  - proposal_view_events: immutable public view event log
 * wave-11 leaf C additions (public-proposal-view):
 *  - proposals.status 'viewed' added to status check
 *
 * Enum-like columns use text() + CHECK constraints, never pgEnum.
 * JSONB columns use jsonb().
 * Plain btree indexes via drizzle index().
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  numeric,
  integer,
  jsonb,
  timestamp,
  index,
  check,
  uniqueIndex,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'

// ── proposals ─────────────────────────────────────────────────────────────────

export const proposals = pgTable(
  'proposals',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id').notNull(),
    customerId: uuid('customer_id'),
    leadId: uuid('lead_id'),
    // Set when proposal-to-contract conversion runs (wave-10 leaf6)
    contractId: uuid('contract_id'),
    title: text('title').notNull(),
    // wave-11: display name / subject (for editor; maps to title on send)
    name: text('name'),
    status: text('status').notNull().default('draft'),
    // wave-11: structured JSONB content (ProposalContent)
    content: jsonb('content'),
    lineItems: jsonb('line_items').notNull().$defaultFn(() => []),
    subtotal: numeric('subtotal', { precision: 12, scale: 2 }),
    discount: numeric('discount', { precision: 12, scale: 2 }),
    total: numeric('total', { precision: 12, scale: 2 }),
    // wave-11: denormalized total from content line_items sections (NUMERIC 10,2)
    totalAmount: numeric('total_amount', { precision: 10, scale: 2 }),
    notes: text('notes'),
    validUntil: timestamp('valid_until', { withTimezone: true }),
    // wave-11: proposal expiry for acceptance (maps to validUntil semantics for editor)
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    sentAt: timestamp('sent_at', { withTimezone: true }),
    // wave-11: public URL token for /p/{token}
    publicToken: text('public_token').unique(),
    // wave-11: view tracking
    viewCount: integer('view_count').notNull().default(0),
    firstViewedAt: timestamp('first_viewed_at', { withTimezone: true }),
    lastViewedAt: timestamp('last_viewed_at', { withTimezone: true }),
    acceptedAt: timestamp('accepted_at', { withTimezone: true }),
    acceptedByName: text('accepted_by_name'),
    rejectedAt: timestamp('rejected_at', { withTimezone: true }),
    rejectedReason: text('rejected_reason'),
    // wave-11: per-proposal locale for public page dir/lang
    locale: text('locale').default('he'),
    // wave-11: staff user who created the proposal
    createdBy: uuid('created_by'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    statusCheck: check(
      'proposals_status_check',
      sql`${t.status} IN ('draft','sent','viewed','accepted','rejected','expired')`,
    ),
    localeCheck: check(
      'proposals_locale_check',
      sql`${t.locale} IN ('he','en')`,
    ),
    tenantIdx: index('idx_proposals_tenant').on(t.tenantId),
    tenantStatusIdx: index('idx_proposals_tenant_status').on(t.tenantId, t.status),
    tenantCustomerIdx: index('idx_proposals_tenant_customer').on(t.tenantId, t.customerId),
    publicTokenIdx: uniqueIndex('idx_proposals_public_token').on(t.publicToken),
  }),
)

export type ProposalRow = typeof proposals.$inferSelect
export type NewProposal = typeof proposals.$inferInsert

// ── proposal_view_events ──────────────────────────────────────────────────────

export const proposalViewEvents = pgTable(
  'proposal_view_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    proposalId: uuid('proposal_id')
      .notNull()
      .references(() => proposals.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id').notNull(),
    ip: text('ip'),
    userAgent: text('user_agent'),
    referrer: text('referrer'),
    utmSource: text('utm_source'),
    utmMedium: text('utm_medium'),
    utmCampaign: text('utm_campaign'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    proposalIdx: index('idx_proposal_view_events_proposal').on(t.proposalId, t.createdAt),
    tenantIdx: index('idx_proposal_view_events_tenant').on(t.tenantId, t.createdAt),
  }),
)

export type ProposalViewEventRow = typeof proposalViewEvents.$inferSelect
export type NewProposalViewEvent = typeof proposalViewEvents.$inferInsert

// ── email_sequences ───────────────────────────────────────────────────────────

export const emailSequences = pgTable(
  'email_sequences',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id').notNull(),
    name: text('name').notNull(),
    triggerType: text('trigger_type').notNull().default('manual'),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    triggerCheck: check(
      'email_sequences_trigger_check',
      sql`${t.triggerType} IN ('manual','lead_created','proposal_sent','invoice_overdue')`,
    ),
    tenantIdx: index('idx_email_sequences_tenant').on(t.tenantId),
  }),
)

export type EmailSequenceRow = typeof emailSequences.$inferSelect
export type NewEmailSequence = typeof emailSequences.$inferInsert

// ── email_sequence_steps ──────────────────────────────────────────────────────

export const emailSequenceSteps = pgTable(
  'email_sequence_steps',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sequenceId: uuid('sequence_id').notNull().references(() => emailSequences.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id').notNull(),
    stepNumber: integer('step_number').notNull(),
    delayDays: integer('delay_days').notNull().default(0),
    subject: text('subject').notNull(),
    bodyHtml: text('body_html').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    sequenceIdx: index('idx_email_sequence_steps_sequence').on(t.sequenceId),
    tenantIdx: index('idx_email_sequence_steps_tenant').on(t.tenantId),
  }),
)

export type EmailSequenceStepRow = typeof emailSequenceSteps.$inferSelect
export type NewEmailSequenceStep = typeof emailSequenceSteps.$inferInsert

// ── email_sequence_enrollments ────────────────────────────────────────────────

export const emailSequenceEnrollments = pgTable(
  'email_sequence_enrollments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sequenceId: uuid('sequence_id').notNull().references(() => emailSequences.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id').notNull(),
    contactEmail: text('contact_email').notNull(),
    leadId: uuid('lead_id'),
    currentStep: integer('current_step').notNull().default(0),
    status: text('status').notNull().default('active'),
    enrolledAt: timestamp('enrolled_at', { withTimezone: true }).notNull().defaultNow(),
    nextSendAt: timestamp('next_send_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    statusCheck: check(
      'email_sequence_enrollments_status_check',
      sql`${t.status} IN ('active','completed','unsubscribed')`,
    ),
    sequenceIdx: index('idx_email_sequence_enrollments_sequence').on(t.sequenceId),
    tenantIdx: index('idx_email_sequence_enrollments_tenant').on(t.tenantId),
    nextSendIdx: index('idx_email_sequence_enrollments_next_send').on(t.tenantId, t.nextSendAt),
  }),
)

export type EmailSequenceEnrollmentRow = typeof emailSequenceEnrollments.$inferSelect
export type NewEmailSequenceEnrollment = typeof emailSequenceEnrollments.$inferInsert

// ── campaigns ─────────────────────────────────────────────────────────────────

export const campaigns = pgTable(
  'campaigns',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id').notNull(),
    name: text('name').notNull(),
    type: text('type').notNull().default('email'),
    status: text('status').notNull().default('draft'),
    targetSegment: jsonb('target_segment'),
    scheduledAt: timestamp('scheduled_at', { withTimezone: true }),
    sentCount: integer('sent_count').notNull().default(0),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    typeCheck: check(
      'campaigns_type_check',
      sql`${t.type} IN ('email','sms','push')`,
    ),
    statusCheck: check(
      'campaigns_status_check',
      sql`${t.status} IN ('draft','active','completed')`,
    ),
    tenantIdx: index('idx_campaigns_tenant').on(t.tenantId),
    tenantStatusIdx: index('idx_campaigns_tenant_status').on(t.tenantId, t.status),
  }),
)

export type CampaignRow = typeof campaigns.$inferSelect
export type NewCampaign = typeof campaigns.$inferInsert