/**
 * Drizzle Schema Generator
 *
 * Generate Drizzle ORM schema definitions from:
 * 1. TypeScript interfaces/types
 * 2. Database introspection
 *
 * This module creates idiomatic Drizzle schemas that can be used
 * for type-safe database queries.
 *
 * @module drizzle/schema-generator
 */

import type { Sql } from '../types.js'

// ============================================================================
// Type Definitions
// ============================================================================

/**
 * Supported PostgreSQL column types with Drizzle equivalents
 */
export interface DrizzleColumnType {
  /** Drizzle function name (e.g., 'text', 'integer', 'timestamp') */
  drizzleType: string
  /** Whether it needs import from pg-core */
  import: string
  /** Additional type parameters (e.g., precision for numeric) */
  params?: string
  /** TypeScript type for the column */
  tsType: string
}

/**
 * Column definition for schema generation
 */
export interface SchemaColumnDefinition {
  /** Column name in the database */
  name: string
  /** PostgreSQL data type */
  pgType: string
  /** Whether the column allows null */
  nullable: boolean
  /** Whether the column has a default value */
  hasDefault: boolean
  /** Default value expression */
  defaultValue?: string | undefined
  /** Whether this is a primary key */
  isPrimaryKey: boolean
  /** Whether this column is unique */
  isUnique: boolean
  /** Maximum length for string types */
  maxLength?: number | undefined
  /** Foreign key reference */
  references?: {
    table: string
    column: string
    onDelete?: string | undefined
    onUpdate?: string | undefined
  } | undefined
  /** Array type flag */
  isArray: boolean
}

/**
 * Index definition for schema generation
 */
export interface SchemaIndexDefinition {
  /** Index name */
  name: string
  /** Columns in the index */
  columns: string[]
  /** Whether the index is unique */
  isUnique: boolean
  /** Index type (btree, hash, gin, gist, etc.) */
  type?: string | undefined
  /** WHERE clause for partial indexes */
  where?: string | undefined
}

/**
 * Table definition for schema generation
 */
export interface SchemaTableDefinition {
  /** Table name */
  name: string
  /** Schema name (default: public) */
  schema: string
  /** Column definitions */
  columns: SchemaColumnDefinition[]
  /** Primary key columns (for composite keys) */
  primaryKey?: string[] | undefined
  /** Index definitions */
  indexes: SchemaIndexDefinition[]
  /** Check constraints */
  checkConstraints?: Array<{
    name: string
    expression: string
  }> | undefined
}

/**
 * Enum definition for schema generation
 */
export interface SchemaEnumDefinition {
  /** Enum name */
  name: string
  /** Enum values */
  values: string[]
}

/**
 * Complete database schema for generation
 */
export interface DatabaseSchemaDefinition {
  /** Tables in the schema */
  tables: SchemaTableDefinition[]
  /** Enum types */
  enums: SchemaEnumDefinition[]
  /** Schema name */
  schemaName: string
}

/**
 * Options for schema generation
 */
export interface SchemaGeneratorOptions {
  /** Database schema to introspect (default: 'public') */
  schema?: string
  /** Whether to include relations */
  includeRelations?: boolean
  /** Whether to include indexes */
  includeIndexes?: boolean
  /** Tables to include (if specified, only these tables are generated) */
  includeTables?: string[]
  /** Tables to exclude */
  excludeTables?: string[]
  /** Whether to generate types as well */
  generateTypes?: boolean
  /** Naming convention for table names */
  tableNaming?: 'camelCase' | 'PascalCase' | 'snake_case'
  /** Naming convention for column names */
  columnNaming?: 'camelCase' | 'snake_case'
  /** Whether to use singular table names */
  singularTableNames?: boolean
}

/**
 * TypeScript property definition for type-to-schema conversion
 */
export interface TypeScriptProperty {
  /** Property name */
  name: string
  /** TypeScript type string */
  type: string
  /** Whether the property is optional */
  optional: boolean
  /** JSDoc comment if any */
  comment?: string | undefined
}

/**
 * TypeScript interface definition for schema conversion
 */
export interface TypeScriptInterface {
  /** Interface name */
  name: string
  /** Properties */
  properties: TypeScriptProperty[]
  /** Extends clause */
  extends?: string[] | undefined
  /** JSDoc comment */
  comment?: string | undefined
}

// ============================================================================
// PostgreSQL to Drizzle Type Mapping
// ============================================================================

/**
 * Map PostgreSQL types to Drizzle column types
 */
const PG_TO_DRIZZLE_MAP: Record<string, DrizzleColumnType> = {
  // Integer types
  smallint: { drizzleType: 'smallint', import: 'smallint', tsType: 'number' },
  integer: { drizzleType: 'integer', import: 'integer', tsType: 'number' },
  int: { drizzleType: 'integer', import: 'integer', tsType: 'number' },
  int4: { drizzleType: 'integer', import: 'integer', tsType: 'number' },
  bigint: { drizzleType: 'bigint', import: 'bigint', tsType: 'bigint' },
  int8: { drizzleType: 'bigint', import: 'bigint', tsType: 'bigint' },
  serial: { drizzleType: 'serial', import: 'serial', tsType: 'number' },
  smallserial: { drizzleType: 'smallserial', import: 'smallserial', tsType: 'number' },
  bigserial: { drizzleType: 'bigserial', import: 'bigserial', tsType: 'bigint' },

  // Floating-point types
  real: { drizzleType: 'real', import: 'real', tsType: 'number' },
  float4: { drizzleType: 'real', import: 'real', tsType: 'number' },
  'double precision': { drizzleType: 'doublePrecision', import: 'doublePrecision', tsType: 'number' },
  float8: { drizzleType: 'doublePrecision', import: 'doublePrecision', tsType: 'number' },

  // Arbitrary precision
  numeric: { drizzleType: 'numeric', import: 'numeric', tsType: 'string' },
  decimal: { drizzleType: 'numeric', import: 'numeric', tsType: 'string' },

  // Character types
  'character varying': { drizzleType: 'varchar', import: 'varchar', tsType: 'string' },
  varchar: { drizzleType: 'varchar', import: 'varchar', tsType: 'string' },
  character: { drizzleType: 'char', import: 'char', tsType: 'string' },
  char: { drizzleType: 'char', import: 'char', tsType: 'string' },
  text: { drizzleType: 'text', import: 'text', tsType: 'string' },

  // Binary
  bytea: { drizzleType: 'bytea', import: 'bytea', tsType: 'Buffer' },

  // Boolean
  boolean: { drizzleType: 'boolean', import: 'boolean', tsType: 'boolean' },
  bool: { drizzleType: 'boolean', import: 'boolean', tsType: 'boolean' },

  // Date/time types
  date: { drizzleType: 'date', import: 'date', tsType: 'string' },
  time: { drizzleType: 'time', import: 'time', tsType: 'string' },
  'time with time zone': { drizzleType: 'time', import: 'time', tsType: 'string' },
  'time without time zone': { drizzleType: 'time', import: 'time', tsType: 'string' },
  timestamp: { drizzleType: 'timestamp', import: 'timestamp', tsType: 'Date' },
  'timestamp with time zone': { drizzleType: 'timestamp', import: 'timestamp', tsType: 'Date' },
  'timestamp without time zone': { drizzleType: 'timestamp', import: 'timestamp', tsType: 'Date' },
  timestamptz: { drizzleType: 'timestamp', import: 'timestamp', tsType: 'Date' },
  interval: { drizzleType: 'interval', import: 'interval', tsType: 'string' },

  // UUID
  uuid: { drizzleType: 'uuid', import: 'uuid', tsType: 'string' },

  // JSON types
  json: { drizzleType: 'json', import: 'json', tsType: 'unknown' },
  jsonb: { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },

  // Network types
  inet: { drizzleType: 'inet', import: 'inet', tsType: 'string' },
  cidr: { drizzleType: 'cidr', import: 'cidr', tsType: 'string' },
  macaddr: { drizzleType: 'macaddr', import: 'macaddr', tsType: 'string' },
  macaddr8: { drizzleType: 'macaddr8', import: 'macaddr8', tsType: 'string' },

  // Geometric types
  point: { drizzleType: 'point', import: 'point', tsType: '{ x: number; y: number }' },
  line: { drizzleType: 'line', import: 'line', tsType: 'string' },
  lseg: { drizzleType: 'line', import: 'line', tsType: 'string' },
  box: { drizzleType: 'customType', import: 'customType', tsType: 'string' },
  path: { drizzleType: 'customType', import: 'customType', tsType: 'string' },
  polygon: { drizzleType: 'customType', import: 'customType', tsType: 'string' },
  circle: { drizzleType: 'customType', import: 'customType', tsType: 'string' },
}

/**
 * Map TypeScript types to Drizzle column types
 */
const TS_TO_DRIZZLE_MAP: Record<string, DrizzleColumnType> = {
  string: { drizzleType: 'text', import: 'text', tsType: 'string' },
  number: { drizzleType: 'integer', import: 'integer', tsType: 'number' },
  bigint: { drizzleType: 'bigint', import: 'bigint', tsType: 'bigint' },
  boolean: { drizzleType: 'boolean', import: 'boolean', tsType: 'boolean' },
  Date: { drizzleType: 'timestamp', import: 'timestamp', tsType: 'Date' },
  object: { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },
  'Record<string, unknown>': { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },
  'Record<string, any>': { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },
  unknown: { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },
  any: { drizzleType: 'jsonb', import: 'jsonb', tsType: 'unknown' },
  Buffer: { drizzleType: 'bytea', import: 'bytea', tsType: 'Buffer' },
  Uint8Array: { drizzleType: 'bytea', import: 'bytea', tsType: 'Buffer' },
}

// ============================================================================
// Database Introspection
// ============================================================================

/**
 * Introspect a PostgreSQL database and return schema definitions
 *
 * @example
 * ```typescript
 * const schema = await introspectDatabase(sql, { schema: 'public' })
 * const drizzleCode = generateDrizzleSchema(schema)
 * ```
 */
export async function introspectDatabase(
  client: Sql,
  options: SchemaGeneratorOptions = {}
): Promise<DatabaseSchemaDefinition> {
  const schema = options.schema ?? 'public'

  // Get tables
  const tables = await introspectTables(client, schema, options)

  // Get enums
  const enums = await introspectEnums(client, schema)

  return {
    tables,
    enums,
    schemaName: schema,
  }
}

/**
 * Introspect all tables in a schema
 */
async function introspectTables(
  client: Sql,
  schema: string,
  options: SchemaGeneratorOptions
): Promise<SchemaTableDefinition[]> {
  const tablesQuery = `
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = $1
      AND table_type = 'BASE TABLE'
    ORDER BY table_name
  `
  const tablesResult = await client.unsafe<{ table_name: string }>(tablesQuery, [schema])

  const tables: SchemaTableDefinition[] = []

  for (const { table_name } of tablesResult) {
    // Check include/exclude lists
    if (options.includeTables && !options.includeTables.includes(table_name)) {
      continue
    }
    if (options.excludeTables && options.excludeTables.includes(table_name)) {
      continue
    }
    // Skip internal tables
    if (table_name.startsWith('_')) {
      continue
    }

    const table = await introspectTable(client, schema, table_name, options)
    tables.push(table)
  }

  return tables
}

/**
 * Introspect a single table
 */
async function introspectTable(
  client: Sql,
  schema: string,
  tableName: string,
  options: SchemaGeneratorOptions
): Promise<SchemaTableDefinition> {
  // Get columns
  const columnsQuery = `
    SELECT
      c.column_name,
      c.data_type,
      c.udt_name,
      c.is_nullable,
      c.column_default,
      c.character_maximum_length,
      c.numeric_precision,
      c.numeric_scale
    FROM information_schema.columns c
    WHERE c.table_schema = $1 AND c.table_name = $2
    ORDER BY c.ordinal_position
  `
  const columnsResult = await client.unsafe<{
    column_name: string
    data_type: string
    udt_name: string
    is_nullable: string
    column_default: string | null
    character_maximum_length: number | null
    numeric_precision: number | null
    numeric_scale: number | null
  }>(columnsQuery, [schema, tableName])

  // Get primary key columns
  const pkQuery = `
    SELECT a.attname as column_name
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    WHERE i.indrelid = $1::regclass
      AND i.indisprimary
  `
  const pkResult = await client.unsafe<{ column_name: string }>(pkQuery, [
    `${schema}.${tableName}`,
  ])
  const primaryKeyColumns = new Set(pkResult.map((r) => r.column_name))

  // Get unique constraints
  const uniqueQuery = `
    SELECT a.attname as column_name
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    WHERE i.indrelid = $1::regclass
      AND i.indisunique
      AND NOT i.indisprimary
  `
  const uniqueResult = await client.unsafe<{ column_name: string }>(uniqueQuery, [
    `${schema}.${tableName}`,
  ])
  const uniqueColumns = new Set(uniqueResult.map((r) => r.column_name))

  // Get foreign keys
  const fkQuery = `
    SELECT
      kcu.column_name,
      ccu.table_name AS foreign_table_name,
      ccu.column_name AS foreign_column_name,
      rc.delete_rule,
      rc.update_rule
    FROM information_schema.key_column_usage kcu
    JOIN information_schema.referential_constraints rc
      ON kcu.constraint_name = rc.constraint_name
      AND kcu.constraint_schema = rc.constraint_schema
    JOIN information_schema.constraint_column_usage ccu
      ON rc.unique_constraint_name = ccu.constraint_name
      AND rc.unique_constraint_schema = ccu.constraint_schema
    WHERE kcu.table_schema = $1 AND kcu.table_name = $2
  `
  const fkResult = await client.unsafe<{
    column_name: string
    foreign_table_name: string
    foreign_column_name: string
    delete_rule: string
    update_rule: string
  }>(fkQuery, [schema, tableName])
  const foreignKeys = new Map(fkResult.map((fk) => [fk.column_name, fk]))

  // Build column definitions
  const columns: SchemaColumnDefinition[] = columnsResult.map((col) => {
    const fk = foreignKeys.get(col.column_name)
    const isArray = col.data_type === 'ARRAY'
    const pgType = isArray ? col.udt_name.replace(/^_/, '') : col.data_type

    return {
      name: col.column_name,
      pgType: pgType.toLowerCase(),
      nullable: col.is_nullable === 'YES',
      hasDefault: col.column_default !== null,
      defaultValue: col.column_default ?? undefined,
      isPrimaryKey: primaryKeyColumns.has(col.column_name),
      isUnique: uniqueColumns.has(col.column_name),
      maxLength: col.character_maximum_length ?? undefined,
      references: fk
        ? {
            table: fk.foreign_table_name,
            column: fk.foreign_column_name,
            onDelete: fk.delete_rule,
            onUpdate: fk.update_rule,
          }
        : undefined,
      isArray,
    }
  })

  // Get indexes if requested
  const indexes: SchemaIndexDefinition[] = []
  if (options.includeIndexes) {
    const indexQuery = `
      SELECT
        i.relname AS index_name,
        array_agg(a.attname ORDER BY array_position(ix.indkey, a.attnum)) AS columns,
        ix.indisunique AS is_unique,
        am.amname AS index_type,
        pg_get_expr(ix.indpred, ix.indrelid) AS where_clause
      FROM pg_index ix
      JOIN pg_class i ON i.oid = ix.indexrelid
      JOIN pg_class t ON t.oid = ix.indrelid
      JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
      JOIN pg_am am ON am.oid = i.relam
      JOIN pg_namespace n ON n.oid = t.relnamespace
      WHERE n.nspname = $1
        AND t.relname = $2
        AND NOT ix.indisprimary
      GROUP BY i.relname, ix.indisunique, am.amname, ix.indpred, ix.indrelid
    `
    const indexResult = await client.unsafe<{
      index_name: string
      columns: string[]
      is_unique: boolean
      index_type: string
      where_clause: string | null
    }>(indexQuery, [schema, tableName])

    for (const idx of indexResult) {
      indexes.push({
        name: idx.index_name,
        columns: idx.columns,
        isUnique: idx.is_unique,
        type: idx.index_type,
        where: idx.where_clause ?? undefined,
      })
    }
  }

  return {
    name: tableName,
    schema,
    columns,
    primaryKey: primaryKeyColumns.size > 1 ? Array.from(primaryKeyColumns) : undefined,
    indexes,
  }
}

/**
 * Introspect enum types in a schema
 */
async function introspectEnums(client: Sql, schema: string): Promise<SchemaEnumDefinition[]> {
  const enumQuery = `
    SELECT
      t.typname AS enum_name,
      array_agg(e.enumlabel ORDER BY e.enumsortorder) AS enum_values
    FROM pg_type t
    JOIN pg_enum e ON t.oid = e.enumtypid
    JOIN pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname = $1
    GROUP BY t.typname
  `
  const enumResult = await client.unsafe<{
    enum_name: string
    enum_values: string[]
  }>(enumQuery, [schema])

  return enumResult.map((e) => ({
    name: e.enum_name,
    values: e.enum_values,
  }))
}

// ============================================================================
// TypeScript Interface Parsing
// ============================================================================

/**
 * Parse TypeScript interface from source code string
 *
 * @example
 * ```typescript
 * const interfaces = parseTypeScriptInterfaces(`
 *   interface User {
 *     id: string;
 *     email: string;
 *     createdAt: Date;
 *   }
 * `)
 * ```
 */
export function parseTypeScriptInterfaces(source: string): TypeScriptInterface[] {
  const interfaces: TypeScriptInterface[] = []

  // Match interface declarations
  const interfaceRegex = /(?:\/\*\*[\s\S]*?\*\/\s*)?export\s+(?:interface|type)\s+(\w+)(?:\s+extends\s+([\w,\s]+))?\s*(?:=\s*)?\{([^}]*)\}/g

  let match
  while ((match = interfaceRegex.exec(source)) !== null) {
    const name = match[1]
    if (!name) continue

    const extendsClause = match[2]?.split(',').map((s) => s.trim()).filter(Boolean)
    const body = match[3] ?? ''

    // Parse properties
    const properties = parseInterfaceProperties(body)

    // Extract JSDoc comment
    const commentMatch = source.slice(0, match.index).match(/\/\*\*[\s\S]*?\*\/\s*$/)

    interfaces.push({
      name,
      properties,
      extends: extendsClause,
      comment: commentMatch?.[0],
    })
  }

  return interfaces
}

/**
 * Parse properties from interface body
 */
function parseInterfaceProperties(body: string): TypeScriptProperty[] {
  const properties: TypeScriptProperty[] = []

  // Match property declarations
  const propRegex = /(?:\/\*\*[\s\S]*?\*\/\s*)?(?:readonly\s+)?(\w+)(\?)?:\s*([^;]+);/g

  let match
  while ((match = propRegex.exec(body)) !== null) {
    const name = match[1]
    const optional = !!match[2]
    const type = match[3]?.trim() ?? 'unknown'

    if (!name) continue

    // Extract JSDoc comment
    const commentMatch = body.slice(0, match.index).match(/\/\*\*[\s\S]*?\*\/\s*$/)

    properties.push({
      name,
      type,
      optional,
      comment: commentMatch?.[0],
    })
  }

  return properties
}

/**
 * Convert TypeScript interfaces to table definitions
 *
 * @example
 * ```typescript
 * const tables = convertInterfacesToTables(interfaces, {
 *   singularTableNames: false,
 * })
 * ```
 */
export function convertInterfacesToTables(
  interfaces: TypeScriptInterface[],
  options: SchemaGeneratorOptions = {}
): SchemaTableDefinition[] {
  return interfaces.map((iface) => convertInterfaceToTable(iface, options))
}

/**
 * Convert a single TypeScript interface to a table definition
 */
function convertInterfaceToTable(
  iface: TypeScriptInterface,
  options: SchemaGeneratorOptions
): SchemaTableDefinition {
  const tableName = convertTableName(iface.name, options)

  const columns: SchemaColumnDefinition[] = iface.properties.map((prop) => {
    const columnName = convertColumnName(prop.name, options)
    const columnType = inferColumnType(prop)

    return {
      name: columnName,
      pgType: columnType.pgType,
      nullable: prop.optional || prop.type.includes('null'),
      hasDefault: isAutoGeneratedColumn(prop.name, prop.type),
      defaultValue: getDefaultValue(prop.name, prop.type),
      isPrimaryKey: isIdColumn(prop.name),
      isUnique: false,
      isArray: prop.type.endsWith('[]'),
    }
  })

  // Auto-detect primary key
  const idColumn = columns.find((c) => isIdColumn(c.name))
  if (idColumn) {
    idColumn.isPrimaryKey = true
  }

  return {
    name: tableName,
    schema: options.schema ?? 'public',
    columns,
    indexes: [],
  }
}

/**
 * Infer PostgreSQL column type from TypeScript property
 */
function inferColumnType(prop: TypeScriptProperty): { pgType: string; drizzleType: string } {
  const type = prop.type.replace(/\s*\|\s*null\s*/g, '').replace(/\s*\|\s*undefined\s*/g, '')

  // Handle array types
  if (type.endsWith('[]')) {
    const baseType = type.slice(0, -2)
    const baseMapping = TS_TO_DRIZZLE_MAP[baseType]
    if (baseMapping) {
      return { pgType: `${baseMapping.drizzleType}[]`, drizzleType: baseMapping.drizzleType }
    }
    return { pgType: 'jsonb', drizzleType: 'jsonb' }
  }

  // Check for special patterns
  const lowerName = prop.name.toLowerCase()

  // UUID patterns
  if (lowerName === 'id' || lowerName.endsWith('id') || lowerName.endsWith('_id')) {
    if (type === 'string') {
      return { pgType: 'uuid', drizzleType: 'uuid' }
    }
  }

  // Timestamp patterns
  if (
    lowerName.includes('created') ||
    lowerName.includes('updated') ||
    lowerName.includes('deleted') ||
    lowerName.endsWith('_at') ||
    lowerName.endsWith('at')
  ) {
    if (type === 'Date' || type === 'string') {
      return { pgType: 'timestamp', drizzleType: 'timestamp' }
    }
  }

  // Email pattern
  if (lowerName === 'email' || lowerName.includes('email')) {
    return { pgType: 'text', drizzleType: 'text' }
  }

  // Check direct mapping
  const mapping = TS_TO_DRIZZLE_MAP[type]
  if (mapping) {
    return { pgType: mapping.drizzleType, drizzleType: mapping.drizzleType }
  }

  // Default to jsonb for complex types
  return { pgType: 'jsonb', drizzleType: 'jsonb' }
}

// ============================================================================
// Schema Code Generation
// ============================================================================

/**
 * Generate Drizzle schema code from database schema definition
 *
 * @example
 * ```typescript
 * const schema = await introspectDatabase(sql)
 * const code = generateDrizzleSchema(schema)
 * console.log(code)
 * ```
 */
export function generateDrizzleSchema(
  schema: DatabaseSchemaDefinition,
  options: SchemaGeneratorOptions = {}
): string {
  const lines: string[] = []
  const imports = new Set<string>()

  // Always need pgTable
  imports.add('pgTable')

  // Collect all needed imports
  for (const table of schema.tables) {
    for (const col of table.columns) {
      const drizzleType = getDrizzleType(col)
      imports.add(drizzleType.import)
    }
  }

  // Add pgEnum if we have enums
  if (schema.enums.length > 0) {
    imports.add('pgEnum')
  }

  // Generate header
  lines.push('/**')
  lines.push(' * Auto-generated Drizzle schema')
  lines.push(` * Generated at: ${new Date().toISOString()}`)
  lines.push(' *')
  lines.push(' * @module schema')
  lines.push(' */')
  lines.push('')

  // Generate imports
  lines.push(`import { ${Array.from(imports).sort().join(', ')} } from 'drizzle-orm/pg-core'`)

  if (options.includeRelations) {
    lines.push(`import { relations } from 'drizzle-orm'`)
  }

  lines.push('')

  // Generate enums
  for (const enumDef of schema.enums) {
    lines.push(generateEnumCode(enumDef))
    lines.push('')
  }

  // Generate tables
  for (const table of schema.tables) {
    lines.push(generateTableCode(table, schema, options))
    lines.push('')
  }

  // Generate relations if requested
  if (options.includeRelations) {
    const relationCode = generateRelationsCode(schema.tables)
    if (relationCode) {
      lines.push(relationCode)
    }
  }

  return lines.join('\n')
}

/**
 * Generate Drizzle enum code
 */
function generateEnumCode(enumDef: SchemaEnumDefinition): string {
  const varName = toCamelCase(enumDef.name)
  const values = enumDef.values.map((v) => `'${v}'`).join(', ')
  return `export const ${varName}Enum = pgEnum('${enumDef.name}', [${values}])`
}

/**
 * Generate Drizzle table code
 */
function generateTableCode(
  table: SchemaTableDefinition,
  schema: DatabaseSchemaDefinition,
  options: SchemaGeneratorOptions
): string {
  const lines: string[] = []
  const varName = toCamelCase(table.name)

  lines.push(`export const ${varName} = pgTable('${table.name}', {`)

  for (let i = 0; i < table.columns.length; i++) {
    const col = table.columns[i]
    if (!col) continue

    const isLast = i === table.columns.length - 1
    const colCode = generateColumnCode(col, schema.enums)
    lines.push(`  ${colCode}${isLast ? '' : ','}`)
  }

  // Add composite primary key if needed
  if (table.primaryKey && table.primaryKey.length > 1) {
    lines.push('}, (table) => ({')
    lines.push(`  pk: primaryKey({ columns: [${table.primaryKey.map((c) => `table.${toCamelCase(c)}`).join(', ')}] }),`)
    lines.push('})')
  } else if (options.includeIndexes && table.indexes.length > 0) {
    lines.push('}, (table) => ({')
    for (const idx of table.indexes) {
      const idxName = toCamelCase(idx.name.replace(new RegExp(`^${table.name}_`), ''))
      const columns = idx.columns.map((c) => `table.${toCamelCase(c)}`).join(', ')
      if (idx.isUnique) {
        lines.push(`  ${idxName}: uniqueIndex('${idx.name}').on(${columns}),`)
      } else {
        lines.push(`  ${idxName}: index('${idx.name}').on(${columns}),`)
      }
    }
    lines.push('})')
  } else {
    lines.push('})')
  }

  return lines.join('\n')
}

/**
 * Generate Drizzle column code
 */
function generateColumnCode(col: SchemaColumnDefinition, enums: SchemaEnumDefinition[]): string {
  const colName = toCamelCase(col.name)
  const drizzleType = getDrizzleType(col)

  // Check if this is an enum type
  const enumDef = enums.find((e) => e.name === col.pgType)

  let code: string

  if (enumDef) {
    // Enum column
    const enumVarName = toCamelCase(enumDef.name)
    code = `${colName}: ${enumVarName}Enum('${col.name}')`
  } else if (col.maxLength && (drizzleType.drizzleType === 'varchar' || drizzleType.drizzleType === 'char')) {
    // String with length
    code = `${colName}: ${drizzleType.drizzleType}('${col.name}', { length: ${col.maxLength} })`
  } else if (drizzleType.drizzleType === 'timestamp') {
    // Timestamp with mode
    code = `${colName}: ${drizzleType.drizzleType}('${col.name}', { mode: 'date' })`
  } else if (col.isArray) {
    // Array type
    code = `${colName}: ${drizzleType.drizzleType}('${col.name}').array()`
  } else {
    // Standard column
    code = `${colName}: ${drizzleType.drizzleType}('${col.name}')`
  }

  // Add modifiers
  if (col.isPrimaryKey) {
    code += '.primaryKey()'
  }
  if (!col.nullable && !col.isPrimaryKey) {
    code += '.notNull()'
  }
  if (col.isUnique) {
    code += '.unique()'
  }
  if (col.hasDefault && col.defaultValue) {
    code += generateDefaultCode(col)
  }
  if (col.references) {
    const refTable = toCamelCase(col.references.table)
    const refCol = toCamelCase(col.references.column)
    code += `.references(() => ${refTable}.${refCol}`
    if (col.references.onDelete && col.references.onDelete !== 'NO ACTION') {
      code += `, { onDelete: '${col.references.onDelete.toLowerCase()}' }`
    }
    code += ')'
  }

  return code
}

/**
 * Generate default value code
 */
function generateDefaultCode(col: SchemaColumnDefinition): string {
  if (!col.defaultValue) return ''

  const def = col.defaultValue.toLowerCase()

  // Handle common defaults
  if (def === 'now()' || def === 'current_timestamp') {
    return '.defaultNow()'
  }
  if (def === 'gen_random_uuid()' || def === 'uuid_generate_v4()') {
    return '.$defaultFn(() => crypto.randomUUID())'
  }
  if (def === 'true' || def === 'false') {
    return `.default(${def})`
  }
  if (/^-?\d+$/.test(def)) {
    return `.default(${def})`
  }
  if (/^'.*'::/.test(col.defaultValue)) {
    const value = col.defaultValue.match(/^'([^']*)'/)
    if (value) {
      return `.default('${value[1]}')`
    }
  }

  // Skip complex defaults
  return ''
}

/**
 * Generate relations code
 */
function generateRelationsCode(tables: SchemaTableDefinition[]): string {
  const lines: string[] = []

  // Build a map of foreign key relationships
  const relationships: Map<string, Array<{ from: string; to: string; column: string; refColumn: string }>> = new Map()

  for (const table of tables) {
    for (const col of table.columns) {
      if (col.references) {
        const key = table.name
        if (!relationships.has(key)) {
          relationships.set(key, [])
        }
        relationships.get(key)!.push({
          from: table.name,
          to: col.references.table,
          column: col.name,
          refColumn: col.references.column,
        })
      }
    }
  }

  // Generate relations for tables with foreign keys
  for (const [tableName, rels] of relationships) {
    const varName = toCamelCase(tableName)
    lines.push(`export const ${varName}Relations = relations(${varName}, ({ one, many }) => ({`)

    for (const rel of rels) {
      const relVarName = toCamelCase(rel.to)
      const fieldName = rel.column.replace(/_id$/, '').replace(/Id$/, '')
      lines.push(`  ${toCamelCase(fieldName)}: one(${relVarName}, {`)
      lines.push(`    fields: [${varName}.${toCamelCase(rel.column)}],`)
      lines.push(`    references: [${relVarName}.${toCamelCase(rel.refColumn)}],`)
      lines.push('  }),')
    }

    lines.push('}))')
    lines.push('')
  }

  // Generate reverse relations
  for (const table of tables) {
    const incomingRels = Array.from(relationships.values())
      .flat()
      .filter((r) => r.to === table.name)

    if (incomingRels.length > 0) {
      const varName = toCamelCase(table.name)
      const existingRelation = relationships.has(table.name)

      if (!existingRelation) {
        lines.push(`export const ${varName}Relations = relations(${varName}, ({ many }) => ({`)
        for (const rel of incomingRels) {
          const relVarName = toCamelCase(rel.from)
          const pluralName = pluralize(rel.from)
          lines.push(`  ${toCamelCase(pluralName)}: many(${relVarName}),`)
        }
        lines.push('}))')
        lines.push('')
      }
    }
  }

  return lines.join('\n')
}

// ============================================================================
// Helper Functions
// ============================================================================

/**
 * Get Drizzle type from column definition
 */
function getDrizzleType(col: SchemaColumnDefinition): DrizzleColumnType {
  const pgType = col.pgType.toLowerCase()

  // Direct mapping
  const mapping = PG_TO_DRIZZLE_MAP[pgType]
  if (mapping) {
    return mapping
  }

  // Handle varchar with length
  if (pgType.startsWith('character varying') || pgType.startsWith('varchar')) {
    return PG_TO_DRIZZLE_MAP['varchar']!
  }

  // Handle numeric with precision
  if (pgType.startsWith('numeric') || pgType.startsWith('decimal')) {
    return PG_TO_DRIZZLE_MAP['numeric']!
  }

  // Default to text
  return { drizzleType: 'text', import: 'text', tsType: 'string' }
}

/**
 * Convert string to camelCase
 */
function toCamelCase(str: string): string {
  return str
    .replace(/^([A-Z])/, (m) => m.toLowerCase())
    .replace(/[-_]([a-z])/g, (_, c) => c.toUpperCase())
}

/**
 * Convert table name based on options
 */
function convertTableName(name: string, options: SchemaGeneratorOptions): string {
  let result = name

  // Convert to snake_case for database
  result = result
    .replace(/([a-z])([A-Z])/g, '$1_$2')
    .toLowerCase()

  // Pluralize if not using singular names
  if (!options.singularTableNames) {
    result = pluralize(result)
  }

  return result
}

/**
 * Convert column name based on options
 */
function convertColumnName(name: string, options: SchemaGeneratorOptions): string {
  if (options.columnNaming === 'snake_case') {
    return name
      .replace(/([a-z])([A-Z])/g, '$1_$2')
      .toLowerCase()
  }
  return name
}

/**
 * Check if a column is an ID column
 */
function isIdColumn(name: string): boolean {
  const lower = name.toLowerCase()
  return lower === 'id' || lower === '_id'
}

/**
 * Check if a column should have an auto-generated value
 */
function isAutoGeneratedColumn(name: string, type: string): boolean {
  const lower = name.toLowerCase()

  // ID columns
  if (isIdColumn(name) && type === 'string') {
    return true
  }

  // Timestamp columns
  if (
    (lower.includes('created') || lower === 'created_at' || lower === 'createdat') &&
    (type === 'Date' || type === 'string')
  ) {
    return true
  }

  return false
}

/**
 * Get default value for auto-generated columns
 */
function getDefaultValue(name: string, type: string): string | undefined {
  const lower = name.toLowerCase()

  if (isIdColumn(name) && type === 'string') {
    return 'gen_random_uuid()'
  }

  if (
    (lower.includes('created') || lower === 'created_at' || lower === 'createdat') &&
    (type === 'Date' || type === 'string')
  ) {
    return 'now()'
  }

  return undefined
}

/**
 * Simple pluralization
 */
function pluralize(word: string): string {
  if (word.endsWith('y') && !['ay', 'ey', 'iy', 'oy', 'uy'].some((s) => word.endsWith(s))) {
    return word.slice(0, -1) + 'ies'
  }
  if (word.endsWith('s') || word.endsWith('x') || word.endsWith('ch') || word.endsWith('sh')) {
    return word + 'es'
  }
  return word + 's'
}

// ============================================================================
// High-Level API Functions
// ============================================================================

/**
 * Generate Drizzle schema from TypeScript source code
 *
 * @example
 * ```typescript
 * const source = `
 *   interface User {
 *     id: string;
 *     email: string;
 *     createdAt: Date;
 *     metadata: Record<string, unknown>;
 *   }
 * `
 * const schema = generateSchemaFromTypeScript(source)
 * console.log(schema)
 * ```
 */
export function generateSchemaFromTypeScript(
  source: string,
  options: SchemaGeneratorOptions = {}
): string {
  const interfaces = parseTypeScriptInterfaces(source)
  const tables = convertInterfacesToTables(interfaces, options)

  return generateDrizzleSchema(
    {
      tables,
      enums: [],
      schemaName: options.schema ?? 'public',
    },
    options
  )
}

/**
 * Generate Drizzle schema from database introspection
 *
 * @example
 * ```typescript
 * import postgres from 'postgres.do'
 * import { generateSchemaFromDatabase } from 'postgres.do/drizzle'
 *
 * const sql = postgres('postgres://...')
 * const schema = await generateSchemaFromDatabase(sql)
 * console.log(schema)
 * ```
 */
export async function generateSchemaFromDatabase(
  client: Sql,
  options: SchemaGeneratorOptions = {}
): Promise<string> {
  const schema = await introspectDatabase(client, options)
  return generateDrizzleSchema(schema, options)
}

/**
 * Generate TypeScript types alongside Drizzle schema
 *
 * @example
 * ```typescript
 * const { schema, types } = await generateSchemaWithTypes(sql)
 * ```
 */
export async function generateSchemaWithTypes(
  client: Sql,
  options: SchemaGeneratorOptions = {}
): Promise<{ schema: string; types: string }> {
  const dbSchema = await introspectDatabase(client, options)
  const schemaCode = generateDrizzleSchema(dbSchema, options)
  const typesCode = generateTypeScriptTypes(dbSchema)

  return { schema: schemaCode, types: typesCode }
}

/**
 * Generate TypeScript types from database schema
 */
function generateTypeScriptTypes(schema: DatabaseSchemaDefinition): string {
  const lines: string[] = []

  lines.push('/**')
  lines.push(' * Auto-generated TypeScript types')
  lines.push(` * Generated at: ${new Date().toISOString()}`)
  lines.push(' */')
  lines.push('')

  // Generate enum types
  for (const enumDef of schema.enums) {
    const typeName = toPascalCase(enumDef.name)
    const values = enumDef.values.map((v) => `'${v}'`).join(' | ')
    lines.push(`export type ${typeName} = ${values}`)
    lines.push('')
  }

  // Generate table types
  for (const table of schema.tables) {
    const typeName = toPascalCase(table.name)
    lines.push(`export interface ${typeName} {`)

    for (const col of table.columns) {
      const propName = toCamelCase(col.name)
      const drizzleType = getDrizzleType(col)
      const nullable = col.nullable ? ' | null' : ''
      lines.push(`  ${propName}: ${drizzleType.tsType}${nullable}`)
    }

    lines.push('}')
    lines.push('')

    // Generate insert type
    lines.push(`export interface ${typeName}Insert {`)

    for (const col of table.columns) {
      const propName = toCamelCase(col.name)
      const drizzleType = getDrizzleType(col)
      const optional = col.hasDefault || col.nullable ? '?' : ''
      const nullable = col.nullable ? ' | null' : ''
      lines.push(`  ${propName}${optional}: ${drizzleType.tsType}${nullable}`)
    }

    lines.push('}')
    lines.push('')
  }

  return lines.join('\n')
}

/**
 * Convert string to PascalCase
 */
function toPascalCase(str: string): string {
  return str
    .replace(/[-_]([a-z])/g, (_, c) => c.toUpperCase())
    .replace(/^([a-z])/, (m) => m.toUpperCase())
}