package storage

import (
	"database/sql"
	"fmt"
)

// ---------------------------
// Postgres Strategy
// ---------------------------

type PostgresStrategy struct{}

func (p *PostgresStrategy) DriverName() string { return "postgres" }

// Placeholder returns $1, $2, etc.
func (p *PostgresStrategy) Placeholder(n int) string { return fmt.Sprintf("$%d", n) }

// GetTimeNow returns the PostgreSQL function for current timestamp.
func (p *PostgresStrategy) GetTimeNow() string { return "now()" }

// GetTrueValue returns the boolean representation.
func (p *PostgresStrategy) GetTrueValue() string { return "true" }

// GetFalseValue returns the boolean representation.
func (p *PostgresStrategy) GetFalseValue() string { return "false" }

func (p *PostgresStrategy) InitSchemaSQL() string {
	return `
		CREATE TABLE IF NOT EXISTS clusters (
			id SERIAL PRIMARY KEY,
			name TEXT UNIQUE NOT NULL,
			data JSONB NOT NULL,
			enabled BOOLEAN DEFAULT true,
			updated_at TIMESTAMP DEFAULT now()
		);
		CREATE TABLE IF NOT EXISTS listeners (
			id SERIAL PRIMARY KEY,
			name TEXT UNIQUE NOT NULL,
			data JSONB NOT NULL,
			enabled BOOLEAN DEFAULT true,
			updated_at TIMESTAMP DEFAULT now()
		);
		CREATE TABLE IF NOT EXISTS secrets (
			id SERIAL PRIMARY KEY,
			name TEXT UNIQUE NOT NULL,
			data JSONB NOT NULL,
			enabled BOOLEAN DEFAULT true,
			updated_at TIMESTAMP DEFAULT now(),
			domain TEXT NULL
		);
		CREATE TABLE IF NOT EXISTS certificates (
			domain TEXT PRIMARY KEY,
			email TEXT NOT NULL,
			cert_pem BYTEA NOT NULL,
			key_pem BYTEA NOT NULL,
			account_key BYTEA NOT NULL,
			account_url TEXT NOT NULL,
			issuer_type TEXT DEFAULT '',
			secret_name TEXT DEFAULT '',
			updated_at TIMESTAMP DEFAULT now(),
			enable_rotation BOOLEAN DEFAULT false,
			renew_before BIGINT DEFAULT 0
		);
		CREATE TABLE IF NOT EXISTS extension_configs (
            id SERIAL PRIMARY KEY,
            name TEXT UNIQUE NOT NULL,
            data JSONB NOT NULL,
            enabled BOOLEAN DEFAULT true,
            updated_at TIMESTAMP DEFAULT now()
        );`
}

func (p *PostgresStrategy) SaveCertificateSQL(ph []string) string {
	// ph[0]...ph[9] correspond to the 10 values
	return fmt.Sprintf(`
		INSERT INTO certificates (domain, email, cert_pem, key_pem, account_key, account_url, issuer_type, secret_name, updated_at, enable_rotation, renew_before)
		VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now(), %s, %s)
		ON CONFLICT (domain) DO UPDATE SET
			email = EXCLUDED.email,
			cert_pem = EXCLUDED.cert_pem,
			key_pem = EXCLUDED.key_pem,
			account_key = EXCLUDED.account_key,
			account_url = EXCLUDED.account_url,
			issuer_type = EXCLUDED.issuer_type,
			secret_name = EXCLUDED.secret_name,
			updated_at = now(),
			enable_rotation = EXCLUDED.enable_rotation,
			renew_before = EXCLUDED.renew_before`,
		ph[0], ph[1], ph[2], ph[3], ph[4], ph[5], ph[6], ph[7], ph[8], ph[9])
}

func (p *PostgresStrategy) SaveSecretSQL(ph []string) string {
	// ph[0] = name, ph[1] = data, ph[2] = domain
	return fmt.Sprintf(`
		INSERT INTO secrets (name, data, enabled, updated_at, domain)
		VALUES (%s, %s, true, now(), %s)
		ON CONFLICT (name) DO UPDATE SET data = %s, enabled = true, updated_at = now(), domain = %s`,
		ph[0], ph[1], ph[2], ph[1], ph[2]) // Note: $2, $3 are repeated for the update clause
}

func (p *PostgresStrategy) SaveClusterSQL(ph []string) string {
	// ph[0] = name, ph[1] = data
	return fmt.Sprintf(`
		INSERT INTO clusters (name, data, enabled, updated_at)
		VALUES (%s, %s, true, now())
		ON CONFLICT (name) DO UPDATE SET data = %s, enabled = true, updated_at = now()`,
		ph[0], ph[1], ph[1])
}

func (p *PostgresStrategy) SaveListenerSQL(ph []string) string {
	// ph[0] = name, ph[1] = data
	return fmt.Sprintf(`
		INSERT INTO listeners (name, data, enabled, updated_at)
		VALUES (%s, %s, true, now())
		ON CONFLICT (name) DO UPDATE SET data = %s, enabled = true, updated_at = now()`,
		ph[0], ph[1], ph[1])
}

func (p *PostgresStrategy) SaveExtensionConfigSQL(ph []string) string {
	// ph[0] = name, ph[1] = data
	return fmt.Sprintf(`
		INSERT INTO extension_configs (name, data, enabled, updated_at)
		VALUES (%s, %s, true, now())
		ON CONFLICT (name) DO UPDATE SET data = %s, enabled = true, updated_at = now()`,
		ph[0], ph[1], ph[1])
}

func (p *PostgresStrategy) DumpSelectFields(table string) string {
	if table == "secrets" {
		return "name, data, domain"
	}
	return "name, data"
}

func (p *PostgresStrategy) ScanRawRow(rows *sql.Rows, row *RawRow, table string) error {
	if table == "secrets" {
		// Postgres: 3 fields (name, JSONB data, domain)
		return rows.Scan(&row.Name, &row.Data)
	}
	// Postgres: 2 fields (name, JSONB data)
	return rows.Scan(&row.Name, &row.Data)
}

func (p *PostgresStrategy) RestoreRawRowSQL(table string) string {
	// Note: We use the strategy's placeholder method if needed, but here we hardcode $1, $2, $3 for clarity.
	if table == "secrets" {
		return `
			INSERT INTO secrets (name, data, enabled, updated_at, domain)
			VALUES ($1, $2, true, now(), $3)
			ON CONFLICT (name)
			DO UPDATE SET data = EXCLUDED.data, enabled = true, updated_at = now(), domain = EXCLUDED.domain`
	}
	// clusters or listeners (only needs $1 and $2 for name and data)
	return fmt.Sprintf(`
		INSERT INTO %s (name, data, enabled, updated_at)
		VALUES ($1, $2, true, now())
		ON CONFLICT (name)
		DO UPDATE SET data = EXCLUDED.data, enabled = true, updated_at = now()`, table)
}

func (p *PostgresStrategy) ClearTableSQL(table string) string {
	// Postgres preferred way to clear and reset IDs
	return fmt.Sprintf("TRUNCATE TABLE %s RESTART IDENTITY", table)
}