package migrate

import (
	"context"
	"database/sql"
	"fmt"
	"net"
	"net/url"
	"os"
	"path/filepath"
	"slices"
	"strconv"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
)

// InitDumpOptions controls generation of a persona-owned MySQL init SQL file.
type InitDumpOptions struct {
	URL            string
	Database       string
	TargetDatabase string
	OutputPath     string
	RowTables      []string
	IncludeDeleted bool
	Where          string
	TableWhere     map[string]string
	Inspector      InitDumpInspector
}

// InitDumpResult describes a generated init SQL file.
type InitDumpResult struct {
	OutputPath     string
	Database       string
	TargetDatabase string
	Schema         TableDumpSet
	Rows           []TableDumpPlan
}

// TableDumpSet describes the tables included in the schema dump.
type TableDumpSet struct {
	Tables []string
}

// TableDumpPlan describes one data dump invocation.
type TableDumpPlan struct {
	Table string
	Where string
}

// InitDumpInspector provides table metadata, schema DDL, and row SQL.
type InitDumpInspector interface {
	ListTables(ctx context.Context, database string) ([]string, error)
	HasColumn(ctx context.Context, database, table, column string) (bool, error)
	CreateTableSQL(ctx context.Context, database, table string) (string, error)
	DumpRows(ctx context.Context, database, table, where string) ([]string, error)
}

// MySQLConnection contains connection fields derived from a URL/DSN.
type MySQLConnection struct {
	Host     string
	Port     string
	User     string
	Password string
	Database string
}

// DumpInitSQL writes a deterministic MySQL init SQL file: full schema for all
// tables plus selected row dumps. Row dumps default to non-deleted rows when a
// table has a deleted_at column.
func DumpInitSQL(ctx context.Context, opts *InitDumpOptions) (*InitDumpResult, error) {
	if opts == nil {
		return nil, fmt.Errorf("options are required")
	}
	if strings.TrimSpace(opts.OutputPath) == "" {
		return nil, fmt.Errorf("output path is required")
	}
	conn, err := ParseMySQLConnectionURL(opts.URL)
	if err != nil {
		return nil, err
	}
	database := strings.TrimSpace(opts.Database)
	if database == "" {
		database = conn.Database
	}
	if database == "" {
		return nil, fmt.Errorf("database is required")
	}
	conn.Database = database
	targetDatabase := strings.TrimSpace(opts.TargetDatabase)
	if targetDatabase == "" {
		targetDatabase = database
	}

	inspector := opts.Inspector
	closeInspector := false
	if inspector == nil {
		inspector, err = newSQLInitDumpInspector(&conn)
		if err != nil {
			return nil, err
		}
		closeInspector = true
	}
	if closeInspector {
		closer, ok := inspector.(interface{ Close() error })
		if !ok {
			return nil, fmt.Errorf("mysql inspector cleanup is unavailable")
		}
		defer closer.Close()
	}

	schemaTables, err := inspector.ListTables(ctx, database)
	if err != nil {
		return nil, fmt.Errorf("list schema tables: %w", err)
	}
	slices.Sort(schemaTables)
	rowTables := normalizeTableList(opts.RowTables)
	if len(rowTables) == 0 {
		rowTables = append([]string(nil), schemaTables...)
	} else {
		rowTables = includeMigrationLedgerTables(rowTables, schemaTables)
	}
	slices.Sort(rowTables)

	var out strings.Builder
	fmt.Fprintf(&out, "-- Generated by cp9 db dump-init from %s.\n", database)
	fmt.Fprintf(&out, "CREATE DATABASE IF NOT EXISTS %s;\n", quoteIdentifier(targetDatabase))
	fmt.Fprintf(&out, "USE %s;\n", quoteIdentifier(targetDatabase))
	out.WriteString("SET FOREIGN_KEY_CHECKS=0;\n\n")

	for _, table := range schemaTables {
		ddl, err := inspector.CreateTableSQL(ctx, database, table)
		if err != nil {
			return nil, fmt.Errorf("dump schema for %s: %w", table, err)
		}
		out.WriteString(strings.TrimSpace(ddl))
		if !strings.HasSuffix(strings.TrimSpace(ddl), ";") {
			_, _ = out.WriteString(";")
		}
		out.WriteString("\n\n")
	}

	plans := make([]TableDumpPlan, 0, len(rowTables))
	for _, table := range rowTables {
		where := strings.TrimSpace(opts.TableWhere[table])
		if where == "" && !isMigrationLedgerTable(table) {
			where = strings.TrimSpace(opts.Where)
		}
		if where == "" && !opts.IncludeDeleted && !isMigrationLedgerTable(table) {
			deletedColumn, err := deletedFilterColumn(ctx, inspector, database, table)
			if err != nil {
				return nil, fmt.Errorf("inspect %s soft-delete column: %w", table, err)
			}
			if deletedColumn != "" {
				where = quoteIdentifier(deletedColumn) + " IS NULL"
			}
		}
		rows, err := inspector.DumpRows(ctx, database, table, where)
		if err != nil {
			return nil, fmt.Errorf("dump rows for %s: %w", table, err)
		}
		for _, row := range rows {
			out.WriteString(row)
			if !strings.HasSuffix(strings.TrimSpace(row), ";") {
				_, _ = out.WriteString(";")
			}
			_, _ = out.WriteString("\n")
		}
		if len(rows) > 0 {
			out.WriteString("\n")
		}
		plans = append(plans, TableDumpPlan{Table: table, Where: where})
	}

	out.WriteString("SET FOREIGN_KEY_CHECKS=1;\n")
	if dir := filepath.Dir(opts.OutputPath); dir != "." && dir != "" {
		if err := os.MkdirAll(dir, 0o755); err != nil {
			return nil, fmt.Errorf("create output directory %s: %w", dir, err)
		}
	}
	if err := os.WriteFile(opts.OutputPath, []byte(out.String()), 0o600); err != nil {
		return nil, fmt.Errorf("write %s: %w", opts.OutputPath, err)
	}
	return &InitDumpResult{
		OutputPath:     opts.OutputPath,
		Database:       database,
		TargetDatabase: targetDatabase,
		Schema:         TableDumpSet{Tables: schemaTables},
		Rows:           plans,
	}, nil
}

func includeMigrationLedgerTables(rowTables, schemaTables []string) []string {
	out := append([]string(nil), rowTables...)
	seen := make(map[string]struct{}, len(out))
	for _, table := range out {
		seen[table] = struct{}{}
	}
	for _, table := range schemaTables {
		if !isMigrationLedgerTable(table) {
			continue
		}
		if _, ok := seen[table]; ok {
			continue
		}
		seen[table] = struct{}{}
		out = append(out, table)
	}
	return out
}

func isMigrationLedgerTable(table string) bool {
	table = strings.TrimSpace(table)
	return table == "migrations" ||
		table == "schema_migrations" ||
		table == "goose_db_version" ||
		strings.HasSuffix(table, "_migrations")
}

func deletedFilterColumn(ctx context.Context, inspector InitDumpInspector, database, table string) (string, error) {
	for _, column := range []string{"deleted_at", "delete_time"} {
		hasColumn, err := inspector.HasColumn(ctx, database, table, column)
		if err != nil {
			return "", err
		}
		if hasColumn {
			return column, nil
		}
	}
	return "", nil
}

// ParseMySQLConnectionURL accepts mysql:// URLs and Go MySQL DSNs.
func ParseMySQLConnectionURL(raw string) (MySQLConnection, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return MySQLConnection{}, fmt.Errorf("mysql url is required")
	}
	if strings.HasPrefix(raw, "mysql://") {
		u, err := url.Parse(raw)
		if err != nil {
			return MySQLConnection{}, fmt.Errorf("parse mysql url: %w", err)
		}
		host := u.Hostname()
		if host == "" {
			return MySQLConnection{}, fmt.Errorf("mysql url host is required")
		}
		port := u.Port()
		if port == "" {
			port = "3306"
		}
		if u.User == nil || strings.TrimSpace(u.User.Username()) == "" {
			return MySQLConnection{}, fmt.Errorf("mysql url user is required")
		}
		password, _ := u.User.Password()
		return MySQLConnection{
			Host:     host,
			Port:     port,
			User:     u.User.Username(),
			Password: password,
			Database: strings.TrimPrefix(u.Path, "/"),
		}, nil
	}

	cfg, err := mysql.ParseDSN(raw)
	if err == nil {
		host, port := splitMySQLAddr(cfg.Addr)
		return MySQLConnection{
			Host:     host,
			Port:     port,
			User:     cfg.User,
			Password: cfg.Passwd,
			Database: cfg.DBName,
		}, nil
	}

	host, port, user, pass, legacyErr := ParseDSN(raw)
	if legacyErr != nil {
		return MySQLConnection{}, fmt.Errorf("parse mysql connection: mysql dsn: %w; legacy dsn: %w", err, legacyErr)
	}
	return MySQLConnection{
		Host:     host,
		Port:     port,
		User:     user,
		Password: pass,
		Database: extractDBName(raw, ""),
	}, nil
}

func splitMySQLAddr(addr string) (host, port string) {
	if strings.TrimSpace(addr) == "" {
		return "127.0.0.1", "3306"
	}
	host, port, err := net.SplitHostPort(addr)
	if err != nil {
		return addr, "3306"
	}
	return host, port
}

func normalizeTableList(tables []string) []string {
	seen := map[string]struct{}{}
	out := make([]string, 0, len(tables))
	for _, raw := range tables {
		for part := range strings.SplitSeq(raw, ",") {
			table := strings.TrimSpace(part)
			if table == "" {
				continue
			}
			if _, ok := seen[table]; ok {
				continue
			}
			seen[table] = struct{}{}
			out = append(out, table)
		}
	}
	return out
}

type sqlInitDumpInspector struct {
	db *sql.DB
}

func newSQLInitDumpInspector(conn *MySQLConnection) (*sqlInitDumpInspector, error) {
	if conn == nil {
		return nil, fmt.Errorf("mysql connection is required")
	}
	cfg := mysql.Config{
		User:                 conn.User,
		Passwd:               conn.Password,
		Net:                  "tcp",
		Addr:                 net.JoinHostPort(conn.Host, conn.Port),
		DBName:               conn.Database,
		AllowNativePasswords: true,
		ParseTime:            true,
	}
	db, err := sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		return nil, fmt.Errorf("open mysql connection: %w", err)
	}
	return &sqlInitDumpInspector{db: db}, nil
}

func (i *sqlInitDumpInspector) Close() error {
	return i.db.Close()
}

func (i *sqlInitDumpInspector) ListTables(ctx context.Context, database string) ([]string, error) {
	rows, err := i.db.QueryContext(ctx, `
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME`, database)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var table string
		if err := rows.Scan(&table); err != nil {
			return nil, err
		}
		tables = append(tables, table)
	}
	return tables, rows.Err()
}

func (i *sqlInitDumpInspector) HasColumn(ctx context.Context, database, table, column string) (bool, error) {
	var count int
	if err := i.db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?`, database, table, column).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}

func (i *sqlInitDumpInspector) CreateTableSQL(ctx context.Context, database, table string) (string, error) {
	var tableName, createSQL string
	query := "SHOW CREATE TABLE " + qualifiedTableName(database, table)
	if err := i.db.QueryRowContext(ctx, query).Scan(&tableName, &createSQL); err != nil {
		return "", err
	}
	return createSQL, nil
}

func (i *sqlInitDumpInspector) DumpRows(ctx context.Context, database, table, where string) ([]string, error) {
	columns, err := i.dumpableColumns(ctx, database, table)
	if err != nil {
		return nil, err
	}
	if len(columns) == 0 {
		return nil, nil
	}
	quotedColumns := make([]string, 0, len(columns))
	for _, column := range columns {
		quotedColumns = append(quotedColumns, quoteIdentifier(column))
	}

	// #nosec G202 -- identifiers are quoted; WHERE is explicit filter input.
	query := "SELECT " + strings.Join(quotedColumns, ", ") + " FROM " +
		qualifiedTableName(database, table)
	if strings.TrimSpace(where) != "" {
		query += " WHERE " + where
	}
	orderBy, err := i.primaryKeyOrderBy(ctx, database, table)
	if err != nil {
		return nil, err
	}
	if orderBy != "" {
		query += " ORDER BY " + orderBy
	}
	rows, err := i.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	values := make([]any, len(columns))
	valuePtrs := make([]any, len(columns))
	for i := range values {
		valuePtrs[i] = &values[i]
	}

	var statements []string
	for rows.Next() {
		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, err
		}
		literals := make([]string, 0, len(values))
		for _, value := range values {
			literals = append(literals, formatSQLLiteral(value))
		}
		statements = append(
			statements,
			fmt.Sprintf(
				"INSERT INTO %s (%s) VALUES (%s);",
				quoteIdentifier(table),
				strings.Join(quotedColumns, ", "),
				strings.Join(literals, ", "),
			),
		)
	}
	return statements, rows.Err()
}

func (i *sqlInitDumpInspector) dumpableColumns(ctx context.Context, database, table string) ([]string, error) {
	rows, err := i.db.QueryContext(ctx, `
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
  AND (GENERATION_EXPRESSION IS NULL OR GENERATION_EXPRESSION = '')
ORDER BY ORDINAL_POSITION`, database, table)
	if err != nil {
		return nil, fmt.Errorf("inspect %s dumpable columns: %w", table, err)
	}
	defer rows.Close()

	var columns []string
	for rows.Next() {
		var column string
		if err := rows.Scan(&column); err != nil {
			return nil, err
		}
		columns = append(columns, column)
	}
	return columns, rows.Err()
}

func (i *sqlInitDumpInspector) primaryKeyOrderBy(ctx context.Context, database, table string) (string, error) {
	rows, err := i.db.QueryContext(ctx, `
SELECT COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION`, database, table)
	if err != nil {
		return "", fmt.Errorf("inspect %s primary key: %w", table, err)
	}
	defer rows.Close()

	var columns []string
	for rows.Next() {
		var column string
		if err := rows.Scan(&column); err != nil {
			return "", err
		}
		columns = append(columns, quoteIdentifier(column))
	}
	if err := rows.Err(); err != nil {
		return "", err
	}
	return strings.Join(columns, ", "), nil
}

func qualifiedTableName(database, table string) string {
	return quoteIdentifier(database) + "." + quoteIdentifier(table)
}

func quoteIdentifier(name string) string {
	return "`" + strings.ReplaceAll(name, "`", "``") + "`"
}

func formatSQLLiteral(value any) string {
	switch v := value.(type) {
	case nil:
		return "NULL"
	case []byte:
		return quoteSQLString(string(v))
	case string:
		return quoteSQLString(v)
	case time.Time:
		return quoteSQLString(v.Format("2006-01-02 15:04:05.999999"))
	case bool:
		if v {
			return "1"
		}
		return "0"
	case int64:
		return strconv.FormatInt(v, 10)
	case uint64:
		return strconv.FormatUint(v, 10)
	case float64:
		return strconv.FormatFloat(v, 'g', -1, 64)
	default:
		return quoteSQLString(fmt.Sprint(v))
	}
}

func quoteSQLString(value string) string {
	var b strings.Builder
	_, _ = b.WriteString("'")
	for _, r := range value {
		switch r {
		case 0:
			b.WriteString(`\0`)
		case '\n':
			b.WriteString(`\n`)
		case '\r':
			b.WriteString(`\r`)
		case '\\':
			b.WriteString(`\\`)
		case '\'':
			b.WriteString(`\'`)
		case '\032':
			b.WriteString(`\Z`)
		default:
			_, _ = b.WriteRune(r)
		}
	}
	_, _ = b.WriteString("'")
	return b.String()
}