/**
 * Calendar module schema — calendar-module.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - calendar_events:          manual + synced + task-derived events
 *  - calendar_connections:     per-user Google/Outlook OAuth connections
 *  - scheduling_connections:   tenant-level Calendly/Acuity/moCal integrations
 *
 * Plain btree column indexes declared here.
 * Expression/GIN/partial indexes returned in manifest.raw_ddl.
 *
 * BYTEA columns: Drizzle/Neon does not have a native bytea() builder in
 * drizzle-orm/pg-core; we use customType to map the raw column.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  index,
  check,
  unique,
  customType,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { tasks } from './tasks'
import { projects } from './projects'
import { customers } from './customers'

// ── BYTEA custom type ──────────────────────────────────────────────────────────

const bytea = customType<{ data: Uint8Array; driverData: Buffer }>({
  dataType() {
    return 'bytea'
  },
  toDriver(value: Uint8Array): Buffer {
    return Buffer.from(value)
  },
  fromDriver(value: Buffer): Uint8Array {
    return new Uint8Array(value)
  },
})

// ── calendar_events ────────────────────────────────────────────────────────────

export const calendarEvents = pgTable(
  'calendar_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    title: text('title').notNull(),
    description: text('description'),
    startAt: timestamp('start_at', { withTimezone: true }).notNull(),
    endAt: timestamp('end_at', { withTimezone: true }).notNull(),
    allDay: boolean('all_day').notNull().default(false),
    location: text('location'),
    source: text('source').notNull().default('manual'),
    taskId: uuid('task_id').references(() => tasks.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),
    externalId: text('external_id'),
    externalCalendarId: text('external_calendar_id'),
    syncedAt: timestamp('synced_at', { withTimezone: true }),
    syncStatus: text('sync_status').notNull().default('local'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantRangeIdx: index('idx_calendar_events_range').on(t.tenantId, t.startAt, t.endAt),
    externalUq: unique('uq_calendar_events_tenant_source_external').on(
      t.tenantId,
      t.source,
      t.externalId,
    ),
    tenantCreatedByIdx: index('idx_calendar_events_created_by').on(t.tenantId, t.createdBy),
    sourceCheck: check(
      'calendar_events_source_check',
      sql`${t.source} IN ('manual','task','project','google','outlook','calendly','acuity','mocal')`,
    ),
    syncStatusCheck: check(
      'calendar_events_sync_status_check',
      sql`${t.syncStatus} IN ('local','synced','pending','error')`,
    ),
  }),
)

export type CalendarEventRow = typeof calendarEvents.$inferSelect
export type NewCalendarEvent = typeof calendarEvents.$inferInsert

// ── calendar_connections ───────────────────────────────────────────────────────

export const calendarConnections = pgTable(
  'calendar_connections',
  {
    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' }),
    provider: text('provider').notNull(),
    externalUserId: text('external_user_id').notNull(),
    accessToken: bytea('access_token').notNull(),
    refreshToken: bytea('refresh_token').notNull(),
    tokenExpiresAt: timestamp('token_expires_at', { withTimezone: true }),
    connectedEmail: text('connected_email'),
    selectedCalendarId: text('selected_calendar_id'),
    selectedCalendarName: text('selected_calendar_name'),
    syncDirection: text('sync_direction').notNull().default('two_way'),
    syncTaskDueDates: boolean('sync_task_due_dates').notNull().default(true),
    syncManualEvents: boolean('sync_manual_events').notNull().default(true),
    syncCustomerMeetings: boolean('sync_customer_meetings').notNull().default(false),
    syncEnabled: boolean('sync_enabled').notNull().default(true),
    status: text('status').notNull().default('active'),
    lastSyncError: text('last_sync_error'),
    lastSyncedAt: timestamp('last_synced_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantUserIdx: index('idx_calendar_connections_user').on(t.tenantId, t.userId),
    tenantUserProviderUniq: unique('uq_calendar_connections_tenant_user_provider').on(
      t.tenantId,
      t.userId,
      t.provider,
    ),
    providerCheck: check(
      'calendar_connections_provider_check',
      sql`${t.provider} IN ('google','outlook')`,
    ),
    syncDirectionCheck: check(
      'calendar_connections_sync_direction_check',
      sql`${t.syncDirection} IN ('two_way','read_only','push_only')`,
    ),
    statusCheck: check(
      'calendar_connections_status_check',
      sql`${t.status} IN ('active','error','disconnected')`,
    ),
  }),
)

export type CalendarConnectionRow = typeof calendarConnections.$inferSelect
export type NewCalendarConnection = typeof calendarConnections.$inferInsert

// ── scheduling_connections ─────────────────────────────────────────────────────

export const schedulingConnections = pgTable(
  'scheduling_connections',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    apiKey: bytea('api_key').notNull(),
    webhookUri: text('webhook_uri'),
    settings: jsonb('settings').default(sql`'{}'::jsonb`),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantProviderUniq: unique('uq_scheduling_connections_tenant_provider').on(
      t.tenantId,
      t.provider,
    ),
    providerCheck: check(
      'scheduling_connections_provider_check',
      sql`${t.provider} IN ('calendly','acuity','mocal')`,
    ),
  }),
)

export type SchedulingConnectionRow = typeof schedulingConnections.$inferSelect
export type NewSchedulingConnection = typeof schedulingConnections.$inferInsert