#!/usr/bin/env tsx
import { existsSync, mkdirSync, readFileSync, readdirSync, writeFileSync } from 'fs';
import { dirname, join, resolve } from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

interface Column {
  name: string;
  type: string;
  nullable: boolean;
  defaultValue?: string;
}

interface Table {
  name: string;
  columns: Column[];
}

interface CompositeType {
  name: string;
  fields: { name: string; type: string }[];
}

function toPascalCase(str: string): string {
  return str
    .split('_')
    .map(s => s.charAt(0).toUpperCase() + s.slice(1))
    .join('');
}

const customTypes = new Set<string>();

function parsePostgresType(pgType: string): { zod: string; import?: string } {
  const typeMap: Record<string, string> = {
    text: 'z.string()',
    'text,': 'z.string()',
    varchar: 'z.string()',
    uuid: 'z.string().uuid()',
    integer: 'z.number().int()',
    int: 'z.number().int()',
    bigint: 'z.number().int()',
    boolean: 'z.boolean()',
    timestamp: 'z.string().datetime()',
    'timestamp with time zone': 'z.string().datetime()',
    timestamptz: 'z.string().datetime()',
    jsonb: 'z.record(z.string(), z.any())',
    json: 'z.record(z.string(), z.any())',
    tsvector: 'z.string().optional()',
    'tsvector,': 'z.string().optional()',
  };

  const cleanType = pgType.replace(/,$/, '').toLowerCase();
  if (typeMap[cleanType]) return { zod: typeMap[cleanType] };
  if (customTypes.has(cleanType)) {
    const schemaName = `${toPascalCase(cleanType)}Schema`;
    return { zod: schemaName, import: cleanType };
  }
  return { zod: 'z.unknown()' };
}

function parseSqlMigrations(): Table[] {
  const migrationsDir = resolve(__dirname, '../../../database/supabase/migrations');
  const tables: Table[] = [];

  try {
    const files = readdirSync(migrationsDir).filter(f => f.endsWith('.sql'));

    for (const file of files) {
      const content = readFileSync(join(migrationsDir, file), 'utf-8');

      const tablePattern = /CREATE TABLE(?:\s+IF NOT EXISTS)?\s+(\w+)\s*\(/gi;
      let match;

      while ((match = tablePattern.exec(content)) !== null) {
        const tableName = match[1];
        const startPos = match.index + match[0].length;

        let parenCount = 1;
        let pos = startPos;
        let endPos = -1;

        while (pos < content.length && parenCount > 0) {
          if (content[pos] === '(') parenCount++;
          else if (content[pos] === ')') parenCount--;

          if (parenCount === 0) {
            endPos = pos;
            break;
          }
          pos++;
        }

        if (endPos === -1) continue;

        const columnsText = content.substring(startPos, endPos);

        const columns: Column[] = [];
        const lines = columnsText
          .split('\n')
          .map(l => l.trim())
          .filter(l => l);

        for (const line of lines) {
          if (
            line.includes('CONSTRAINT') ||
            line.includes('INDEX') ||
            line.includes('FOREIGN KEY') ||
            line.includes('TRIGGER') ||
            line.includes('FUNCTION') ||
            line.startsWith('--') ||
            line.trim() === '' ||
            line.includes('ALTER TABLE') ||
            line.includes('CREATE POLICY') ||
            line.includes('ENABLE ROW LEVEL')
          ) {
            continue;
          }

          if (line.includes('PRIMARY KEY')) {
            const pkMatch = line.match(/^(\w+)\s+([^,\s]+)(?:\s+.*PRIMARY KEY.*)?/);
            if (pkMatch) {
              const [, name, type] = pkMatch;
              columns.push({
                name,
                type,
                nullable: false,
                defaultValue: 'has_default',
              });
            }
            continue;
          }

          if (line.includes('CHECK')) {
            const checkMatch = line.match(/^(\w+)\s+([^,\s]+)(?:\s+[^C]*)?CHECK/);
            if (checkMatch) {
              const [, name, type] = checkMatch;
              const hasDefault = line.includes('DEFAULT');
              const notNull = line.includes('NOT NULL');

              columns.push({
                name,
                type,
                nullable: !notNull,
                defaultValue: hasDefault ? 'has_default' : undefined,
              });
            }
            continue;
          }

          const columnMatch = line.match(/^(\w+)\s+(.+)/);
          if (columnMatch) {
            const [, name, fullDefinition] = columnMatch;

            let baseType = '';
            let modifiers = '';

            if (fullDefinition.toLowerCase().startsWith('timestamp with time zone')) {
              baseType = 'timestamp with time zone';
              modifiers = fullDefinition.substring(23).trim();
            } else {
              const parts = fullDefinition.split(/\s+/);
              baseType = parts[0];
              modifiers = parts.slice(1).join(' ');
            }

            const isNotNull = modifiers.includes('NOT NULL');
            const hasDefault =
              modifiers.includes('DEFAULT') || baseType.includes('gen_random_uuid()');
            const nullable = !isNotNull;

            columns.push({
              name,
              type: baseType.trim(),
              nullable,
              defaultValue: hasDefault ? 'has_default' : undefined,
            });
          }
        }

        if (columns.length > 0) {
          tables.push({ name: tableName, columns });
        }
      }
    }
  } catch (error) {
    console.error('Error reading migrations:', error);
    return [];
  }

  return tables;
}

function parseCompositeTypes(): CompositeType[] {
  const migrationsDir = resolve(__dirname, '../../../database/supabase/migrations');
  const types: CompositeType[] = [];

  try {
    const files = readdirSync(migrationsDir).filter(f => f.endsWith('.sql'));

    for (const file of files) {
      const content = readFileSync(join(migrationsDir, file), 'utf-8');
      const typePattern = /CREATE TYPE\s+(\w+)\s+AS\s*\(/gi;
      let match;

      while ((match = typePattern.exec(content)) !== null) {
        const typeName = match[1];
        const startPos = match.index + match[0].length;

        let parenCount = 1;
        let pos = startPos;
        let endPos = -1;

        while (pos < content.length && parenCount > 0) {
          if (content[pos] === '(') parenCount++;
          else if (content[pos] === ')') parenCount--;
          if (parenCount === 0) {
            endPos = pos;
            break;
          }
          pos++;
        }

        if (endPos === -1) continue;

        const fieldsText = content.substring(startPos, endPos);
        const fields: { name: string; type: string }[] = [];

        for (const line of fieldsText.split('\n')) {
          const trimmed = line.trim().replace(/,$/, '');
          if (!trimmed || trimmed.startsWith('--')) continue;
          const fieldMatch = trimmed.match(/^(\w+)\s+(\w+)/);
          if (fieldMatch) {
            fields.push({ name: fieldMatch[1], type: fieldMatch[2] });
          }
        }

        if (fields.length > 0) {
          types.push({ name: typeName, fields });
        }
      }
    }
  } catch (error) {
    console.error('Error parsing composite types:', error);
  }

  return types;
}

function generateTypeZodSchema(type: CompositeType): string {
  const schemaName = `${toPascalCase(type.name)}Schema`;
  const typeName = toPascalCase(type.name);

  const fields = type.fields.map(f => `  ${f.name}: z.number().nullable().optional()`).join(',\n');

  return `export const ${schemaName} = z.object({
${fields}
});

export type ${typeName} = z.infer<typeof ${schemaName}>;
`;
}

function generateZodSchema(table: Table): string {
  const tableNameToSingular: Record<string, string> = {
    topics: 'topic',
    posts: 'post',
    recipes: 'recipe',
    profiles: 'profile',
    unesco_petition_signatures: 'unescoPetitionSignature',
  };

  const singularName = tableNameToSingular[table.name] || table.name;
  const schemaName = `${toPascalCase(table.name)}Schema`;
  const typeName = toPascalCase(singularName);

  const imports = new Set<string>();
  const fields = table.columns
    .map(col => {
      const parsed = parsePostgresType(col.type);
      let zodType = parsed.zod;
      if (parsed.import) imports.add(parsed.import);

      if (col.nullable && !col.defaultValue) {
        zodType += '.nullable()';
      } else if (col.defaultValue) {
        zodType += '.optional()';
      }

      return `  ${col.name}: ${zodType}`;
    })
    .join(',\n');

  const importStatements = Array.from(imports)
    .map(t => `import { ${toPascalCase(t)}Schema } from './${t}';`)
    .join('\n');

  return `${importStatements ? importStatements + '\n' : ''}export const ${schemaName} = z.object({
${fields}
});

export type ${typeName} = z.infer<typeof ${schemaName}>;
`;
}

function main() {
  console.log('🔄 Translating SQL to Zod schemas...');

  const srcDir = resolve(__dirname, '../src');
  const srcSchemas = new Set(
    existsSync(srcDir)
      ? readdirSync(srcDir)
          .filter(f => f.endsWith('.ts'))
          .filter(f => readFileSync(join(srcDir, f), 'utf-8').includes('Schema'))
          .map(f => f.replace('.ts', ''))
      : []
  );

  const types = parseCompositeTypes().filter(t => !srcSchemas.has(t.name));
  types.forEach(t => customTypes.add(t.name));
  const tables = parseSqlMigrations();

  if (tables.length === 0 && types.length === 0) {
    console.log('⚠️  No tables or types found in migrations');
    return;
  }

  const fromDatabaseDir = resolve(__dirname, '../from-database');
  mkdirSync(fromDatabaseDir, { recursive: true });

  const srcImports = Array.from(srcSchemas).map(
    name => `export { ${toPascalCase(name)}Schema } from './${name}';`
  );
  const typeImports = types.map(
    t => `export { ${toPascalCase(t.name)}Schema } from './${t.name}';`
  );
  const tableImports = tables.map(
    t => `export { ${toPascalCase(t.name)}Schema } from './${t.name}';`
  );

  const indexContent = [...srcImports, ...typeImports, ...tableImports].join('\n') + '\n';
  writeFileSync(join(fromDatabaseDir, 'index.ts'), indexContent);

  for (const table of tables) {
    const schemaContent = `import { z } from 'zod';\n${generateZodSchema(table)}`;
    writeFileSync(join(fromDatabaseDir, `${table.name}.ts`), schemaContent);
  }

  for (const type of types) {
    const schemaContent = `import { z } from 'zod';\n${generateTypeZodSchema(type)}`;
    writeFileSync(join(fromDatabaseDir, `${type.name}.ts`), schemaContent);
  }

  console.log(`✅ Generated Zod schemas for ${tables.length} tables:`);
  tables.forEach(t => console.log(`   - ${t.name}`));
  if (types.length > 0) {
    console.log(`✅ Generated Zod schemas for ${types.length} types:`);
    types.forEach(t => console.log(`   - ${t.name}`));
  }
}

if (import.meta.url === `file://${process.argv[1]}`) {
  main();
}