package sqldb

import (
	"database/sql"
	"fmt"
	"net/url"
	"path"
	"strings"
	"time"

	pgx_migrate "github.com/golang-migrate/migrate/v4/database/pgx/v5"
	_ "github.com/golang-migrate/migrate/v4/source/file"
	_ "github.com/jackc/pgx/v5"
	"github.com/lightningnetwork/lnd/fn/v2"
)

var (
	// DefaultPostgresFixtureLifetime is the default maximum time a Postgres
	// test fixture is being kept alive. After that time the docker
	// container will be terminated forcefully, even if the tests aren't
	// fully executed yet. So this time needs to be chosen correctly to be
	// longer than the longest expected individual test run time.
	DefaultPostgresFixtureLifetime = 10 * time.Minute

	// postgresSchemaReplacements is a map of schema strings that need to be
	// replaced for postgres. This is needed because we write the schemas to
	// work with sqlite primarily but in sqlc's own dialect, and postgres
	// has some differences.
	postgresSchemaReplacements = map[string]string{
		"BLOB":                "BYTEA",
		"INTEGER PRIMARY KEY": "BIGSERIAL PRIMARY KEY",
		// We need this space in front of the TIMESTAMP keyword to
		// avoid replacing words which just have the word "TIMESTAMP" in
		// them.
		" TIMESTAMP": " TIMESTAMP WITHOUT TIME ZONE",
		"UNHEX":      "DECODE",
	}

	// Make sure PostgresStore implements the MigrationExecutor interface.
	_ MigrationExecutor = (*PostgresStore)(nil)

	// Make sure PostgresStore implements the DB interface.
	_ DB = (*PostgresStore)(nil)
)

// sslModesRequiringTLS lists sslmode values that already enforce TLS and
// therefore do not need to be rewritten when RequireSSL is set.
var sslModesRequiringTLS = map[string]struct{}{
	"require":     {},
	"verify-ca":   {},
	"verify-full": {},
}

// replacePasswordInDSN takes a DSN string and returns it with the password
// replaced by "***".
func replacePasswordInDSN(dsn string) (string, error) {
	// Parse the DSN as a URL
	u, err := url.Parse(dsn)
	if err != nil {
		return "", err
	}

	// Check if the URL has a user info part
	if u.User != nil {
		username := u.User.Username()

		// Reconstruct user info with "***" as password
		userInfo := username + ":***@"

		// Rebuild the DSN with the modified user info
		sanitizeDSN := strings.Replace(
			dsn, u.User.String()+"@", userInfo, 1,
		)

		return sanitizeDSN, nil
	}

	// Return the original DSN if no user info is present
	return dsn, nil
}

// getDatabaseNameFromDSN extracts the database name from a DSN string.
func getDatabaseNameFromDSN(dsn string) (string, error) {
	// Parse the DSN as a URL
	u, err := url.Parse(dsn)
	if err != nil {
		return "", err
	}

	// The database name is the last segment of the path. Trim leading slash
	// and return the last segment.
	return path.Base(u.Path), nil
}

// ensureRequiredSSLMode rewrites the DSN to require TLS when requested.
func ensureRequiredSSLMode(dsn string, requireSSL bool) (string, error) {
	if !requireSSL {
		return dsn, nil
	}

	u, err := url.Parse(dsn)
	if err != nil {
		return "", err
	}

	query := u.Query()
	sslMode := query.Get("sslmode")
	if _, ok := sslModesRequiringTLS[sslMode]; !ok {
		query.Set("sslmode", "require")
	}

	u.RawQuery = query.Encode()

	return u.String(), nil
}

// PostgresStore is a database store implementation that uses a Postgres
// backend.
type PostgresStore struct {
	cfg *PostgresConfig

	*BaseDB
}

// NewPostgresStore creates a new store that is backed by a Postgres database
// backend.
func NewPostgresStore(cfg *PostgresConfig) (*PostgresStore, error) {
	if cfg == nil {
		return nil, fmt.Errorf("postgres config is required")
	}

	// Copy the caller config so we can enforce RequireSSL on the DSN
	// without mutating a config value that may be reused elsewhere.
	effectiveCfg := *cfg

	effectiveDSN, err := ensureRequiredSSLMode(
		effectiveCfg.Dsn, effectiveCfg.RequireSSL,
	)
	if err != nil {
		return nil, err
	}
	effectiveCfg.Dsn = effectiveDSN

	sanitizedDSN, err := replacePasswordInDSN(effectiveCfg.Dsn)
	if err != nil {
		return nil, err
	}
	log.Infof("Using SQL database '%s'", sanitizedDSN)

	db, err := sql.Open("pgx", effectiveCfg.Dsn)
	if err != nil {
		return nil, err
	}

	// Ensure the migration tracker table exists before running migrations.
	// This table tracks migration progress and ensures compatibility with
	// SQLC query generation. If the table is already created by an SQLC
	// migration, this operation becomes a no-op.
	migrationTrackerSQL := `
	CREATE TABLE IF NOT EXISTS migration_tracker (
		version INTEGER UNIQUE NOT NULL,
		migration_time TIMESTAMP NOT NULL
	);`

	_, err = db.Exec(migrationTrackerSQL)
	if err != nil {
		return nil, fmt.Errorf("error creating migration tracker: %w",
			err)
	}

	maxConns := DefaultPostgresMaxConns
	if cfg.MaxOpenConnections > 0 {
		maxConns = cfg.MaxOpenConnections
	}

	maxIdleConns := defaultMaxIdleConns
	if cfg.MaxIdleConnections > 0 {
		maxIdleConns = cfg.MaxIdleConnections
	}

	connMaxLifetime := defaultConnMaxLifetime
	if cfg.ConnMaxLifetime > 0 {
		connMaxLifetime = cfg.ConnMaxLifetime
	}

	connMaxIdleTime := defaultConnMaxIdleTime
	if cfg.ConnMaxIdleTime > 0 {
		connMaxIdleTime = cfg.ConnMaxIdleTime
	}

	db.SetMaxOpenConns(maxConns)
	db.SetMaxIdleConns(maxIdleConns)
	db.SetConnMaxLifetime(connMaxLifetime)
	db.SetConnMaxIdleTime(connMaxIdleTime)

	return &PostgresStore{
		cfg: &effectiveCfg,
		BaseDB: &BaseDB{
			DB:             db,
			BackendType:    BackendTypePostgres,
			SkipMigrations: effectiveCfg.SkipMigrations,
		},
	}, nil
}

// GetBaseDB returns the underlying BaseDB instance for the Postgres store.
// It is a trivial helper method to comply with the sqldb.DB interface.
func (s *PostgresStore) GetBaseDB() *BaseDB {
	return s.BaseDB
}

func errPostgresMigration(err error) error {
	return fmt.Errorf("error creating postgres migration: %w", err)
}

// ExecuteMigrations runs migrations for the Postgres database using the
// default production migration target.
func (s *PostgresStore) ExecuteMigrations(set MigrationSet) error {
	if s.SkipMigrations {
		return nil
	}

	return s.executeMigrations(TargetLatest, set)
}

// executeMigrations runs migrations for the Postgres database, depending on
// the target given, either all migrations or up to a given version.
func (s *PostgresStore) executeMigrations(target MigrationTarget,
	set MigrationSet) error {

	if err := set.validate(); err != nil {
		return err
	}

	dbName, err := getDatabaseNameFromDSN(s.cfg.Dsn)
	if err != nil {
		return err
	}

	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{
		MigrationsTable: set.TrackingTableName,
	})
	if err != nil {
		return errPostgresMigration(err)
	}

	opts := &migrateOptions{
		latestVersion: fn.Some(set.LatestMigrationVersion),
	}

	if set.MakeProgrammaticMigrations != nil {
		postMigSteps, err := set.MakeProgrammaticMigrations(s.BaseDB)
		if err != nil {
			return errPostgresMigration(err)
		}
		opts.programmaticMigrs = postMigSteps
	}

	// Populate the database with our set of schemas based on our embedded
	// in-memory file system.
	postgresFS := newReplacerFS(set.SQLFiles, postgresSchemaReplacements)
	return applyMigrations(
		postgresFS, driver, set.SQLFileDirectory, dbName, target, opts,
	)
}

// GetSchemaVersion returns the current schema version of the Postgres database.
func (s *PostgresStore) GetSchemaVersion() (int, bool, error) {
	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})
	if err != nil {
		return 0, false, errPostgresMigration(err)

	}

	version, dirty, err := driver.Version()
	if err != nil {
		return 0, false, err
	}

	return version, dirty, nil
}

// SetSchemaVersion sets the schema version of the Postgres database.
//
// NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
func (s *PostgresStore) SetSchemaVersion(version int, dirty bool) error {
	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})
	if err != nil {
		return errPostgresMigration(err)
	}

	return driver.SetVersion(version, dirty)
}
