import (
	"database/sql"
	"errors"
	"fmt"
	"strings"

	_ "modernc.org/sqlite"
	"github.com/janpfeifer/gonb/gonbui"
)

// ErrUnanswered は、練習問題が未回答のときにプレースホルダ関数が返す特別なエラー。
var ErrUnanswered = errors.New("未回答: この関数はまだ実装されていません")

func openCourseDB(path string) *sql.DB {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	return db
}

func renderTable(headers []string, rows [][]string) string {
	var b strings.Builder
	b.WriteString(`<table border="1" cellpadding="4" style="border-collapse:collapse"><tr>`)
	for _, h := range headers {
		b.WriteString(fmt.Sprintf("<th>%s</th>", h))
	}
	b.WriteString("</tr>")
	for _, row := range rows {
		b.WriteString("<tr>")
		for _, c := range row {
			b.WriteString(fmt.Sprintf("<td>%s</td>", c))
		}
		b.WriteString("</tr>")
	}
	b.WriteString("</table>")
	return b.String()
}

%%
db := openCourseDB("_02_3_sql_aggregation_joins.db")
defer db.Close()

mustExec := func(query string, args ...any) {
	if _, err := db.Exec(query, args...); err != nil {
		panic(err)
	}
}

mustExec(`DROP TABLE IF EXISTS orders`)
mustExec(`DROP TABLE IF EXISTS customers`)
mustExec(`DROP TABLE IF EXISTS products`)

mustExec(`CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)`)
mustExec(`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)`)
mustExec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, product_id INTEGER, quantity INTEGER)`)

mustExec(`INSERT INTO customers (id, name) VALUES (1,'tanaka'), (2,'suzuki'), (3,'sato'), (4,'sato')`)
mustExec(`INSERT INTO products (id, name, price) VALUES (1,'keyboard',3000), (2,'mouse',1500), (3,'monitor',20000), (4,'mouse',1800)`)
mustExec(`INSERT INTO orders (id, customer_id, product_id, quantity) VALUES
	(1,1,1,1),
	(2,1,2,2),
	(3,2,3,1),
	(4,3,2,1),
	(5,2,1,3),
	(6,99,3,1)`)

fmt.Println("✅ シード完了: customers=4行 / products=4行 / orders=6行（うちid=6は存在しない顧客id=99を参照）")

✅ シード完了: customers=4行 / products=4行 / orders=6行（うちid=6は存在しない顧客id=99を参照）

%%
db := openCourseDB("_02_3_sql_aggregation_joins.db")
defer db.Close()

var correctByID, wrongByName int
if err := db.QueryRow(`SELECT COUNT(*) FROM orders o JOIN products p ON p.id = o.product_id`).Scan(&correctByID); err != nil {
	panic(err)
}
if err := db.QueryRow(`SELECT COUNT(*) FROM orders o JOIN products p ON p.name = (SELECT name FROM products WHERE id = o.product_id)`).Scan(&wrongByName); err != nil {
	panic(err)
}

fmt.Println("正しいJOIN（product_id = products.id）の行数:", correctByID)
fmt.Println("誤ったJOIN（商品名で結合）の行数:", wrongByName)

gonbui.DisplayHTML(renderTable(
	[]string{"結合方法", "結合キー", "結果の行数"},
	[][]string{
		{"正しい", "product_id = products.id（ユニークキー）", fmt.Sprint(correctByID)},
		{"誤り", "products.name（'mouse' が2つあり重複）", fmt.Sprint(wrongByName)},
	},
))
gonbui.Sync()

正しいJOIN（product_id = products.id）の行数: 6
誤ったJOIN（商品名で結合）の行数: 8

%%
db := openCourseDB("_02_3_sql_aggregation_joins.db")
defer db.Close()

var totalOrders, innerCount, leftCount int
db.QueryRow(`SELECT COUNT(*) FROM orders`).Scan(&totalOrders)
if err := db.QueryRow(`SELECT COUNT(*) FROM orders o JOIN customers c ON o.customer_id = c.id`).Scan(&innerCount); err != nil {
	panic(err)
}
if err := db.QueryRow(`SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.id`).Scan(&leftCount); err != nil {
	panic(err)
}

gonbui.DisplayHTML(renderTable(
	[]string{"", "行数"},
	[][]string{
		{"orders テーブルの全行数", fmt.Sprint(totalOrders)},
		{"INNER JOIN customers（存在しない顧客id=99の注文は消える）", fmt.Sprint(innerCount)},
		{"LEFT JOIN customers（消えない。customer名はNULLになる）", fmt.Sprint(leftCount)},
	},
))
gonbui.Sync()

%%
db := openCourseDB("_02_3_sql_aggregation_joins.db")
defer db.Close()

rows, err := db.Query(`
	SELECT c.id, c.name, SUM(p.price * o.quantity) AS total, COUNT(*) AS cnt
	FROM orders o
	JOIN products p ON p.id = o.product_id
	JOIN customers c ON c.id = o.customer_id
	GROUP BY c.id, c.name
	HAVING SUM(p.price * o.quantity) >= 5000
	ORDER BY total DESC
`)
if err != nil {
	panic(err)
}
defer rows.Close()

var tableRows [][]string
for rows.Next() {
	var id, total, cnt int
	var name string
	if err := rows.Scan(&id, &name, &total, &cnt); err != nil {
		panic(err)
	}
	tableRows = append(tableRows, []string{fmt.Sprint(id), name, fmt.Sprintf("%d円", total), fmt.Sprint(cnt)})
}

gonbui.DisplayHTML(renderTable([]string{"顧客ID", "名前", "売上合計", "注文件数"}, tableRows))
gonbui.Sync()

func TopCustomersBySpend(db *sql.DB, minTotal int) ([]CustomerSpend, error)

type CustomerSpend struct {
	CustomerID int
	Name       string
	Total      int
	OrderCount int
}

// YOUR CODE HERE
// TopCustomersBySpend を実装してください。
// （未実装のままチェックセルを実行すると「未回答」と表示されます）
func TopCustomersBySpend(db *sql.DB, minTotal int) ([]CustomerSpend, error) {
	return nil, ErrUnanswered
}

import "reflect"

func mustEqual(got, want any, name string) {
	if reflect.DeepEqual(got, want) {
		fmt.Printf("✅ Passed: %s\n", name)
		return
	}
	panic(fmt.Sprintf("❌ %s\n  got  = %v (%T)\n  want = %v (%T)", name, got, got, want, want))
}

%%
db := openCourseDB("_02_3_sql_aggregation_joins.db")
defer db.Close()

result, err := TopCustomersBySpend(db, 5000)
if errors.Is(err, ErrUnanswered) {
	fmt.Println("⚠️ 未回答: 練習問題を解いてから、このセルを再度実行してください")
} else {
	if err != nil {
		panic(err)
	}
	mustEqual(len(result), 2, "5000円以上の顧客は2人")
	mustEqual(result[0], CustomerSpend{CustomerID: 2, Name: "suzuki", Total: 29000, OrderCount: 2}, "1位はsuzuki（29000円）")
	mustEqual(result[1], CustomerSpend{CustomerID: 1, Name: "tanaka", Total: 6000, OrderCount: 2}, "2位はtanaka（6000円）")
	fmt.Println("🎉 すべてのチェックが通りました")
}

⚠️ 未回答: 練習問題を解いてから、このセルを再度実行してください

02.3 集計とJOIN — データを結合して意味を取り出す¶

非自明ポイント: JOIN は「間違えても動いてしまう」¶

1. データを用意する¶

2. 罠1: ユニークでないキーで結合すると行が膨らむ¶

3. 罠2: INNER JOIN は参照先が無いと行ごと消える¶

4. GROUP BY と HAVING で集計する¶

5. 直感・類推: 名簿の名寄せ¶

練習問題 2.3: TopCustomersBySpend（売上上位の顧客）を実装しよう¶

チェックのためのヘルパー¶

まとめ¶

結合方法	結合キー	結果の行数
正しい	product_id = products.id（ユニークキー）	6
誤り	products.name（'mouse' が2つあり重複）	8

	行数
orders テーブルの全行数	6
INNER JOIN customers（存在しない顧客id=99の注文は消える）	5
LEFT JOIN customers（消えない。customer名はNULLになる）	6

顧客ID	名前	売上合計	注文件数
2	suzuki	29000円	2
1	tanaka	6000円	2