package storage

import (
	"database/sql"
	"fmt"
)

// ---------------------------
// SQLite Strategy
// ---------------------------

type SQLiteStrategy struct{}

func (s *SQLiteStrategy) DriverName() string { return "sqlite" }

// Placeholder returns ?.
func (s *SQLiteStrategy) Placeholder(n int) string { return "?" }

// GetTimeNow returns the SQLite function for current timestamp.
func (s *SQLiteStrategy) GetTimeNow() string { return "CURRENT_TIMESTAMP" }

// GetTrueValue returns the integer representation.
func (s *SQLiteStrategy) GetTrueValue() string { return "1" }

// GetFalseValue returns the integer representation.
func (s *SQLiteStrategy) GetFalseValue() string { return "0" }

func (s *SQLiteStrategy) InitSchemaSQL() string {
	return `
		CREATE TABLE IF NOT EXISTS clusters (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT UNIQUE NOT NULL,
			data TEXT NOT NULL,
			enabled BOOLEAN DEFAULT 1,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		);
		CREATE TABLE IF NOT EXISTS listeners (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT UNIQUE NOT NULL,
			data TEXT NOT NULL,
			enabled BOOLEAN DEFAULT 1,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		);
		CREATE TABLE IF NOT EXISTS secrets (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT UNIQUE NOT NULL,
			data TEXT NOT NULL,
			enabled BOOLEAN DEFAULT 1,
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			domain TEXT NULL
		);
		CREATE TABLE IF NOT EXISTS certificates (
			domain TEXT PRIMARY KEY,
			email TEXT NOT NULL,
			cert_pem BLOB NOT NULL,
			key_pem BLOB NOT NULL,
			account_key BLOB NOT NULL,
			account_url TEXT NOT NULL,
			issuer_type TEXT DEFAULT '',
			secret_name TEXT DEFAULT '',
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			enable_rotation BOOLEAN DEFAULT 0,
			renew_before INTEGER DEFAULT 0
		);`
}

func (s *SQLiteStrategy) SaveCertificateSQL(ph []string) string {
	return `
		INSERT INTO certificates (domain, email, cert_pem, key_pem, account_key, account_url, issuer_type, secret_name, updated_at, enable_rotation, renew_before)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, ?)
		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 = CURRENT_TIMESTAMP,
			enable_rotation = excluded.enable_rotation,
			renew_before = excluded.renew_before`
}

func (s *SQLiteStrategy) SaveSecretSQL(ph []string) string {
	return `
		INSERT INTO secrets (name, data, enabled, updated_at, domain)
		VALUES (?, ?, 1, CURRENT_TIMESTAMP, ?)
		ON CONFLICT(name) DO UPDATE SET data=excluded.data, enabled=1, updated_at=CURRENT_TIMESTAMP, domain=excluded.domain`
}

func (s *SQLiteStrategy) SaveClusterSQL(ph []string) string {
	return `
		INSERT INTO clusters (name, data, enabled, updated_at)
		VALUES (?, ?, 1, CURRENT_TIMESTAMP)
		ON CONFLICT(name) DO UPDATE SET data=excluded.data, enabled=1, updated_at=CURRENT_TIMESTAMP`
}

func (s *SQLiteStrategy) SaveListenerSQL(ph []string) string {
	return `
		INSERT INTO listeners (name, data, enabled, updated_at)
		VALUES (?, ?, 1, CURRENT_TIMESTAMP)
		ON CONFLICT(name) DO UPDATE SET data=excluded.data, enabled=1, updated_at=CURRENT_TIMESTAMP`
}

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

func (s *SQLiteStrategy) ScanRawRow(rows *sql.Rows, row *RawRow, table string) error {
	var dataStr string
	if table == "secrets" {
		// SQLite: 3 fields (name, TEXT data, domain)
		if err := rows.Scan(&row.Name, &dataStr, &row.Domain); err != nil {
			return err
		}
	} else {
		// SQLite: 2 fields (name, TEXT data)
		if err := rows.Scan(&row.Name, &dataStr); err != nil {
			return err
		}
	}
	row.Data = []byte(dataStr)
	return nil
}

func (s *SQLiteStrategy) RestoreRawRowSQL(table string) string {
	if table == "secrets" {
		return `
			INSERT INTO secrets (name, data, enabled, updated_at, domain)
			VALUES (?, ?, 1, CURRENT_TIMESTAMP, ?)
			ON CONFLICT(name)
			DO UPDATE SET data=excluded.data, enabled=1, updated_at=CURRENT_TIMESTAMP, domain=excluded.domain`
	}
	// clusters or listeners
	return fmt.Sprintf(`
		INSERT INTO %s (name, data, enabled, updated_at)
		VALUES (?, ?, 1, CURRENT_TIMESTAMP)
		ON CONFLICT(name)
		DO UPDATE SET data=excluded.data, enabled=1, updated_at=CURRENT_TIMESTAMP`, table)
}

func (s *SQLiteStrategy) ClearTableSQL(table string) string {
	// SQLite safe way to clear
	return fmt.Sprintf("DELETE FROM %s", table)
}