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,
			full_chain_pem BLOB DEFAULT '', -- Added: full_chain_pem with a default empty blob for backward compatibility
			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
		);
		-- Add ALTER TABLE statement for existing databases to add the full_chain_pem column
		-- This must be separate as ALTER TABLE IF NOT EXISTS ADD COLUMN is not universally supported in SQLite for BLOB.
		-- A simpler approach is to just try adding and ignore the error if it exists, as InitSchema does.
		-- However, for robustness, we check for column existence first.
		PRAGMA foreign_keys = OFF; -- Disable foreign key checks for schema modification
		INSERT OR IGNORE INTO sqlite_master (type, name, tbl_name, rootpage, sql) VALUES ('table', 'certificates', 'certificates', 0, 'CREATE TABLE certificates (domain TEXT PRIMARY KEY, email TEXT NOT NULL, cert_pem BLOB NOT NULL, key_pem BLOB NOT NULL, full_chain_pem BLOB DEFAULT '', 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)');
		ALTER TABLE certificates ADD COLUMN full_chain_pem BLOB DEFAULT '';
		PRAGMA foreign_keys = ON; -- Re-enable foreign key checks

		CREATE TABLE IF NOT EXISTS extension_configs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            data TEXT NOT NULL,
            enabled BOOLEAN DEFAULT 1,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );`
}

func (s *SQLiteStrategy) SaveCertificateSQL(ph []string) string {
	// ph should now have 11 elements for all fields including full_chain_pem
	return `
		INSERT INTO certificates (domain, email, cert_pem, key_pem, full_chain_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,
			full_chain_pem = excluded.full_chain_pem, -- Added: full_chain_pem update
			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) SaveExtensionConfigSQL(ph []string) string {
	// ph[0] = name, ph[1] = data
	return `
        INSERT INTO extension_configs (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" // MODIFIED: Include domain for secrets
	}
	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 fmt.Sprintf(`
			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`,
		) // MODIFIED: Handle domain for secrets
	}
	// 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)
}