database

var CannedQueries = map[string]CannedQuery{
	"sizes": {
		Name:        "sizes",
		Description: "Table sizes and row counts",
		SQL: map[string]string{
			"postgres": `SELECT
	schemaname || '.' || relname AS "table",
	pg_size_pretty(pg_total_relation_size(relid)) AS "total_size",
	n_live_tup AS "estimated_rows"
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;`,
			"mysql": `SELECT
	table_name AS 'table',
	CONCAT(ROUND(data_length / 1024, 2), ' KB') AS 'data_size',
	table_rows AS 'estimated_rows'
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;`,
		},
	},
	"recent": {
		Name:        "recent",
		Description: "Last 10 rows from each user table",
		SQL: map[string]string{

			"postgres": `SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name;`,
			"mysql": `SELECT table_name FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'
ORDER BY table_name;`,
		},
	},
	"missing-indexes": {
		Name:        "missing-indexes",
		Description: "Tables with no non-primary indexes (potential performance risk)",
		SQL: map[string]string{
			"postgres": `SELECT
	t.tablename AS "table",
	COALESCE(idx.index_count, 0) AS "index_count",
	CASE WHEN COALESCE(idx.index_count, 0) = 0
		THEN '⚠️  No indexes'
		ELSE '✓'
	END AS "status"
FROM pg_tables t
LEFT JOIN (
	SELECT tablename, COUNT(*) AS index_count
	FROM pg_indexes
	WHERE schemaname = 'public'
	GROUP BY tablename
) idx ON t.tablename = idx.tablename
WHERE t.schemaname = 'public'
ORDER BY COALESCE(idx.index_count, 0) ASC, t.tablename;`,
			"mysql": `SELECT
	t.table_name AS 'table',
	COALESCE(idx.index_count, 0) AS 'index_count',
	CASE WHEN COALESCE(idx.index_count, 0) = 0
		THEN 'No indexes'
		ELSE 'OK'
	END AS 'status'
FROM information_schema.tables t
LEFT JOIN (
	SELECT table_name, COUNT(DISTINCT index_name) AS index_count
	FROM information_schema.statistics
	WHERE table_schema = DATABASE()
	GROUP BY table_name
) idx ON t.table_name = idx.table_name
WHERE t.table_schema = DATABASE()
ORDER BY COALESCE(idx.index_count, 0) ASC, t.table_name;`,
		},
	},
	"nulls": {
		Name:        "nulls",
		Description: "Columns with high NULL ratios for a specific table",
		SQL: map[string]string{

			"postgres": `SELECT
	attname AS "column",
	n_live_tup AS "total_rows",
	null_frac AS "null_ratio",
	CASE
		WHEN null_frac > 0.5 THEN '⚠️  >50%% NULLs'
		WHEN null_frac > 0.1 THEN '~' || ROUND(null_frac * 100) || '%%'
		ELSE '✓ low'
	END AS "status"
FROM pg_stats
WHERE schemaname = 'public' AND tablename = '%s'
ORDER BY null_frac DESC;`,
			"mysql": `SELECT
	column_name AS 'column',
	is_nullable AS 'nullable',
	column_type AS 'type',
	column_default AS 'default_value'
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = '%s'
ORDER BY ordinal_position;`,
		},
	},
}

var Registry = map[string]Engine{
	"postgres": {
		Name:         "PostgreSQL",
		Image:        "docker.io/library/postgres:16-alpine",
		DefaultPort:  5432,
		InternalPort: 5432,
		VolumePath:   "/var/lib/postgresql/data",
		Env: map[string]string{
			"POSTGRES_USER":     "devx",
			"POSTGRES_PASSWORD": "devx",
			"POSTGRES_DB":       "devx",
		},
		ReadyLog:      "database system is ready to accept connections",
		ConnStringFmt: "postgresql://%s:%s@localhost:%d/%s",
	},
	"redis": {
		Name:          "Redis",
		Image:         "docker.io/library/redis:7-alpine",
		DefaultPort:   6379,
		InternalPort:  6379,
		VolumePath:    "/data",
		Env:           map[string]string{},
		ReadyLog:      "Ready to accept connections",
		ConnStringFmt: "redis://localhost:%d",
	},
	"mysql": {
		Name:         "MySQL",
		Image:        "docker.io/library/mysql:8",
		DefaultPort:  3306,
		InternalPort: 3306,
		VolumePath:   "/var/lib/mysql",
		Env: map[string]string{
			"MYSQL_ROOT_PASSWORD": "devx",
			"MYSQL_DATABASE":      "devx",
			"MYSQL_USER":          "devx",
			"MYSQL_PASSWORD":      "devx",
		},
		ReadyLog:      "ready for connections",
		ConnStringFmt: "mysql://%s:%s@localhost:%d/%s",
	},
	"mongo": {
		Name:         "MongoDB",
		Image:        "docker.io/library/mongo:7",
		DefaultPort:  27017,
		InternalPort: 27017,
		VolumePath:   "/data/db",
		Env: map[string]string{
			"MONGO_INITDB_ROOT_USERNAME": "devx",
			"MONGO_INITDB_ROOT_PASSWORD": "devx",
		},
		ReadyLog:      "Waiting for connections",
		ConnStringFmt: "mongodb://%s:%s@localhost:%d",
	},
}