# Database Schema Comparison PowerShell Script
# Compares current Django database schema with target Grazuri schema

$ErrorActionPreference = "Stop"

$targetSqlFile = "c:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_loans.sql"
$currentSqlFile = "c:\Users\Teamjoint company\Desktop\branchsystem\acbptxvs_branch_system.sql"
$outputFile = "c:\Users\Teamjoint company\Desktop\branchsystem\schema_comparison_report.txt"

Write-Host "=" * 80
Write-Host "DATABASE SCHEMA COMPARISON TOOL"
Write-Host "=" * 80
Write-Host ""

# Function to parse SQL file and extract table names
function Parse-SqlTables {
    param([string]$filePath)
    
    Write-Host "Parsing $filePath..."
    
    if (-not (Test-Path $filePath)) {
        Write-Host "ERROR: File not found: $filePath"
        return @{}
    }
    
    $content = Get-Content $filePath -Raw -Encoding UTF8
    
    # Find all CREATE TABLE statements
    $pattern = 'CREATE TABLE\s+`?(\w+)`?\s*\((.*?)\)\s*ENGINE'
    $matches = [regex]::Matches($content, $pattern, [System.Text.RegularExpressions.RegexOptions]::Singleline -bor [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
    
    $tables = @{}
    
    foreach ($match in $matches) {
        $tableName = $match.Groups[1].Value
        $tableDef = $match.Groups[2].Value
        
        $columns = @{}
        
        # Parse column definitions
        $lines = $tableDef -split "`n"
        foreach ($line in $lines) {
            $line = $line.Trim().TrimEnd(',')
            if ([string]::IsNullOrWhiteSpace($line)) { continue }
            
            # Skip constraints
            if ($line -match '^\s*(PRIMARY KEY|KEY|UNIQUE KEY|CONSTRAINT|FOREIGN KEY|INDEX)') {
                continue
            }
            
            # Parse column definition
            $parts = $line -split '\s+', 2
            if ($parts.Count -ge 2) {
                $colName = $parts[0].Trim('`')
                $colDef = $parts[1]
                
                # Extract column type
                if ($colDef -match '^(\w+(\([^)]+\))?)') {
                    $colType = $matches[1]
                    $columns[$colName] = $colType
                }
            }
        }
        
        $tables[$tableName] = $columns
    }
    
    Write-Host "Found $($tables.Count) tables"
    return $tables
}

# Parse both SQL files
Write-Host ""
$targetSchema = Parse-SqlTables -filePath $targetSqlFile
Write-Host ""
$currentSchema = Parse-SqlTables -filePath $currentSqlFile
Write-Host ""

if ($targetSchema.Count -eq 0 -or $currentSchema.Count -eq 0) {
    Write-Host "ERROR: Could not parse schema files"
    exit 1
}

# Compare schemas
Write-Host "Comparing schemas..."
Write-Host ""

$targetTables = $targetSchema.Keys | Sort-Object
$currentTables = $currentSchema.Keys | Sort-Object

$missingTables = $targetTables | Where-Object { $_ -notin $currentTables }
$extraTables = $currentTables | Where-Object { $_ -notin $targetTables }
$commonTables = $targetTables | Where-Object { $_ -in $currentTables }

$missingColumns = @{}
$extraColumns = @{}
$typesDifferent = @{}

foreach ($table in $commonTables) {
    $targetCols = $targetSchema[$table].Keys | Sort-Object
    $currentCols = $currentSchema[$table].Keys | Sort-Object
    
    $missing = $targetCols | Where-Object { $_ -notin $currentCols }
    $extra = $currentCols | Where-Object { $_ -notin $targetCols }
    
    if ($missing.Count -gt 0) {
        $missingColumns[$table] = $missing
    }
    
    if ($extra.Count -gt 0) {
        $extraColumns[$table] = $extra
    }
    
    # Check type differences for common columns
    $commonCols = $targetCols | Where-Object { $_ -in $currentCols }
    $typeDiffs = @()
    
    foreach ($col in $commonCols) {
        $targetType = $targetSchema[$table][$col]
        $currentType = $currentSchema[$table][$col]
        
        # Normalize types for comparison
        $targetTypeNorm = $targetType -replace '\(.*?\)', ''
        $currentTypeNorm = $currentType -replace '\(.*?\)', ''
        
        if ($targetTypeNorm -ne $currentTypeNorm) {
            $typeDiffs += @{
                column = $col
                current = $currentType
                target = $targetType
            }
        }
    }
    
    if ($typeDiffs.Count -gt 0) {
        $typesDifferent[$table] = $typeDiffs
    }
}

# Generate report
$report = @()
$report += "=" * 80
$report += "DATABASE SCHEMA COMPARISON REPORT"
$report += "=" * 80
$report += ""
$report += "Current Database: acbptxvs_branch_system (Django)"
$report += "Target Schema: xygbfpsg_loans.sql (Grazuri)"
$report += "Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
$report += ""

# Summary
$report += "=" * 80
$report += "SUMMARY"
$report += "=" * 80
$report += ""
$report += "Missing Tables in Current Schema: $($missingTables.Count)"
$report += "Extra Tables in Current Schema: $($extraTables.Count)"
$report += "Tables with Missing Columns: $($missingColumns.Count)"
$report += "Tables with Extra Columns: $($extraColumns.Count)"
$report += "Tables with Type Differences: $($typesDifferent.Count)"
$report += ""

# Missing Tables
if ($missingTables.Count -gt 0) {
    $report += "=" * 80
    $report += "MISSING TABLES IN CURRENT SCHEMA"
    $report += "=" * 80
    $report += ""
    $report += "The following tables exist in the target Grazuri schema but are missing"
    $report += "in the current Django database:"
    $report += ""
    $i = 1
    foreach ($table in $missingTables) {
        $colCount = $targetSchema[$table].Count
        $report += "$i. $table ($colCount columns)"
        $i++
    }
    $report += ""
}

# Extra Tables
if ($extraTables.Count -gt 0) {
    $report += "=" * 80
    $report += "EXTRA TABLES IN CURRENT SCHEMA"
    $report += "=" * 80
    $report += ""
    $report += "The following tables exist in the current Django database but are not"
    $report += "in the target Grazuri schema (these are Django-specific tables):"
    $report += ""
    $i = 1
    foreach ($table in $extraTables) {
        $report += "$i. $table"
        $i++
    }
    $report += ""
}

# Missing Columns
if ($missingColumns.Count -gt 0) {
    $report += "=" * 80
    $report += "MISSING COLUMNS IN EXISTING TABLES"
    $report += "=" * 80
    $report += ""
    $report += "These columns exist in the target schema but are missing in the current"
    $report += "database. Django migrations will need to add these columns."
    $report += ""
    foreach ($table in ($missingColumns.Keys | Sort-Object)) {
        $columns = $missingColumns[$table]
        $report += "Table: $table"
        $report += "Missing Columns ($($columns.Count)):"
        foreach ($col in $columns) {
            $colType = $targetSchema[$table][$col]
            $report += "  - $col : $colType"
        }
        $report += ""
    }
}

# Extra Columns
if ($extraColumns.Count -gt 0) {
    $report += "=" * 80
    $report += "EXTRA COLUMNS IN EXISTING TABLES"
    $report += "=" * 80
    $report += ""
    $report += "These columns exist in the current database but not in the target schema."
    $report += "These are likely Django-specific fields and should be preserved."
    $report += ""
    foreach ($table in ($extraColumns.Keys | Sort-Object)) {
        $columns = $extraColumns[$table]
        $report += "Table: $table"
        $report += "Extra Columns ($($columns.Count)):"
        foreach ($col in $columns) {
            $report += "  - $col"
        }
        $report += ""
    }
}

# Type Differences
if ($typesDifferent.Count -gt 0) {
    $report += "=" * 80
    $report += "DATA TYPE DIFFERENCES"
    $report += "=" * 80
    $report += ""
    $report += "These columns have different data types between current and target schemas."
    $report += ""
    foreach ($table in ($typesDifferent.Keys | Sort-Object)) {
        $diffs = $typesDifferent[$table]
        $report += "Table: $table"
        $report += "Type Differences ($($diffs.Count)):"
        foreach ($diff in $diffs) {
            $report += "  Column: $($diff.column)"
            $report += "    Current: $($diff.current)"
            $report += "    Target:  $($diff.target)"
        }
        $report += ""
    }
}

# Recommendations
$report += "=" * 80
$report += "RECOMMENDATIONS"
$report += "=" * 80
$report += ""
$report += "1. CREATE DJANGO MODELS FOR MISSING TABLES:"
$report += "   - Create models for the following tables:"
$count = 0
foreach ($table in $missingTables) {
    if ($count -lt 10) {
        $report += "     * $table"
        $count++
    }
}
if ($missingTables.Count -gt 10) {
    $report += "     ... and $($missingTables.Count - 10) more"
}
$report += ""

$report += "2. ADD MISSING COLUMNS TO EXISTING MODELS:"
$report += "   - Update Django models to include missing columns"
$report += "   - Generate migrations using: python manage.py makemigrations"
$report += ""

$report += "3. REVIEW DATA TYPE DIFFERENCES:"
$report += "   - Ensure Django field types match target schema requirements"
$report += "   - Consider data migration if types are incompatible"
$report += ""

$report += "4. PRESERVE DJANGO-SPECIFIC TABLES:"
$report += "   - Keep Django admin, auth, and session tables"
$report += "   - These are required for Django functionality"
$report += ""

$report += "5. TEST MIGRATIONS:"
$report += "   - Test all migrations in development environment first"
$report += "   - Verify data integrity after applying migrations"
$report += "   - Create database backup before production deployment"
$report += ""

# Write report to file
$report | Out-File -FilePath $outputFile -Encoding UTF8

Write-Host "Report saved to: $outputFile"
Write-Host ""

# Print summary to console
Write-Host "=" * 80
Write-Host "COMPARISON SUMMARY"
Write-Host "=" * 80
Write-Host "Missing Tables: $($missingTables.Count)"
Write-Host "Extra Tables: $($extraTables.Count)"
Write-Host "Tables with Missing Columns: $($missingColumns.Count)"
Write-Host "Tables with Extra Columns: $($extraColumns.Count)"
Write-Host "Tables with Type Differences: $($typesDifferent.Count)"
Write-Host ""

Write-Host "Schema comparison complete!"
Write-Host "Review the detailed report at: $outputFile"
