path: root/lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts

import fs from 'fs';
import path from 'path';

interface TableColumn {
  owner: string;
  tableName: string;
  columnName: string;
  dataType: string;
  dataLength: number;
  nullable: string;
  tableComments: string;
  columnComments: string;
  constraints: string;
}

interface TableSchema {
  tableName: string;
  tableComments: string;
  columns: TableColumn[];
}

// 컬러 로그 함수들
const log = {
  info: (msg: string) => console.log(`\x1b[34mℹ\x1b[0m ${msg}`),
  success: (msg: string) => console.log(`\x1b[32m✓\x1b[0m ${msg}`),
  warning: (msg: string) => console.log(`\x1b[33m⚠\x1b[0m ${msg}`),
  error: (msg: string) => console.log(`\x1b[31m✗\x1b[0m ${msg}`),
};

// Oracle -> PostgreSQL 타입 매핑 (nonsap용)
function mapOracleTypeToPostgres(oracleType: string, dataLength: number): string {
  const type = oracleType.toUpperCase();
  
  switch (type) {
    case 'VARCHAR2':
      if (dataLength <= 255) {
        return `varchar({ length: ${dataLength} })`;
      } else {
        return 'text()';
      }
    case 'NUMBER':
      return 'numeric()';
    case 'DATE':
      return 'timestamp()';
    case 'CHAR':
      return `char({ length: ${dataLength} })`;
    case 'CLOB':
      return 'text()';
    case 'BLOB':
      return 'bytea()';
    case 'INTEGER':
      return 'integer()';
    case 'DECIMAL':
      return 'decimal()';
    case 'FLOAT':
      return 'real()';
    case 'DOUBLE':
      return 'doublePrecision()';
    default:
      log.warning(`Unknown Oracle type: ${type}, defaulting to text()`);
      return 'text()';
  }
}

// 테이블명을 camelCase로 변환
function toCamelCase(str: string): string {
  return str.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
}

// 컬럼명을 camelCase로 변환
function toColumnName(str: string): string {
  return str.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
}

// CSV 파싱 함수
function parseCSV(csvContent: string): TableColumn[] {
  const lines = csvContent.split('\n');
  const data: TableColumn[] = [];

  for (let i = 1; i < lines.length; i++) {
    const line = lines[i].trim();
    if (!line) continue;

    const values = line.split(',');
    if (values.length < 9) continue;

    data.push({
      owner: values[0],
      tableName: values[1],
      columnName: values[2],
      dataType: values[3],
      dataLength: parseInt(values[4]) || 0,
      nullable: values[5],
      tableComments: values[6],
      columnComments: values[7],
      constraints: values[8] || '',
    });
  }

  return data;
}

// 테이블별로 그룹화
function groupByTable(columns: TableColumn[]): TableSchema[] {
  const tables = new Map<string, TableSchema>();

  for (const column of columns) {
    if (!tables.has(column.tableName)) {
      tables.set(column.tableName, {
        tableName: column.tableName,
        tableComments: column.tableComments,
        columns: [],
      });
    }
    tables.get(column.tableName)!.columns.push(column);
  }

  return Array.from(tables.values());
}

// 제약조건 파싱 및 처리
function parseConstraints(constraints: string) {
  const result = {
    isPrimaryKey: false,
    isUnique: false,
    hasCheck: false,
    hasForeignKey: false,
  };

  if (!constraints) return result;

  const constraintUpper = constraints.toUpperCase();
  result.isPrimaryKey = constraintUpper.includes('PK');
  result.isUnique = constraintUpper.includes('UNIQUE');
  result.hasCheck = constraintUpper.includes('CHECK');
  result.hasForeignKey = constraintUpper.includes('FK') || constraintUpper.includes('FOREIGN');

  return result;
}

// Drizzle 스키마 생성
function generateDrizzleSchema(tables: TableSchema[]): string {
  let schema = `import { pgSchema, varchar, text, numeric, timestamp, char, bytea, integer, decimal, real, doublePrecision, primaryKey } from 'drizzle-orm/pg-core';

// NonSAP Oracle 데이터베이스 스키마
export const nonsapSchema = pgSchema('nonsap');

`;

  // Primary key 정보를 추적하기 위한 Map
  const primaryKeys = new Map<string, string[]>();

  // 각 테이블에 대한 스키마 생성
  for (const table of tables) {
    const tableName = toCamelCase(table.tableName);
    const pkColumns: string[] = [];

    // Primary key 컬럼 찾기
    for (const column of table.columns) {
      const constraintInfo = parseConstraints(column.constraints);
      if (constraintInfo.isPrimaryKey) {
        pkColumns.push(column.columnName); // 원본 컬럼명 그대로 사용
      }
    }
    
    if (pkColumns.length > 0) {
      primaryKeys.set(tableName, pkColumns);
    }

    // 테이블 코멘트 추가
    if (table.tableComments) {
      schema += `// ${table.tableComments}\n`;
    }
    
    schema += `export const ${tableName} = nonsapSchema.table('${table.tableName.toLowerCase()}', {\n`;

    table.columns.forEach((column) => {
      const columnName = column.columnName; // 원본 컬럼명 그대로 사용
      const postgresType = mapOracleTypeToPostgres(column.dataType, column.dataLength);
      const nullable = column.nullable === 'Y' ? '' : '.notNull()';
      const constraintInfo = parseConstraints(column.constraints);
      
      // 컬럼 코멘트 추가
      if (column.columnComments) {
        schema += `  // ${column.columnComments}\n`;
      }
      
      let columnDef = `  ${columnName}: ${postgresType}${nullable}`;
      
      // Primary key는 별도로 처리되므로 여기서는 제외
      if (constraintInfo.isUnique && !constraintInfo.isPrimaryKey) {
        columnDef += '.unique()';
      }
      
      columnDef += ',\n';
      schema += columnDef;
    });

    schema += '}';

    // Primary key 추가
    if (pkColumns.length > 0) {
      if (pkColumns.length === 1) {
        schema += `, (table) => ({\n  pk: primaryKey({ columns: [table.${pkColumns[0]}] })\n})`;
      } else {
        const pkColumnsStr = pkColumns.map(col => `table.${col}`).join(', ');
        schema += `, (table) => ({\n  pk: primaryKey({ columns: [${pkColumnsStr}] })\n})`;
      }
    }

    schema += ');\n\n';
  }

  // 타입 export 추가
  schema += `// 타입 정의\n`;
  for (const table of tables) {
    const tableName = toCamelCase(table.tableName);
    const typeName = tableName.charAt(0).toUpperCase() + tableName.slice(1);
    schema += `export type ${typeName} = typeof ${tableName}.$inferSelect;\n`;
    schema += `export type New${typeName} = typeof ${tableName}.$inferInsert;\n`;
  }

  return schema;
}

async function main() {
  try {
    log.info('Starting NonSAP Oracle to PostgreSQL Drizzle schema generation...');
    
    // CSV 파일 읽기
    const csvPath = path.join(__dirname, '1.table-and-columns-info.csv');
    const csvContent = fs.readFileSync(csvPath, 'utf-8');
    
    log.info('Parsing CSV data...');
    const columns = parseCSV(csvContent);
    
    if (columns.length === 0) {
      throw new Error('No data found in CSV file');
    }
    
    log.info(`Parsed ${columns.length} column definitions`);
    
    // 테이블별로 그룹화
    const tables = groupByTable(columns);
    
    log.info(`Found ${tables.length} tables`);
    
    // Drizzle 스키마 생성
    log.info('Generating Drizzle schema...');
    const schemaCode = generateDrizzleSchema(tables);
    
    // 파일 저장
    const outputPath = path.join(__dirname, 'schema.ts');
    fs.writeFileSync(outputPath, schemaCode, 'utf-8');
    
    log.success(`Generated Drizzle schema: ${outputPath}`);
    log.success(`Generated ${tables.length} table schemas with ${columns.length} total columns`);
    
  } catch (error: unknown) {
    if (error instanceof Error) {
      log.error(`Failed to generate schema: ${error.message}`);
    } else {
      log.error('Failed to generate schema: Unknown error');
    }
    process.exit(1);
  }
}

if (require.main === module) {
  main();
}

export { main };