// Utility functions for opening and configuring a connection to a MySQL-like
// database, with optional TLS support.
//
// Functions in this file are not thread-safe. However, the returned *sql.DB is.
// Sane defaults are assumed: utf8mb4 encoding, UTC timezone, parsing date/time
// into time.Time.

package dbutil

import (
	"crypto/sha256"
	"crypto/tls"
	"crypto/x509"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"net/url"
	"path/filepath"
	"strings"

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

// SQL statement suffix to be appended when creating tables.
const SqlCreateTableSuffix = "CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci"

// Description of the SQL configuration file format.
const SqlConfigFileDescription = `File must contain a JSON object of the following form:
   {
    "dataSourceName": "[username[:password]@][protocol[(address)]]/dbname", (the connection string required by go-sql-driver; database name must be specified, query parameters are not supported)
    "tlsDisable": "false|true", (defaults to false; if set to true, uses an unencrypted connection; otherwise, the following fields are mandatory)
    "tlsServerName": "serverName", (the domain name of the SQL server for TLS)
    "rootCertPath": "[/]path/server-ca.pem", (the root certificate of the SQL server for TLS)
    "clientCertPath": "[/]path/client-cert.pem", (the client certificate for TLS)
    "clientKeyPath": "[/]path/client-key.pem" (the client private key for TLS)
   }
Paths must be either absolute or relative to the configuration file directory.`

// SqlConfig holds the fields needed to connect to a SQL instance and to
// configure TLS encryption of the information sent over the wire. It must be
// activated via Activate() before use.
type SqlConfig struct {
	// DataSourceName is the connection string as required by go-sql-driver:
	// "[username[:password]@][protocol[(address)]]/dbname";
	// database name must be specified, query parameters are not supported.
	DataSourceName string `json:"dataSourceName"`
	// TLSDisable, if set to true, uses an unencrypted connection;
	// otherwise, the following fields are mandatory.
	TLSDisable bool `json:"tlsDisable"`
	// TLSServerName is the domain name of the SQL server for TLS.
	TLSServerName string `json:"tlsServerName"`
	// RootCertPath is the root certificate of the SQL server for TLS.
	RootCertPath string `json:"rootCertPath"`
	// ClientCertPath is the client certificate for TLS.
	ClientCertPath string `json:"clientCertPath"`
	// ClientKeyPath is the client private key for TLS.
	ClientKeyPath string `json:"clientKeyPath"`
}

// ActiveSqlConfig represents a SQL configuration that has been activated
// by registering the TLS configuration (if applicable). It can be used for
// opening SQL database connections.
type ActiveSqlConfig struct {
	// cfg is a copy of the SqlConfig that was activated.
	cfg *SqlConfig
	// tlsConfigIdentifier is the identifier under which the TLS configuration
	// is registered with go-sql-driver. It is computed as a secure hash of the
	// SqlConfig after resolving any relative paths.
	tlsConfigIdentifier string `json:"-"`
}

// Parses the SQL configuration file pointed to by sqlConfigFile (format
// described in SqlConfigFileDescription; also see links below).
// https://github.com/go-sql-driver/mysql/#dsn-data-source-name
// https://github.com/go-sql-driver/mysql/#tls
func ParseSqlConfigFromFile(sqlConfigFile string) (*SqlConfig, error) {
	configJSON, err := ioutil.ReadFile(sqlConfigFile)
	if err != nil {
		return nil, fmt.Errorf("failed reading SQL config file %q: %v", sqlConfigFile, err)
	}
	var config SqlConfig
	if err = json.Unmarshal(configJSON, &config); err != nil {
		// TODO(ivanpi): Parsing errors might leak the SQL password into error
		// logs, depending on standard library implementation.
		return nil, fmt.Errorf("failed parsing SQL config file %q: %v", sqlConfigFile, err)
	}
	return &config, nil
}

// Activates the SQL configuration by registering the TLS configuration with
// go-mysql-driver (if TLSDisable is not set).
// Certificate paths from SqlConfig that aren't absolute are interpreted relative
// to certBaseDir.
// For more information see https://github.com/go-sql-driver/mysql/#tls
func (sc *SqlConfig) Activate(certBaseDir string) (*ActiveSqlConfig, error) {
	if sc.TLSDisable {
		return &ActiveSqlConfig{
			cfg: sc.normalizePaths(""),
		}, nil
	}
	cbdAbs, err := filepath.Abs(certBaseDir)
	if err != nil {
		return nil, fmt.Errorf("failed resolving certificate base directory %q: %v", certBaseDir, err)
	}
	scn := sc.normalizePaths(cbdAbs)
	configId := scn.hash()
	if err = registerSqlTLSConfig(scn, configId); err != nil {
		return nil, fmt.Errorf("failed registering TLS config: %v", err)
	}
	return &ActiveSqlConfig{
		cfg:                 scn,
		tlsConfigIdentifier: configId,
	}, nil
}

// Convenience function to parse and activate the SQL configuration file.
// Certificate paths that aren't absolute are interpreted relative to the
// directory containing sqlConfigFile.
func ActivateSqlConfigFromFile(sqlConfigFile string) (*ActiveSqlConfig, error) {
	cfg, err := ParseSqlConfigFromFile(sqlConfigFile)
	if err != nil {
		return nil, err
	}
	activeCfg, err := cfg.Activate(filepath.Dir(sqlConfigFile))
	if err != nil {
		return nil, fmt.Errorf("failed activating SQL config from file %q: %v", sqlConfigFile, err)
	}
	return activeCfg, nil
}

// Opens a connection to the SQL database using the provided configuration.
// Sets the specified transaction isolation (see link below).
// https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tx_isolation
func (sqlConfig *ActiveSqlConfig) NewSqlDBConn(txIsolation string) (*sql.DB, error) {
	return openSqlDBConn(configureSqlDBConn(sqlConfig, txIsolation))
}

// Convenience function to parse and activate the configuration file and open
// a connection to the SQL database. If multiple connections with the same
// configuration are needed, a single ActivateSqlConfigFromFile() and multiple
// NewSqlDbConn() calls are recommended instead.
func NewSqlDBConnFromFile(sqlConfigFile, txIsolation string) (*sql.DB, error) {
	config, err := ActivateSqlConfigFromFile(sqlConfigFile)
	if err != nil {
		return nil, err
	}
	return config.NewSqlDBConn(txIsolation)
}

func configureSqlDBConn(sqlConfig *ActiveSqlConfig, txIsolation string) string {
	params := url.Values{}
	// Setting charset is unneccessary when collation is set, according to
	// https://github.com/go-sql-driver/mysql/#charset
	params.Set("collation", "utf8mb4_general_ci")
	// Maps SQL date/time values into time.Time instead of strings.
	params.Set("parseTime", "true")
	params.Set("loc", "UTC")
	params.Set("time_zone", "'+00:00'")
	if !sqlConfig.cfg.TLSDisable {
		params.Set("tls", sqlConfig.tlsConfigIdentifier)
	}
	params.Set("tx_isolation", "'"+txIsolation+"'")
	return sqlConfig.cfg.DataSourceName + "?" + params.Encode()
}

func openSqlDBConn(dataSrcName string) (*sql.DB, error) {
	// Prevent leaking the SQL password into error logs.
	sanitizedDSN := dataSrcName[strings.LastIndex(dataSrcName, "@")+1:]
	db, err := sql.Open("mysql", dataSrcName)
	if err != nil {
		return nil, fmt.Errorf("failed opening database connection at %q: %v", sanitizedDSN, err)
	}
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("failed connecting to database at %q: %v", sanitizedDSN, err)
	}
	return db, nil
}

// registerSqlTLSConfig sets up the SQL connection to use TLS encryption
// and registers the configuration under configId.
// For more information see https://github.com/go-sql-driver/mysql/#tls
func registerSqlTLSConfig(cfg *SqlConfig, configId string) error {
	rootCertPool := x509.NewCertPool()
	pem, err := ioutil.ReadFile(cfg.RootCertPath)
	if err != nil {
		return fmt.Errorf("failed reading root certificate: %v", err)
	}
	if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
		return fmt.Errorf("failed to append PEM to cert pool")
	}
	ckpair, err := tls.LoadX509KeyPair(cfg.ClientCertPath, cfg.ClientKeyPath)
	if err != nil {
		return fmt.Errorf("failed loading client key pair: %v", err)
	}
	clientCert := []tls.Certificate{ckpair}
	return mysql.RegisterTLSConfig(configId, &tls.Config{
		RootCAs:      rootCertPool,
		Certificates: clientCert,
		ServerName:   cfg.TLSServerName,
		// SSLv3 is more vulnerable than TLSv1.0, see https://en.wikipedia.org/wiki/POODLE
		// TODO(ivanpi): Increase when Cloud SQL starts supporting higher TLS versions.
		MinVersion: tls.VersionTLS10,
		ClientAuth: tls.RequireAndVerifyClientCert,
	})
}

// Computes a secure hash of the SqlConfig. Paths are canonicalized before
// hashing.
func (sc *SqlConfig) hash() string {
	scn := sc.normalizePaths("")
	fieldsToHash := []interface{}{
		scn.DataSourceName, scn.TLSDisable, scn.TLSServerName,
		scn.RootCertPath, scn.ClientCertPath, scn.ClientKeyPath,
	}
	hashAcc := make([]byte, 0, len(fieldsToHash)*sha256.Size)
	for _, field := range fieldsToHash {
		fieldHash := sha256.Sum256([]byte(fmt.Sprintf("%v", field)))
		hashAcc = append(hashAcc, fieldHash[:]...)
	}
	structHash := sha256.Sum256(hashAcc)
	return hex.EncodeToString(structHash[:])
}

// Returns a copy of the SqlConfig with certificate paths canonicalized and
// resolved relative to certBaseDir. Blank paths remain blank.
func (sc *SqlConfig) normalizePaths(certBaseDir string) *SqlConfig {
	scn := *sc
	for _, path := range []*string{&scn.RootCertPath, &scn.ClientCertPath, &scn.ClientKeyPath} {
		*path = normalizePath(*path, certBaseDir)
	}
	return &scn
}

// If path is not absolute, resolves path relative to baseDir. Otherwise,
// canonicalizes path. Blank paths are not resolved or canonicalized.
func normalizePath(path, baseDir string) string {
	if strings.TrimSpace(path) == "" {
		return ""
	}
	if filepath.IsAbs(path) {
		return filepath.Clean(path)
	}
	return filepath.Join(baseDir, path)
}