/**
 * Schema utilities for Google Cloud Spanner
 */
import { SpannerSchema, SpannerTable, getSpannerClient } from './types.js';

/**
 * Extracts schema information from Spanner database
 * 
 * @param instanceId Spanner instance ID
 * @param databaseId Spanner database ID
 * @returns Schema information for the database
 */
export async function getSpannerSchema(instanceId: string, databaseId: string): Promise<SpannerSchema> {
  const spanner = getSpannerClient();
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);
  
  // Query for tables
  const [tablesResult] = await database.run({
    sql: `SELECT t.table_name
          FROM information_schema.tables t
          WHERE t.table_catalog = '' AND t.table_schema = ''
          ORDER BY t.table_name`
  });
  
  if (!tablesResult || tablesResult.length === 0) {
    return { tables: [] };
  }
  
  const tables: SpannerTable[] = [];
  
  // Process each table to get columns
  for (const tableRow of tablesResult) {
    const tableName = (tableRow as any).table_name as string;
    
    // Get columns for this table
    const [columnsResult] = await database.run({
      sql: `SELECT column_name, spanner_type, is_nullable
            FROM information_schema.columns
            WHERE table_catalog = '' AND table_schema = '' AND table_name = @tableName
            ORDER BY ordinal_position`,
      params: { tableName }
    });
    
    const columns = columnsResult.map((col: any) => ({
      name: col.column_name as string,
      type: col.spanner_type as string,
      nullable: col.is_nullable === 'YES'
    }));
    
    // Get indexes for this table
    const [indexesResult] = await database.run({
      sql: `SELECT i.index_name, ic.column_name, i.is_unique
            FROM information_schema.indexes i
            JOIN information_schema.index_columns ic
              ON i.table_catalog = ic.table_catalog
              AND i.table_schema = ic.table_schema
              AND i.table_name = ic.table_name
              AND i.index_name = ic.index_name
            WHERE i.table_catalog = '' AND i.table_schema = '' AND i.table_name = @tableName
            ORDER BY i.index_name, ic.ordinal_position`,
      params: { tableName }
    });
    
    // Group index columns by index name
    const indexMap = new Map<string, { name: string; columns: string[]; unique: boolean }>();
    for (const idx of indexesResult) {
      const indexName = (idx as any).index_name as string;
      const columnName = (idx as any).column_name as string;
      const isUnique = (idx as any).is_unique as boolean;
      
      if (!indexMap.has(indexName)) {
        indexMap.set(indexName, { name: indexName, columns: [], unique: isUnique });
      }
      
      indexMap.get(indexName)?.columns.push(columnName);
    }
    
    const indexes = Array.from(indexMap.values());
    
    // Get foreign keys for this table (if available)
    let foreignKeys: { name: string; columns: string[]; referencedTable: string; referencedColumns: string[] }[] = [];
    
    try {
      // Note: This query might fail in some Spanner databases that don't have the referential_constraints view
      const [foreignKeysResult] = await database.run({
        sql: `SELECT
                rc.constraint_name,
                kcu1.column_name,
                kcu2.table_name AS referenced_table,
                kcu2.column_name AS referenced_column
              FROM information_schema.referential_constraints rc
              JOIN information_schema.key_column_usage kcu1
                ON kcu1.constraint_name = rc.constraint_name
              JOIN information_schema.key_column_usage kcu2
                ON kcu2.constraint_name = rc.unique_constraint_name
              WHERE kcu1.table_catalog = '' AND kcu1.table_schema = '' 
                AND kcu1.table_name = @tableName
              ORDER BY rc.constraint_name, kcu1.ordinal_position`,
        params: { tableName }
      });
      
      // Group foreign key columns by constraint name
      const fkMap = new Map<string, { name: string; columns: string[]; referencedTable: string; referencedColumns: string[] }>();
      for (const fk of foreignKeysResult) {
        const constraintName = (fk as any).constraint_name as string;
        const columnName = (fk as any).column_name as string;
        const referencedTable = (fk as any).referenced_table as string;
        const referencedColumn = (fk as any).referenced_column as string;
        
        if (!fkMap.has(constraintName)) {
          fkMap.set(constraintName, { 
            name: constraintName, 
            columns: [], 
            referencedTable, 
            referencedColumns: [] 
          });
        }
        
        const entry = fkMap.get(constraintName);
        if (entry) {
          entry.columns.push(columnName);
          entry.referencedColumns.push(referencedColumn);
        }
      }
      
      foreignKeys = Array.from(fkMap.values());
    } catch (error) {
      console.error(`Error fetching foreign keys for table ${tableName}:`, error);
      // Continue without foreign keys if the query fails
    }
    
    tables.push({
      name: tableName,
      columns,
      indexes: indexes.length > 0 ? indexes : undefined,
      foreignKeys: foreignKeys.length > 0 ? foreignKeys : undefined
    });
  }
  
  return { tables };
}

/**
 * Formats schema information as markdown
 * 
 * @param schema The schema to format
 * @returns A markdown string representation of the schema
 */
export function formatSchemaAsMarkdown(schema: SpannerSchema): string {
  if (schema.tables.length === 0) {
    return 'No tables found in the database.';
  }
  
  let markdown = '# Database Schema\n\n';
  
  for (const table of schema.tables) {
    markdown += `## Table: ${table.name}\n\n`;
    
    // Columns
    markdown += '### Columns\n\n';
    markdown += '| Column | Type | Nullable |\n';
    markdown += '|--------|------|----------|\n';
    
    for (const column of table.columns) {
      markdown += `| ${column.name} | ${column.type} | ${column.nullable ? 'YES' : 'NO'} |\n`;
    }
    
    markdown += '\n';
    
    // Indexes
    if (table.indexes && table.indexes.length > 0) {
      markdown += '### Indexes\n\n';
      markdown += '| Name | Columns | Unique |\n';
      markdown += '|------|---------|--------|\n';
      
      for (const index of table.indexes) {
        markdown += `| ${index.name} | ${index.columns.join(', ')} | ${index.unique ? 'YES' : 'NO'} |\n`;
      }
      
      markdown += '\n';
    }
    
    // Foreign Keys
    if (table.foreignKeys && table.foreignKeys.length > 0) {
      markdown += '### Foreign Keys\n\n';
      markdown += '| Name | Columns | Referenced Table | Referenced Columns |\n';
      markdown += '|------|---------|-----------------|-------------------|\n';
      
      for (const fk of table.foreignKeys) {
        markdown += `| ${fk.name} | ${fk.columns.join(', ')} | ${fk.referencedTable} | ${fk.referencedColumns.join(', ')} |\n`;
      }
      
      markdown += '\n';
    }
    
    markdown += '---\n\n';
  }
  
  return markdown;
}