package main

import (
	stdjson "encoding/json"
	"fmt"
	"os"
	"strings"
	"time"

	"github.com/spf13/cobra"

	"git.warky.dev/wdevs/relspecgo/pkg/merge"
	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/readers"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/bun"
	"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/drizzle"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/gorm"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/graphql"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/json"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/mssql"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/pgsql"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/prisma"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/sqlite"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/typeorm"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/yaml"
	"git.warky.dev/wdevs/relspecgo/pkg/writers"
	wbun "git.warky.dev/wdevs/relspecgo/pkg/writers/bun"
	wdbml "git.warky.dev/wdevs/relspecgo/pkg/writers/dbml"
	wdctx "git.warky.dev/wdevs/relspecgo/pkg/writers/dctx"
	wdrawdb "git.warky.dev/wdevs/relspecgo/pkg/writers/drawdb"
	wdrizzle "git.warky.dev/wdevs/relspecgo/pkg/writers/drizzle"
	wgorm "git.warky.dev/wdevs/relspecgo/pkg/writers/gorm"
	wgraphql "git.warky.dev/wdevs/relspecgo/pkg/writers/graphql"
	wjson "git.warky.dev/wdevs/relspecgo/pkg/writers/json"
	wmssql "git.warky.dev/wdevs/relspecgo/pkg/writers/mssql"
	wpgsql "git.warky.dev/wdevs/relspecgo/pkg/writers/pgsql"
	wprisma "git.warky.dev/wdevs/relspecgo/pkg/writers/prisma"
	wsqlite "git.warky.dev/wdevs/relspecgo/pkg/writers/sqlite"
	wtypeorm "git.warky.dev/wdevs/relspecgo/pkg/writers/typeorm"
	wyaml "git.warky.dev/wdevs/relspecgo/pkg/writers/yaml"
)

var (
	convertSourceType      string
	convertSourcePath      string
	convertSourceConn      string
	convertFromList        []string
	convertTargetType      string
	convertTargetPath      string
	convertPackageName     string
	convertSchemaFilter    string
	convertFlattenSchema   bool
	convertNullableTypes   string
	convertNullableArrays  string
	convertContinueOnError bool
	convertExtraFields     string
)

var convertCmd = &cobra.Command{
	Use:   "convert",
	Short: "Convert database schemas between different formats",
	Long: `Convert database schemas from one format to another.

Supports reading from multiple sources (live databases, DBML, DCTX, DrawDB,
JSON, YAML, etc.) and writing to various formats (GORM, Bun, DBML, JSON,
YAML, SQL, etc.).

Input formats:
  - dbml: DBML schema files
  - dctx: DCTX schema files
  - drawdb: DrawDB JSON files
  - graphql: GraphQL schema files (.graphql, SDL)
  - json: JSON database schema
  - yaml: YAML database schema
  - gorm: GORM model files (Go, file or directory)
  - bun: Bun model files (Go, file or directory)
  - drizzle: Drizzle ORM schema files (TypeScript, file or directory)
  - prisma: Prisma schema files (.prisma)
  - typeorm: TypeORM entity files (TypeScript)
  - pgsql: PostgreSQL database (live connection)
  - mssql: Microsoft SQL Server database (live connection)
  - sqlite: SQLite database file

Output formats:
  - dbml: DBML schema files
  - dctx: DCTX schema files
  - drawdb: DrawDB JSON files
  - graphql: GraphQL schema files (.graphql, SDL)
  - json: JSON database schema
  - yaml: YAML database schema
  - gorm: GORM model files (Go)
  - bun: Bun model files (Go)
  - drizzle: Drizzle ORM schema files (TypeScript)
  - prisma: Prisma schema files (.prisma)
  - typeorm: TypeORM entity files (TypeScript)
  - pgsql: PostgreSQL SQL schema
  - mssql: Microsoft SQL Server SQL schema
  - sqlite: SQLite SQL schema (with automatic schema flattening)

Connection String Examples:
  PostgreSQL:
    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

  SQLite:
    /path/to/database.db
    ./relative/path/database.sqlite
    database.db


Examples:
  # Convert DBML to GORM models
  relspec convert --from dbml --from-path schema.dbml \
                  --to gorm --to-path models/ --package models

  # Convert PostgreSQL database to DBML
  relspec convert --from pgsql \
                  --from-conn "postgres://myuser:mypass@localhost:5432/mydb" \
                  --to dbml --to-path schema.dbml

  # Convert PostgreSQL to JSON (with SSL disabled)
  relspec convert --from pgsql \
                  --from-conn "postgresql://user:pass@localhost/db?sslmode=disable" \
                  --to json --to-path schema.json

  # Convert DBML to JSON
  relspec convert --from dbml --from-path schema.dbml \
                  --to json --to-path schema.json

  # Convert DrawDB to PostgreSQL SQL
  relspec convert --from drawdb --from-path design.ddb \
                  --to pgsql --to-path schema.sql

  # Convert local PostgreSQL database to YAML
  relspec convert --from pgsql \
                  --from-conn "host=localhost port=5432 user=admin password=secret dbname=prod" \
                  --to yaml --to-path schema.yaml

  # Convert PostgreSQL to DCTX (single schema required)
  relspec convert --from pgsql \
                  --from-conn "postgres://user:pass@localhost:5432/mydb" \
                  --to dctx --to-path schema.dctx --schema public

  # Convert DBML to DCTX with specific schema
  relspec convert --from dbml --from-path schema.dbml \
                  --to dctx --to-path output.dctx --schema public

  # Convert GORM models directory to DBML
  relspec convert --from gorm --from-path /path/to/models \
                  --to dbml --to-path schema.dbml

  # Convert Bun models directory to JSON
  relspec convert --from bun --from-path ./models \
                  --to json --to-path schema.json

  # Convert SQLite database to JSON
  relspec convert --from sqlite --from-path database.db \
                  --to json --to-path schema.json

  # Convert SQLite to PostgreSQL SQL
  relspec convert --from sqlite --from-path database.db \
                  --to pgsql --to-path schema.sql`,
	RunE: runConvert,
}

func init() {
	convertCmd.Flags().StringVar(&convertSourceType, "from", "", "Source format (dbml, dctx, drawdb, graphql, json, yaml, gorm, bun, drizzle, prisma, typeorm, pgsql, sqlite)")
	convertCmd.Flags().StringVar(&convertSourcePath, "from-path", "", "Source file path (for file-based formats)")
	convertCmd.Flags().StringVar(&convertSourceConn, "from-conn", "", "Source connection string (for pgsql) or file path (for sqlite)")
	convertCmd.Flags().StringSliceVar(&convertFromList, "from-list", nil, "Comma-separated list of source file paths to read and merge (mutually exclusive with --from-path)")

	convertCmd.Flags().StringVar(&convertTargetType, "to", "", "Target format (dbml, dctx, drawdb, graphql, json, yaml, gorm, bun, drizzle, prisma, typeorm, pgsql)")
	convertCmd.Flags().StringVar(&convertTargetPath, "to-path", "", "Target output path (file or directory)")
	convertCmd.Flags().StringVar(&convertPackageName, "package", "", "Package name (for code generation formats like gorm/bun)")
	convertCmd.Flags().StringVar(&convertSchemaFilter, "schema", "", "Filter to a specific schema by name (required for formats like dctx that only support single schemas)")
	convertCmd.Flags().BoolVar(&convertFlattenSchema, "flatten-schema", false, "Flatten schema.table names to schema_table (useful for databases like SQLite that do not support schemas)")
	convertCmd.Flags().StringVar(&convertNullableTypes, "types", "", "Nullable type package for code-gen writers (bun/gorm): 'baselib' (default, Go pointer types), 'stdlib' (database/sql), or 'sqltypes'")
	convertCmd.Flags().StringVar(&convertNullableArrays, "array-nullable", "", "Nullable PostgreSQL array representation for the Bun writer in stdlib/baselib --types mode: 'slice' (default, plain slice) or 'pointer_slice' (*[]T, distinguishes NULL from '{}')")
	convertCmd.Flags().BoolVar(&convertContinueOnError, "continue-on-error", false, "Prepend \\set ON_ERROR_STOP off to generated SQL so psql continues past errors (pgsql output only)")
	convertCmd.Flags().StringVar(&convertExtraFields, "extra-fields", "", "Path to JSON file containing extra Bun model fields to inject (bun output only); fields support target_table, name, type, bun_tag, json_tag, comment")

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

func runConvert(cmd *cobra.Command, args []string) error {
	fmt.Fprintf(os.Stderr, "\n=== RelSpec Schema Converter ===\n")
	fmt.Fprintf(os.Stderr, "Started at: %s\n\n", getCurrentTimestamp())

	// Validate mutually exclusive flags
	if convertSourcePath != "" && len(convertFromList) > 0 {
		return fmt.Errorf("--from-path and --from-list are mutually exclusive")
	}

	// Read source database
	fmt.Fprintf(os.Stderr, "[1/2] Reading source schema...\n")
	fmt.Fprintf(os.Stderr, "      Format: %s\n", convertSourceType)

	var db *models.Database
	var err error

	if len(convertFromList) > 0 {
		db, err = readDatabaseListForConvert(convertSourceType, convertFromList)
	} else {
		if convertSourcePath != "" {
			fmt.Fprintf(os.Stderr, "      Path:   %s\n", convertSourcePath)
		}
		if convertSourceConn != "" {
			fmt.Fprintf(os.Stderr, "      Conn:   %s\n", maskPassword(convertSourceConn))
		}
		db, err = readDatabaseForConvert(convertSourceType, convertSourcePath, convertSourceConn)
	}
	if err != nil {
		return fmt.Errorf("failed to read source: %w", err)
	}

	fmt.Fprintf(os.Stderr, "      ✓ Successfully read database '%s'\n", db.Name)
	fmt.Fprintf(os.Stderr, "      Found: %d schema(s)\n", len(db.Schemas))

	totalTables := 0
	for _, schema := range db.Schemas {
		totalTables += len(schema.Tables)
	}
	fmt.Fprintf(os.Stderr, "      Found: %d table(s)\n\n", totalTables)

	// Write to target format
	fmt.Fprintf(os.Stderr, "[2/2] Writing to target format...\n")
	fmt.Fprintf(os.Stderr, "      Format: %s\n", convertTargetType)
	fmt.Fprintf(os.Stderr, "      Output: %s\n", convertTargetPath)
	if convertPackageName != "" {
		fmt.Fprintf(os.Stderr, "      Package: %s\n", convertPackageName)
	}
	if convertSchemaFilter != "" {
		fmt.Fprintf(os.Stderr, "      Schema: %s\n", convertSchemaFilter)
	}

	if err := writeDatabase(db, convertTargetType, convertTargetPath, convertPackageName, convertSchemaFilter, convertFlattenSchema, convertNullableTypes, convertNullableArrays, convertContinueOnError, convertExtraFields); err != nil {
		return fmt.Errorf("failed to write target: %w", err)
	}

	fmt.Fprintf(os.Stderr, "      ✓ Successfully wrote output\n\n")
	fmt.Fprintf(os.Stderr, "=== Conversion Complete ===\n")
	fmt.Fprintf(os.Stderr, "Completed at: %s\n\n", getCurrentTimestamp())

	return nil
}

func readDatabaseListForConvert(dbType string, files []string) (*models.Database, error) {
	if len(files) == 0 {
		return nil, fmt.Errorf("file list is empty")
	}

	fmt.Fprintf(os.Stderr, "      Files:  %d file(s)\n", len(files))

	var base *models.Database
	for i, filePath := range files {
		fmt.Fprintf(os.Stderr, "      [%d/%d] %s\n", i+1, len(files), filePath)
		db, err := readDatabaseForConvert(dbType, filePath, "")
		if err != nil {
			return nil, fmt.Errorf("failed to read %s: %w", filePath, err)
		}
		if base == nil {
			base = db
		} else {
			merge.MergeDatabases(base, db, &merge.MergeOptions{})
		}
	}

	return base, nil
}

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

	switch strings.ToLower(dbType) {
	case "dbml":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for DBML format")
		}
		reader = dbml.NewReader(newReaderOptions(filePath, ""))

	case "dctx":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for DCTX format")
		}
		reader = dctx.NewReader(newReaderOptions(filePath, ""))

	case "drawdb":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for DrawDB format")
		}
		reader = drawdb.NewReader(newReaderOptions(filePath, ""))

	case "json":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for JSON format")
		}
		reader = json.NewReader(newReaderOptions(filePath, ""))

	case "yaml", "yml":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for YAML format")
		}
		reader = yaml.NewReader(newReaderOptions(filePath, ""))

	case "pgsql", "postgres", "postgresql":
		if connString == "" {
			return nil, fmt.Errorf("connection string is required for PostgreSQL format")
		}
		reader = pgsql.NewReader(newReaderOptions("", connString))

	case "gorm":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for GORM format")
		}
		reader = gorm.NewReader(newReaderOptions(filePath, ""))

	case "bun":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for Bun format")
		}
		reader = bun.NewReader(newReaderOptions(filePath, ""))

	case "drizzle":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for Drizzle format")
		}
		reader = drizzle.NewReader(newReaderOptions(filePath, ""))

	case "prisma":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for Prisma format")
		}
		reader = prisma.NewReader(newReaderOptions(filePath, ""))

	case "typeorm":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for TypeORM format")
		}
		reader = typeorm.NewReader(newReaderOptions(filePath, ""))

	case "graphql", "gql":
		if filePath == "" {
			return nil, fmt.Errorf("file path is required for GraphQL format")
		}
		reader = graphql.NewReader(newReaderOptions(filePath, ""))

	case "mssql", "sqlserver", "mssql2016", "mssql2017", "mssql2019", "mssql2022":
		if connString == "" {
			return nil, fmt.Errorf("connection string is required for MSSQL format")
		}
		reader = mssql.NewReader(newReaderOptions("", connString))

	case "sqlite", "sqlite3":
		// SQLite can use either file path or connection string
		dbPath := filePath
		if dbPath == "" {
			dbPath = connString
		}
		if dbPath == "" {
			return nil, fmt.Errorf("file path or connection string is required for SQLite format")
		}
		reader = sqlite.NewReader(newReaderOptions(dbPath, ""))

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

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

	return db, nil
}

func writeDatabase(db *models.Database, dbType, outputPath, packageName, schemaFilter string, flattenSchema bool, nullableTypes, nullableArrays string, continueOnError bool, extraFields string) error {
	var writer writers.Writer

	writerOpts := newWriterOptions(outputPath, packageName, flattenSchema, nullableTypes, nullableArrays, continueOnError)
	if extraFields != "" {
		if !strings.EqualFold(dbType, "bun") {
			return fmt.Errorf("--extra-fields is only supported for Bun output")
		}
		extraFieldsJSON, err := os.ReadFile(extraFields)
		if err != nil {
			return fmt.Errorf("failed to read --extra-fields file %q: %w", extraFields, err)
		}

		var parsed []wbun.ExtraFieldConfig
		if err := stdjson.Unmarshal(extraFieldsJSON, &parsed); err != nil {
			return fmt.Errorf("invalid --extra-fields JSON in %q: %w", extraFields, err)
		}
		if len(parsed) == 0 {
			return fmt.Errorf("--extra-fields must contain at least one field")
		}
		writerOpts.Metadata = map[string]interface{}{
			"extra_fields": string(extraFieldsJSON),
		}
	}

	switch strings.ToLower(dbType) {
	case "dbml":
		writer = wdbml.NewWriter(writerOpts)

	case "dctx":
		writer = wdctx.NewWriter(writerOpts)

	case "drawdb":
		writer = wdrawdb.NewWriter(writerOpts)

	case "json":
		writer = wjson.NewWriter(writerOpts)

	case "yaml", "yml":
		writer = wyaml.NewWriter(writerOpts)

	case "gorm":
		if packageName == "" {
			return fmt.Errorf("package name is required for GORM format (use --package flag)")
		}
		writer = wgorm.NewWriter(writerOpts)

	case "bun":
		if packageName == "" {
			return fmt.Errorf("package name is required for Bun format (use --package flag)")
		}
		writer = wbun.NewWriter(writerOpts)

	case "drizzle":
		writer = wdrizzle.NewWriter(writerOpts)

	case "pgsql", "postgres", "postgresql", "sql":
		writer = wpgsql.NewWriter(writerOpts)

	case "mssql", "sqlserver", "mssql2016", "mssql2017", "mssql2019", "mssql2022":
		writer = wmssql.NewWriter(writerOpts)

	case "sqlite", "sqlite3":
		writer = wsqlite.NewWriter(writerOpts)

	case "prisma":
		writer = wprisma.NewWriter(writerOpts)

	case "typeorm":
		writer = wtypeorm.NewWriter(writerOpts)

	case "graphql", "gql":
		writer = wgraphql.NewWriter(writerOpts)

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

	// Handle schema filtering for formats that only support single schemas
	if schemaFilter != "" {
		// Find the specified schema
		var targetSchema *models.Schema
		for _, schema := range db.Schemas {
			if schema.Name == schemaFilter {
				targetSchema = schema
				break
			}
		}

		if targetSchema == nil {
			return fmt.Errorf("schema '%s' not found in database. Available schemas: %v",
				schemaFilter, getSchemaNames(db))
		}

		// Write just the filtered schema
		if err := writer.WriteSchema(targetSchema); err != nil {
			return fmt.Errorf("failed to write schema: %w", err)
		}
		return nil
	}

	// For formats like DCTX that don't support full database writes, require schema filter
	if strings.EqualFold(dbType, "dctx") {
		if len(db.Schemas) == 0 {
			return fmt.Errorf("no schemas found in database")
		}
		if len(db.Schemas) == 1 {
			// Auto-select the only schema
			if err := writer.WriteSchema(db.Schemas[0]); err != nil {
				return fmt.Errorf("failed to write schema: %w", err)
			}
			return nil
		}
		// Multiple schemas, require user to specify which one
		return fmt.Errorf("multiple schemas found, please specify which schema to export using --schema flag. Available schemas: %v",
			getSchemaNames(db))
	}

	// Write full database for formats that support it
	if err := writer.WriteDatabase(db); err != nil {
		return fmt.Errorf("failed to write database: %w", err)
	}

	return nil
}

// getSchemaNames returns a slice of schema names from a database
func getSchemaNames(db *models.Database) []string {
	names := make([]string, len(db.Schemas))
	for i, schema := range db.Schemas {
		names[i] = schema.Name
	}
	return names
}

func getCurrentTimestamp() string {
	return time.Now().Format("2006-01-02 15:04:05")
}

func maskPassword(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=") {
		result := ""
		parts := strings.Split(connStr, " ")
		for i, part := range parts {
			if strings.HasPrefix(part, "password=") {
				parts[i] = "password=***"
			}
		}
		result = strings.Join(parts, " ")
		return result
	}
	return connStr
}
