package main

import (
	"fmt"
	"os"
	"strings"
	"time"

	"github.com/spf13/cobra"

	"git.warky.dev/wdevs/relspecgo/pkg/diff"
	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/readers"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/dbml"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/dctx"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/drawdb"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/json"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/pgsql"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/yaml"
)

var (
	sourceType   string
	sourcePath   string
	sourceConn   string
	targetType   string
	targetPath   string
	targetConn   string
	outputFormat string
	outputPath   string
)

var diffCmd = &cobra.Command{
	Use:   "diff",
	Short: "Compare two database schemas and report differences",
	Long: `Compare two database schemas from different sources and generate a
differences report.

The command reads schemas from two sources (source and target) and
analyzes differences including missing tables, schemas, columns,
indexes, constraints, and sequences.

Output formats:
  - summary: Human-readable summary to stdout (default)
  - json: Detailed JSON report
  - html: HTML report with visual formatting

PostgreSQL Connection String Examples:
  postgres://username:password@localhost:5432/database_name
  postgres://username:password@localhost/database_name
  postgresql://user:pass@host:5432/dbname?sslmode=disable
  postgresql://user:pass@host/dbname?sslmode=require
  host=localhost port=5432 user=username password=pass dbname=mydb sslmode=disable


Examples:
  # Compare two DBML files with summary output
  relspec diff --from dbml --from-path schema1.dbml \
               --to dbml --to-path schema2.dbml

  # Compare PostgreSQL database with DBML file, output as JSON
  relspec diff --from pgsql \
               --from-conn "postgres://myuser:mypass@localhost:5432/prod_db" \
               --to dbml --to-path schema.dbml \
               --format json --output diff.json

  # Compare two PostgreSQL databases, output as HTML
  relspec diff --from pgsql \
               --from-conn "postgres://user:pass@localhost:5432/db1" \
               --to pgsql \
               --to-conn "postgres://user:pass@localhost:5432/db2" \
               --format html --output report.html

  # Compare local and remote PostgreSQL databases
  relspec diff --from pgsql \
               --from-conn "postgresql://admin:secret@localhost/staging?sslmode=disable" \
               --to pgsql \
               --to-conn "postgresql://admin:secret@prod.example.com/production?sslmode=require" \
               --format summary

  # Compare DBML files with detailed JSON output
  relspec diff --from dbml --from-path v1.dbml \
               --to dbml --to-path v2.dbml \
               --format json --output changes.json`,
	RunE: runDiff,
}

func init() {
	diffCmd.Flags().StringVar(&sourceType, "from", "", "Source database format (dbml, dctx, drawdb, json, yaml, pgsql)")
	diffCmd.Flags().StringVar(&sourcePath, "from-path", "", "Source file path (for file-based formats)")
	diffCmd.Flags().StringVar(&sourceConn, "from-conn", "", "Source connection string (for database formats)")

	diffCmd.Flags().StringVar(&targetType, "to", "", "Target database format (dbml, dctx, drawdb, json, yaml, pgsql)")
	diffCmd.Flags().StringVar(&targetPath, "to-path", "", "Target file path (for file-based formats)")
	diffCmd.Flags().StringVar(&targetConn, "to-conn", "", "Target connection string (for database formats)")

	diffCmd.Flags().StringVar(&outputFormat, "format", "summary", "Output format (summary, json, html)")
	diffCmd.Flags().StringVar(&outputPath, "output", "", "Output file path (default: stdout for summary, required for json/html)")

	err := diffCmd.MarkFlagRequired("from")
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error marking from flag as required: %v\n", err)
	}
	err = diffCmd.MarkFlagRequired("to")
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error marking to flag as required: %v\n", err)
	}
}

func runDiff(cmd *cobra.Command, args []string) error {
	fmt.Fprintf(os.Stderr, "\n=== RelSpec Schema Diff ===\n")
	fmt.Fprintf(os.Stderr, "Started at: %s\n\n", time.Now().Format("2006-01-02 15:04:05"))

	// Read source database
	fmt.Fprintf(os.Stderr, "[1/3] Reading source schema...\n")
	fmt.Fprintf(os.Stderr, "      Format: %s\n", sourceType)
	if sourcePath != "" {
		fmt.Fprintf(os.Stderr, "      Path:   %s\n", sourcePath)
	}
	if sourceConn != "" {
		fmt.Fprintf(os.Stderr, "      Conn:   %s\n", maskPasswordInDiff(sourceConn))
	}

	sourceDB, err := readDatabase(sourceType, sourcePath, sourceConn, "source")
	if err != nil {
		return fmt.Errorf("failed to read source database: %w", err)
	}

	fmt.Fprintf(os.Stderr, "      ✓ Successfully read database '%s'\n", sourceDB.Name)
	sourceTables := 0
	for _, schema := range sourceDB.Schemas {
		sourceTables += len(schema.Tables)
	}
	fmt.Fprintf(os.Stderr, "      Found: %d schema(s), %d table(s)\n\n", len(sourceDB.Schemas), sourceTables)

	// Read target database
	fmt.Fprintf(os.Stderr, "[2/3] Reading target schema...\n")
	fmt.Fprintf(os.Stderr, "      Format: %s\n", targetType)
	if targetPath != "" {
		fmt.Fprintf(os.Stderr, "      Path:   %s\n", targetPath)
	}
	if targetConn != "" {
		fmt.Fprintf(os.Stderr, "      Conn:   %s\n", maskPasswordInDiff(targetConn))
	}

	targetDB, err := readDatabase(targetType, targetPath, targetConn, "target")
	if err != nil {
		return fmt.Errorf("failed to read target database: %w", err)
	}

	fmt.Fprintf(os.Stderr, "      ✓ Successfully read database '%s'\n", targetDB.Name)
	targetTables := 0
	for _, schema := range targetDB.Schemas {
		targetTables += len(schema.Tables)
	}
	fmt.Fprintf(os.Stderr, "      Found: %d schema(s), %d table(s)\n\n", len(targetDB.Schemas), targetTables)

	// Compare databases
	fmt.Fprintf(os.Stderr, "[3/3] Comparing schemas...\n")
	result := diff.CompareDatabases(sourceDB, targetDB)
	summary := diff.ComputeSummary(result)

	totalDiffs := summary.Schemas.Missing + summary.Schemas.Extra + summary.Schemas.Modified +
		summary.Tables.Missing + summary.Tables.Extra + summary.Tables.Modified +
		summary.Columns.Missing + summary.Columns.Extra + summary.Columns.Modified +
		summary.Indexes.Missing + summary.Indexes.Extra + summary.Indexes.Modified +
		summary.Constraints.Missing + summary.Constraints.Extra + summary.Constraints.Modified

	fmt.Fprintf(os.Stderr, "      ✓ Comparison complete\n")
	fmt.Fprintf(os.Stderr, "      Found: %d difference(s)\n\n", totalDiffs)

	// Determine output format
	var format diff.OutputFormat
	switch strings.ToLower(outputFormat) {
	case "summary":
		format = diff.FormatSummary
	case "json":
		format = diff.FormatJSON
	case "html":
		format = diff.FormatHTML
	default:
		return fmt.Errorf("unsupported output format: %s", outputFormat)
	}

	// Determine output writer
	var writer *os.File
	if outputPath == "" {
		if format != diff.FormatSummary {
			return fmt.Errorf("output path is required for %s format", outputFormat)
		}
		writer = os.Stdout
	} else {
		fmt.Fprintf(os.Stderr, "Writing %s report to: %s\n", outputFormat, outputPath)
		f, err := os.Create(outputPath)
		if err != nil {
			return fmt.Errorf("failed to create output file: %w", err)
		}
		defer f.Close()
		writer = f
	}

	// Format and write output
	if err := diff.FormatDiff(result, format, writer); err != nil {
		return fmt.Errorf("failed to format diff output: %w", err)
	}

	if outputPath != "" {
		fmt.Fprintf(os.Stderr, "✓ Report written successfully\n\n")
	}

	fmt.Fprintf(os.Stderr, "=== Diff Complete ===\n")
	fmt.Fprintf(os.Stderr, "Completed at: %s\n\n", time.Now().Format("2006-01-02 15:04:05"))

	return nil
}

func readDatabase(dbType, filePath, connString, label string) (*models.Database, error) {
	var reader readers.Reader

	switch strings.ToLower(dbType) {
	case "dbml":
		if filePath == "" {
			return nil, fmt.Errorf("%s: file path is required for DBML format", label)
		}
		reader = dbml.NewReader(&readers.ReaderOptions{FilePath: filePath})

	case "dctx":
		if filePath == "" {
			return nil, fmt.Errorf("%s: file path is required for DCTX format", label)
		}
		reader = dctx.NewReader(&readers.ReaderOptions{FilePath: filePath})

	case "drawdb":
		if filePath == "" {
			return nil, fmt.Errorf("%s: file path is required for DrawDB format", label)
		}
		reader = drawdb.NewReader(&readers.ReaderOptions{FilePath: filePath})

	case "json":
		if filePath == "" {
			return nil, fmt.Errorf("%s: file path is required for JSON format", label)
		}
		reader = json.NewReader(&readers.ReaderOptions{FilePath: filePath})

	case "yaml":
		if filePath == "" {
			return nil, fmt.Errorf("%s: file path is required for YAML format", label)
		}
		reader = yaml.NewReader(&readers.ReaderOptions{FilePath: filePath})

	case "pgsql", "postgres", "postgresql":
		if connString == "" {
			return nil, fmt.Errorf("%s: connection string is required for PostgreSQL format", label)
		}
		reader = pgsql.NewReader(&readers.ReaderOptions{ConnectionString: connString})

	default:
		return nil, fmt.Errorf("%s: unsupported database format: %s", label, dbType)
	}

	db, err := reader.ReadDatabase()
	if err != nil {
		return nil, fmt.Errorf("%s: failed to read database: %w", label, err)
	}

	return db, nil
}

func maskPasswordInDiff(connStr string) string {
	// Mask password in connection strings for security
	// Handle postgres://user:password@host format
	if strings.Contains(connStr, "://") && strings.Contains(connStr, "@") {
		parts := strings.Split(connStr, "@")
		if len(parts) >= 2 {
			userPart := parts[0]
			if strings.Contains(userPart, ":") {
				userPassParts := strings.Split(userPart, ":")
				if len(userPassParts) >= 3 {
					// postgres://user:pass -> postgres://user:***
					return userPassParts[0] + ":" + userPassParts[1] + ":***@" + strings.Join(parts[1:], "@")
				}
			}
		}
	}
	// Handle key=value format with password=
	if strings.Contains(connStr, "password=") {
		parts := strings.Split(connStr, " ")
		for i, part := range parts {
			if strings.HasPrefix(part, "password=") {
				parts[i] = "password=***"
			}
		}
		return strings.Join(parts, " ")
	}
	return connStr
}