package main

import (
	"context"
	"encoding/json"
	"os"
	"path/filepath"
	"time"
)

// SELECT-only diagnostics. Never mix EXPLAIN instrumentation with HTTP timings.
func queryPlans(ctx context.Context, dsn, output string) error {
	c, err := openDB(ctx, dsn)
	if err != nil {
		return err
	}
	defer c.Close(ctx)
	if err = guard(ctx, c); err != nil {
		return err
	}
	queries := map[string]string{
		"reference_post_count":    "SELECT count(*) FROM blog_post WHERE author_id=1",
		"reference_comment_count": "SELECT count(*) FROM blog_comment WHERE author_id=1",
		"go_user":                 "SELECT u.id,u.username,u.display_name,u.email,u.bio,(SELECT count(*) FROM blog_post p WHERE p.author_id=u.id),(SELECT count(*) FROM blog_comment c WHERE c.author_id=u.id) FROM blog_user u WHERE u.id=1 LIMIT 21",
		"go_posts":                "SELECT p.id,p.title,p.view_count,p.created_at,u.id,u.username,u.display_name FROM blog_post p JOIN blog_user u ON u.id=p.author_id WHERE p.is_published ORDER BY p.created_at DESC,p.id",
		"go_comments":             "SELECT c.id,c.body,c.created_at,u.id,u.username,u.display_name FROM blog_comment c JOIN blog_user u ON u.id=c.author_id WHERE c.post_id=1 ORDER BY c.created_at,c.id",
	}
	entries := map[string]any{}
	for name, sql := range queries {
		var plan json.RawMessage
		if err = c.QueryRow(ctx, "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) "+sql).Scan(&plan); err != nil {
			return err
		}
		entries[name] = map[string]any{"sql": sql, "plan": plan}
	}
	var rows json.RawMessage
	if err = c.QueryRow(ctx, `SELECT json_build_object('users',(SELECT count(*) FROM blog_user),'posts',(SELECT count(*) FROM blog_post),'comments',(SELECT count(*) FROM blog_comment))`).Scan(&rows); err != nil {
		return err
	}
	raw, err := json.MarshalIndent(map[string]any{"diagnostic_only": true, "captured_at": time.Now().UTC(), "rows": rows, "queries": entries}, "", "  ")
	if err != nil {
		return err
	}
	if err = os.MkdirAll(filepath.Dir(output), 0755); err != nil {
		return err
	}
	return os.WriteFile(output, append(raw, '\n'), 0644)
}