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

	"github.com/janpfeifer/gonb/gonbui"

	_ "modernc.org/sqlite"
)

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

// openCourseDB は、このコース共通の SQLite 接続を開く。
// GoNB はセル単位で実行されるため、DB は「1セル完結」（open→defer Close→操作）で使う。
// セルをまたいだ状態共有は Go 変数ではなく、同じパスのファイルを介して行う。
func openCourseDB(path string) *sql.DB {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	return db
}

const flatDBPath = "file:_02_4_normalization.db"

%%
// 非正規化テーブルをシードする（DROP→CREATE→INSERT を1セルにまとめ、再実行しても同じ初期状態に戻す）。
db := openCourseDB(flatDBPath)
defer db.Close()

db.Exec(`DROP TABLE IF EXISTS orders_flat`)
db.Exec(`CREATE TABLE orders_flat (
	order_id INTEGER PRIMARY KEY,
	customer_id INTEGER,
	customer_name TEXT,
	customer_email TEXT,
	product_id INTEGER,
	product_name TEXT,
	price INTEGER,
	qty INTEGER
)`)

seed := []struct {
	customerID          int
	customerName, email string
	productID           int
	productName          string
	price, qty          int
}{
	{1, "佐藤", "sato@example.com", 10, "マグカップ", 800, 2},
	{1, "佐藤", "sato@example.com", 20, "ノート", 300, 1},
	{1, "佐藤", "sato@example.com", 30, "ペン", 150, 5},
	{2, "鈴木", "suzuki@example.com", 10, "マグカップ", 800, 3},
	{2, "鈴木", "suzuki@example.com", 20, "ノート", 300, 2},
	{3, "田中", "tanaka@example.com", 30, "ペン", 150, 1},
}
for _, s := range seed {
	_, err := db.Exec(
		`INSERT INTO orders_flat (customer_id, customer_name, customer_email, product_id, product_name, price, qty)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		s.customerID, s.customerName, s.email, s.productID, s.productName, s.price, s.qty)
	if err != nil {
		panic(err)
	}
}
fmt.Println("✅ orders_flat をシードしました（6行）")

✅ orders_flat をシードしました（6行）

%%
db := openCourseDB(flatDBPath)
defer db.Close()

res, err := db.Exec(`UPDATE orders_flat SET customer_email = ? WHERE customer_id = ?`, "sato-new@example.com", 1)
if err != nil {
	panic(err)
}
n, _ := res.RowsAffected()
fmt.Printf("非正規化テーブルでの更新: %d 行が変更された（佐藤さんの注文は3件あるので3行）\n", n)

var dup int
db.QueryRow(`SELECT COUNT(*) - COUNT(DISTINCT customer_id) FROM orders_flat`).Scan(&dup)
fmt.Printf("customer_id の重複行数（全%d行のうち）: %d\n", 6, dup)

非正規化テーブルでの更新: 3 行が変更された（佐藤さんの注文は3件あるので3行）

customer_id の重複行数（全6行のうち）: 3

%%
db := openCourseDB(flatDBPath)
defer db.Close()

for _, ddl := range []string{
	`DROP TABLE IF EXISTS customers`,
	`DROP TABLE IF EXISTS products`,
	`DROP TABLE IF EXISTS order_items`,
	`CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, email TEXT)`,
	`CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT, price INTEGER)`,
	`CREATE TABLE order_items (order_id INTEGER PRIMARY KEY, customer_id INTEGER, product_id INTEGER, qty INTEGER)`,
} {
	if _, err := db.Exec(ddl); err != nil {
		panic(err)
	}
}

// orders_flat から重複を除いて分解して詰め直す（正規化のマイグレーション操作そのもの）。
db.Exec(`INSERT INTO customers (customer_id, name, email)
          SELECT DISTINCT customer_id, customer_name, customer_email FROM orders_flat`)
db.Exec(`INSERT INTO products (product_id, name, price)
          SELECT DISTINCT product_id, product_name, price FROM orders_flat`)
db.Exec(`INSERT INTO order_items (order_id, customer_id, product_id, qty)
          SELECT order_id, customer_id, product_id, qty FROM orders_flat`)

var custCount, prodCount, itemCount int
db.QueryRow(`SELECT COUNT(*) FROM customers`).Scan(&custCount)
db.QueryRow(`SELECT COUNT(*) FROM products`).Scan(&prodCount)
db.QueryRow(`SELECT COUNT(*) FROM order_items`).Scan(&itemCount)
fmt.Printf("customers=%d行 / products=%d行 / order_items=%d行\n", custCount, prodCount, itemCount)

customers=3行 / products=3行 / order_items=6行

%%
db := openCourseDB(flatDBPath)
defer db.Close()

res, err := db.Exec(`UPDATE customers SET email = ? WHERE customer_id = ?`, "sato-new2@example.com", 1)
if err != nil {
	panic(err)
}
n, _ := res.RowsAffected()
fmt.Printf("正規化後のテーブルでの更新: %d 行が変更された\n", n)

var dup int
db.QueryRow(`SELECT COUNT(*) - COUNT(DISTINCT customer_id) FROM customers`).Scan(&dup)
fmt.Printf("customers テーブルでの customer_id 重複行数: %d\n", dup)

正規化後のテーブルでの更新: 1 行が変更された

customers テーブルでの customer_id 重複行数: 0

func renderComparison(rows [][4]string) string {
	var b strings.Builder
	b.WriteString(`<table border="1" cellpadding="4" style="border-collapse:collapse">`)
	b.WriteString(`<tr><th>項目</th><th>正規化前(orders_flat)</th><th>正規化後(customers等)</th><th>観察点</th></tr>`)
	for _, r := range rows {
		b.WriteString(fmt.Sprintf(`<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>`, r[0], r[1], r[2], r[3]))
	}
	b.WriteString(`</table>`)
	return b.String()
}

%%
gonbui.DisplayHTML(renderComparison([][4]string{
	{"メール変更で影響を受ける行数", "3行（佐藤さんの注文数ぶん重複更新）", "1行（customersに1行しかない）", "更新異常が消えた"},
	{"customer_id の重複行数", "3行（6行中、顧客は3人なので3重複）", "0行（customersはPKで1顧客1行）", "重複が構造的に無くなった"},
}))
gonbui.Sync()

func customerOrderRowCount(db *sql.DB, customerID int) (int, error)

// YOUR CODE HERE
// func customerOrderRowCount(db *sql.DB, customerID int) (int, error) を実装してください。
// （未実装のままチェックセルを実行すると「未回答」と表示されます）
func customerOrderRowCount(db *sql.DB, customerID int) (int, error) {
	return -1, 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))
}

%%
// このチェックは SELECT のみで DB を変更しないため、何度実行しても同じ結果になる。
db := openCourseDB(flatDBPath)
defer db.Close()

n1, err1 := customerOrderRowCount(db, 1)
if errors.Is(err1, ErrUnanswered) {
	fmt.Println("⚠️ 未回答: 練習問題を解いてから、このセルを再度実行してください")
} else {
	mustEqual(err1, nil, "customer_id=1 はエラーなし")
	mustEqual(n1, 3, "佐藤さん(customer_id=1)は3行")

	n2, err2 := customerOrderRowCount(db, 2)
	mustEqual(err2, nil, "customer_id=2 はエラーなし")
	mustEqual(n2, 2, "鈴木さん(customer_id=2)は2行")

	n3, err3 := customerOrderRowCount(db, 999)
	mustEqual(err3, nil, "存在しないIDはエラーではない")
	mustEqual(n3, 0, "存在しないIDは0行")

	fmt.Println("🎉 すべてのチェックが通りました")
}

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

02.4 データモデリングと正規化 — 更新異常を消す設計¶

1. なぜ更新異常が起きるのか¶

2. 非自明な点: 2NF と 3NF の違い¶

3. 更新異常を実測する（正規化前）¶

4. 正規化する: customers / products / order_items に分割¶

5. 更新異常を実測する（正規化後）¶

6. 正規化前後を並べて比較する¶

7. 直感・類推: 名簿を1冊にまとめるか、教室ごとに配るか¶

練習問題 2.4: 影響行数を数える関数を実装しよう¶

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

まとめ¶

order_id	customer_id	customer_name	customer_email	product_id	product_name	price	qty
1	1	佐藤	sato@example.com	10	マグカップ	800	2
2	1	佐藤	sato@example.com	20	ノート	300	1
3	2	鈴木	suzuki@example.com	10	マグカップ	800	3

項目	正規化前(orders_flat)	正規化後(customers等)	観察点
メール変更で影響を受ける行数	3行（佐藤さんの注文数ぶん重複更新）	1行（customersに1行しかない）	更新異常が消えた
customer_id の重複行数	3行（6行中、顧客は3人なので3重複）	0行（customersはPKで1顧客1行）	重複が構造的に無くなった