import { Client } from 'pg';
import Database from 'better-sqlite3';
import { postgresConfig, sqliteConfig, migrationConfig, logConfig } from '../config';

interface TableInfo {
  tableName: string;
  columns: ColumnInfo[];
}

interface ColumnInfo {
  name: string;
  type: string;
  notNull: boolean;
  defaultValue: string | null;
  primaryKey: boolean;
}

/**
 * Maps SQLite data types back to PostgreSQL data types
 * This is a best-effort mapping and may need adjustment based on specific use cases
 */
function mapSqliteTypeToPostgres(sqliteType: string): string {
  const typeMap: Record<string, string> = {
    'INTEGER': 'INTEGER',
    'REAL': 'DOUBLE PRECISION',
    'TEXT': 'TEXT',
    'BLOB': 'BYTEA',
    'NUMERIC': 'NUMERIC',
  };
  
  return typeMap[sqliteType.toUpperCase()] || 'TEXT';
}

/**
 * Gets all table names from SQLite
 */
function getSqliteTables(db: Database.Database): string[] {
  const result = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type = 'table' 
    AND name NOT LIKE 'sqlite_%'
    ORDER BY name
  `).all() as { name: string }[];
  
  return result.map(row => row.name);
}

/**
 * Gets column information for a specific table from SQLite
 */
function getSqliteTableColumns(db: Database.Database, tableName: string): ColumnInfo[] {
  const result = db.prepare(`PRAGMA table_info("${tableName}")`).all() as any[];
  
  return result.map(row => ({
    name: row.name,
    type: row.type,
    notNull: row.notnull === 1,
    defaultValue: row.dflt_value,
    primaryKey: row.pk === 1,
  }));
}

/**
 * Creates PostgreSQL table based on SQLite schema
 */
async function createPostgresTable(
  pgClient: Client, 
  tableName: string, 
  columns: ColumnInfo[]
): Promise<void> {
  // Check if table exists
  const existsResult = await pgClient.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_schema = $1 
      AND table_name = $2
    )
  `, [postgresConfig.schema, tableName]);
  
  const tableExists = existsResult.rows[0].exists;
  
  if (tableExists && migrationConfig.truncateBeforeInsert) {
    // Drop existing table to recreate
    await pgClient.query(`DROP TABLE IF EXISTS "${postgresConfig.schema}"."${tableName}" CASCADE`);
    console.log(`  Dropped existing table: ${tableName}`);
  } else if (tableExists) {
    console.log(`  Table already exists, will append data: ${tableName}`);
    return;
  }
  
  const columnDefs = columns.map(col => {
    let def = `"${col.name}" ${mapSqliteTypeToPostgres(col.type)}`;
    if (col.notNull) {
      def += ' NOT NULL';
    }
    if (col.primaryKey) {
      def += ' PRIMARY KEY';
    }
    if (col.defaultValue !== null) {
      def += ` DEFAULT ${col.defaultValue}`;
    }
    return def;
  }).join(', ');
  
  const createSql = `CREATE TABLE "${postgresConfig.schema}"."${tableName}" (${columnDefs})`;
  
  if (logConfig.verbose) {
    console.log(`Creating table: ${tableName}`);
    console.log(createSql);
  }
  
  await pgClient.query(createSql);
}

/**
 * Attempts to parse JSON values that were stored as TEXT in SQLite
 */
function parseValue(value: any, columnName: string): any {
  if (value === null) return null;
  if (typeof value !== 'string') return value;
  
  // Try to parse as JSON (for arrays/objects that were stringified)
  if ((value.startsWith('[') && value.endsWith(']')) || 
      (value.startsWith('{') && value.endsWith('}'))) {
    try {
      return JSON.parse(value);
    } catch {
      return value;
    }
  }
  
  return value;
}

/**
 * Migrates data from SQLite to PostgreSQL
 */
async function migrateTableData(
  sqliteDb: Database.Database,
  pgClient: Client,
  tableName: string,
  columns: ColumnInfo[]
): Promise<number> {
  // Check if table should be excluded
  if (migrationConfig.excludedTables.includes(tableName)) {
    console.log(`Skipping excluded table: ${tableName}`);
    return 0;
  }
  
  // Check if only specific tables should be included
  if (migrationConfig.includedTables.length > 0 && !migrationConfig.includedTables.includes(tableName)) {
    console.log(`Skipping table not in include list: ${tableName}`);
    return 0;
  }
  
  console.log(`Migrating table: ${tableName}`);
  
  // Get total count
  const countResult = sqliteDb.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`).get() as { count: number };
  const totalRows = countResult.count;
  console.log(`  Total rows to migrate: ${totalRows}`);
  
  if (totalRows === 0) {
    console.log(`  No data to migrate`);
    return 0;
  }
  
  // Clear existing data if configured (and table wasn't just created)
  if (migrationConfig.truncateBeforeInsert) {
    await pgClient.query(`DELETE FROM "${postgresConfig.schema}"."${tableName}"`);
    console.log(`  Cleared existing data`);
  }
  
  // Get column names
  const columnNames = columns.map(c => `"${c.name}"`).join(', ');
  const placeholders = columns.map((_, i) => `$${i + 1}`).join(', ');
  
  let migratedCount = 0;
  let offset = 0;
  
  while (offset < totalRows) {
    const rows = sqliteDb.prepare(`
      SELECT * FROM "${tableName}" 
      ORDER BY ROWID 
      LIMIT ? OFFSET ?
    `).all(migrationConfig.batchSize, offset) as any[];
    
    for (const row of rows) {
      const values = columns.map(col => parseValue(row[col.name], col.name));
      
      await pgClient.query(
        `INSERT INTO "${postgresConfig.schema}"."${tableName}" (${columnNames}) VALUES (${placeholders})`,
        values
      );
    }
    
    migratedCount += rows.length;
    offset += migrationConfig.batchSize;
    
    if (logConfig.verbose || offset % 10000 === 0) {
      console.log(`  Progress: ${migratedCount}/${totalRows} rows`);
    }
  }
  
  console.log(`  Completed: ${migratedCount} rows migrated`);
  return migratedCount;
}

/**
 * Main migration function
 */
async function migrateSqliteToPostgres(): Promise<void> {
  console.log('Starting SQLite to PostgreSQL migration...\n');
  
  // Connect to SQLite
  const sqliteDb = new Database(sqliteConfig.filename);
  console.log('Connected to SQLite');
  
  // Connect to PostgreSQL
  const pgClient = new Client(postgresConfig);
  await pgClient.connect();
  console.log('Connected to PostgreSQL');
  
  try {
    // Get all tables
    const tables = getSqliteTables(sqliteDb);
    console.log(`\nFound ${tables.length} tables to migrate\n`);
    
    let totalMigrated = 0;
    
    // Migrate each table
    for (const tableName of tables) {
      try {
        const columns = getSqliteTableColumns(sqliteDb, tableName);
        
        // Create table in PostgreSQL
        await createPostgresTable(pgClient, tableName, columns);
        
        // Migrate data
        const migrated = await migrateTableData(sqliteDb, pgClient, tableName, columns);
        totalMigrated += migrated;
        
        console.log('');
      } catch (error) {
        console.error(`Error migrating table ${tableName}:`, error);
        if (logConfig.verbose) {
          throw error;
        }
      }
    }
    
    console.log('=================================');
    console.log('Migration completed successfully!');
    console.log(`Total rows migrated: ${totalMigrated}`);
    console.log(`Source: ${sqliteConfig.filename}`);
    console.log(`Target: PostgreSQL`);
    console.log('=================================');
    
  } catch (error) {
    console.error('Migration failed:', error);
    throw error;
  } finally {
    sqliteDb.close();
    await pgClient.end();
  }
}

// Run migration if called directly
if (require.main === module) {
  migrateSqliteToPostgres().catch(console.error);
}

export { migrateSqliteToPostgres };
