import { Client, types as pgTypes } from 'pg';
import Database from 'better-sqlite3';
import { postgresConfig, sqliteConfig, migrationConfig, logConfig } from '../config';
import * as fs from 'fs';
import * as path from 'path';

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

interface ColumnInfo {
  name: string;
  type: string;
  isNullable: boolean;
  defaultValue: string | null;
  isPrimaryKey: boolean;
}

const KOLKATA_OFFSET_MINUTES = 330

const pad = (value: number, size: number = 2) => String(value).padStart(size, '0')

function formatDatePartsInKolkata(
  year: number,
  month: number,
  day: number,
  hour: number,
  minute: number,
  second: number,
  millisecond: number
): string {
  return `${year}-${pad(month)}-${pad(day)}T${pad(hour)}:${pad(minute)}:${pad(second)}.${pad(millisecond, 3)}Z`
}

function formatDateInKolkata(date: Date): string {
  const shifted = new Date(date.getTime() + KOLKATA_OFFSET_MINUTES * 60 * 1000)
  return formatDatePartsInKolkata(
    shifted.getUTCFullYear(),
    shifted.getUTCMonth() + 1,
    shifted.getUTCDate(),
    shifted.getUTCHours(),
    shifted.getUTCMinutes(),
    shifted.getUTCSeconds(),
    shifted.getUTCMilliseconds()
  )
}

function formatStringDateInKolkata(value: string): string | null {
  const trimmed = value.trim()
  if (!trimmed) return null

  const hasTimezone = /[Zz]|[+-]\d{2}:?\d{2}$/.test(trimmed)
  if (hasTimezone) {
    const parsed = new Date(trimmed)
    return Number.isNaN(parsed.getTime()) ? null : formatDateInKolkata(parsed)
  }

  const match = trimmed.match(
    /^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2})(?:\.(\d{1,6}))?)?)?$/
  )
  if (!match) return null

  const year = Number(match[1])
  const month = Number(match[2])
  const day = Number(match[3])
  const hour = match[4] ? Number(match[4]) : 0
  const minute = match[5] ? Number(match[5]) : 0
  const second = match[6] ? Number(match[6]) : 0
  const msRaw = match[7] ? match[7].slice(0, 3).padEnd(3, '0') : '000'
  const millisecond = Number(msRaw)

  if (
    Number.isNaN(year) ||
    Number.isNaN(month) ||
    Number.isNaN(day) ||
    Number.isNaN(hour) ||
    Number.isNaN(minute) ||
    Number.isNaN(second) ||
    Number.isNaN(millisecond)
  ) {
    return null
  }

  return formatDatePartsInKolkata(year, month, day, hour, minute, second, millisecond)
}

/**
 * Maps PostgreSQL data types to SQLite data types
 */
function mapPostgresTypeToSqlite(pgType: string): string {
  const typeMap: Record<string, string> = {
    'bigint': 'INTEGER',
    'bigserial': 'INTEGER',
    'boolean': 'INTEGER',
    'character': 'TEXT',
    'character varying': 'TEXT',
    'date': 'TEXT',
    'double precision': 'REAL',
    'integer': 'INTEGER',
    'json': 'TEXT',
    'jsonb': 'TEXT',
    'numeric': 'REAL',
    'real': 'REAL',
    'serial': 'INTEGER',
    'smallint': 'INTEGER',
    'text': 'TEXT',
    'timestamp with time zone': 'TEXT',
    'timestamp without time zone': 'TEXT',
    'uuid': 'TEXT',
    'ARRAY': 'TEXT', // Arrays stored as JSON text
  };
  
  // Check for array types
  if (pgType.endsWith('[]')) {
    return 'TEXT';
  }
  
  return typeMap[pgType] || 'TEXT';
}

/**
 * Gets all table names from PostgreSQL
 */
async function getPostgresTables(client: Client): Promise<string[]> {
  const result = await client.query(`
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = $1 
    AND table_type = 'BASE TABLE'
    ORDER BY table_name
  `, [postgresConfig.schema]);
  
  return result.rows.map(row => row.table_name);
}

/**
 * Gets column information for a specific table
 */
async function getTableColumns(client: Client, tableName: string): Promise<ColumnInfo[]> {
  const pkResult = await client.query(
    `
    SELECT kcu.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name
     AND tc.table_schema = kcu.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
      AND tc.table_schema = $1
      AND tc.table_name = $2
    ORDER BY kcu.ordinal_position
  `,
    [postgresConfig.schema, tableName]
  )

  const primaryKeys = new Set(pkResult.rows.map(row => row.column_name))

  const result = await client.query(
    `
    SELECT 
      column_name,
      data_type,
      is_nullable,
      column_default
    FROM information_schema.columns
    WHERE table_name = $1
    AND table_schema = $2
    ORDER BY ordinal_position
  `,
    [tableName, postgresConfig.schema]
  )
  
  return result.rows.map(row => ({
    name: row.column_name,
    type: row.data_type,
    isNullable: row.is_nullable === 'YES',
    defaultValue: row.column_default,
    isPrimaryKey: primaryKeys.has(row.column_name),
  }))
}

/**
 * Creates SQLite table based on PostgreSQL schema
 */
function createSqliteTable(db: Database.Database, tableName: string, columns: ColumnInfo[]): void {
  const primaryKeyColumns = columns.filter(col => col.isPrimaryKey).map(col => col.name)

  const columnDefs = columns.map(col => {
    const mappedType = mapPostgresTypeToSqlite(col.type)
    const isSinglePk = primaryKeyColumns.length === 1 && col.isPrimaryKey

    if (isSinglePk && mappedType === 'INTEGER') {
      return `"${col.name}" INTEGER PRIMARY KEY`
    }

    let def = `"${col.name}" ${mappedType}`
    if (!col.isNullable && !col.isPrimaryKey) {
      def += ' NOT NULL'
    }
    if (col.defaultValue !== null && !col.isPrimaryKey) {
      // Convert PostgreSQL default values to SQLite
      let defaultVal = col.defaultValue
      if (defaultVal.includes('nextval')) {
        // Skip auto-increment defaults, SQLite handles this with INTEGER PRIMARY KEY
      } else if (defaultVal === 'now()' || defaultVal.includes('CURRENT_TIMESTAMP')) {
        def += ` DEFAULT CURRENT_TIMESTAMP`
      } else {
        // Strip Postgres type casts (e.g. 'pending'::payment_status)
        defaultVal = defaultVal.replace(/::[\w\."]+/g, '')
        // Remove remaining type keywords from defaults (e.g. 'none' varying)
        defaultVal = defaultVal.replace(/\s+character\s+varying\b/gi, '')
        defaultVal = defaultVal.replace(/\s+varying\b/gi, '')
        defaultVal = defaultVal.trim()
        // Convert Postgres array literal defaults like '{}'[] to JSON array string
        if (defaultVal.includes('[]') && defaultVal.includes('{')) {
          defaultVal = "'[]'"
        }
        def += ` DEFAULT ${defaultVal}`
      }
    }
    return def
  })

  if (primaryKeyColumns.length > 1) {
    const pkDef = `PRIMARY KEY (${primaryKeyColumns.map(col => `"${col}"`).join(', ')})`
    columnDefs.push(pkDef)
  }
  
  const createSql = `CREATE TABLE IF NOT EXISTS "${tableName}" (${columnDefs.join(', ')})`;
  
  if (logConfig.verbose) {
    console.log(`Creating table: ${tableName}`);
    console.log(createSql);
  }
  
  db.exec(createSql);
}

/**
 * Migrates data from PostgreSQL to SQLite
 */
async function migrateTableData(
  pgClient: Client, 
  sqliteDb: Database.Database, 
  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 first
  const countResult = await pgClient.query(
    `SELECT COUNT(*) FROM "${postgresConfig.schema}"."${tableName}"`
  );
  const totalRows = parseInt(countResult.rows[0].count);
  console.log(`  Total rows to migrate: ${totalRows}`);
  
  if (totalRows === 0) {
    console.log(`  No data to migrate`);
    return 0;
  }
  
  // Clear existing data if configured
  if (migrationConfig.truncateBeforeInsert) {
    sqliteDb.exec(`DELETE FROM "${tableName}"`);
    console.log(`  Cleared existing data`);
  }
  
  // Get column names
  const columnNames = columns.map(c => `"${c.name}"`).join(', ');
  const placeholders = columns.map(() => '?').join(', ');
  const insertStmt = sqliteDb.prepare(`INSERT INTO "${tableName}" (${columnNames}) VALUES (${placeholders})`);
  
  let migratedCount = 0;
  let offset = 0;
  
  while (offset < totalRows) {
    const result = await pgClient.query(
      `SELECT * FROM "${postgresConfig.schema}"."${tableName}" ORDER BY 1 LIMIT $1 OFFSET $2`,
      [migrationConfig.batchSize, offset]
    );
    
    const insertMany = sqliteDb.transaction((rows) => {
      console.log(rows.at(-1))
      for (const row of rows) {
        const values = columns.map(col => {
          const val = row[col.name];
          // Handle special cases
          if (val === null || val === undefined) return null;
          if (typeof val === 'boolean') return val ? 1 : 0;
          if (val instanceof Date) {
            const formattedDate = formatDateInKolkata(val)
            console.log({formattedDate, id: row['id']})
            return formattedDate
          }
          if (
            typeof val === 'string' &&
            (col.type === 'date' || col.type.startsWith('timestamp'))
          ) {
            const formatted = formatStringDateInKolkata(val)
                        console.log({formatted, id: row['id']})

            if (formatted) return formatted
          }
          if (Array.isArray(val)) return JSON.stringify(val);
          if (typeof val === 'object') return JSON.stringify(val);
          // Ensure it's a primitive type SQLite can handle
          if (typeof val === 'number') return val;
          if (typeof val === 'bigint') return Number(val);
          return String(val);
        });
        insertStmt.run(values);
      }
    });
    
    insertMany(result.rows);
    migratedCount += result.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 migratePostgresToSqlite(): Promise<void> {
  console.log('Starting PostgreSQL to SQLite migration...\n');

  pgTypes.setTypeParser(1114, (value) => value)
  pgTypes.setTypeParser(1184, (value) => value)
  pgTypes.setTypeParser(1082, (value) => value)

  pgTypes.setTypeParser(1114, (value) => value)
  pgTypes.setTypeParser(1184, (value) => value)
  pgTypes.setTypeParser(1082, (value) => value)
  
  // Ensure SQLite directory exists
  const sqliteDir = path.dirname(sqliteConfig.filename);
  if (!fs.existsSync(sqliteDir)) {
    fs.mkdirSync(sqliteDir, { recursive: true });
  }
  
  // Remove existing SQLite file if starting fresh
  if (migrationConfig.truncateBeforeInsert && fs.existsSync(sqliteConfig.filename)) {
    fs.unlinkSync(sqliteConfig.filename);
    console.log('Removed existing SQLite database');
  }
  
  // Connect to PostgreSQL
  const pgClient = new Client(postgresConfig);
  await pgClient.connect();
  console.log('Connected to PostgreSQL');
  
  // Connect to SQLite
  const sqliteDb = new Database(sqliteConfig.filename);
  console.log('Connected to SQLite');
  
  // Enable foreign keys
  sqliteDb.exec('PRAGMA foreign_keys = ON');
  
  try {
    // Get all tables
    let tables = await getPostgresTables(pgClient);
    // tables = tables.filter(item => item === 'delivery_slot_info')
    console.log(`\nFound ${tables.length} tables to migrate\n`);
    
    let totalMigrated = 0;
    
    // Migrate each table
    for (const tableName of tables) {
      try {
        const columns = await getTableColumns(pgClient, tableName);
        
        // Create table in SQLite
        createSqliteTable(sqliteDb, tableName, columns);
        
        // Migrate data
        const migrated = await migrateTableData(pgClient, sqliteDb, 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(`SQLite database: ${sqliteConfig.filename}`);
    console.log('=================================');
    
  } catch (error) {
    console.error('Migration failed:', error);
    throw error;
  } finally {
    await pgClient.end();
    sqliteDb.close();
  }
}

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

export { migratePostgresToSqlite };
