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_1_relational.db"

%%
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,
	customer_address TEXT,
	product_name TEXT,
	price INTEGER,
	qty INTEGER
)`)

seed := []struct {
	customerID                       int
	customerName, email, address     string
	productName                      string
	price, qty                       int
}{
	{1, "佐藤", "sato@example.com", "東京都渋谷区1-1", "マグカップ", 800, 2},
	{1, "佐藤", "sato@example.com", "東京都渋谷区1-1", "ノート", 300, 1},
	{1, "佐藤", "sato@example.com", "東京都渋谷区1-1", "ペン", 150, 5},
	{2, "鈴木", "suzuki@example.com", "大阪府大阪市2-2", "マグカップ", 800, 3},
	{2, "鈴木", "suzuki@example.com", "大阪府大阪市2-2", "ノート", 300, 2},
	{3, "田中", "tanaka@example.com", "愛知県名古屋市3-3", "ペン", 150, 1},
}
for _, s := range seed {
	_, err := db.Exec(
		`INSERT INTO orders_flat (customer_id, customer_name, customer_email, customer_address, product_name, price, qty)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		s.customerID, s.customerName, s.email, s.address, s.productName, s.price, s.qty)
	if err != nil {
		panic(err)
	}
}
fmt.Println("✅ orders_flat をシードしました（6行、顧客3人ぶん）")

✅ orders_flat をシードしました（6行、顧客3人ぶん）

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

var total, distinctCustomers int
db.QueryRow(`SELECT COUNT(*) FROM orders_flat`).Scan(&total)
db.QueryRow(`SELECT COUNT(DISTINCT customer_id) FROM orders_flat`).Scan(&distinctCustomers)

fmt.Printf("orders_flat の全行数: %d 行\n", total)
fmt.Printf("customer_id の異なり数（実際の顧客数）: %d 人\n", distinctCustomers)
fmt.Printf("重複行数（同じ顧客の情報が複数行に書かれている数）: %d 行\n", total-distinctCustomers)

orders_flat の全行数: 6 行
customer_id の異なり数（実際の顧客数）: 3 人
重複行数（同じ顧客の情報が複数行に書かれている数）: 3 行

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

res, err := db.Exec(
	`UPDATE orders_flat SET customer_address = ? WHERE customer_id = ?`,
	"東京都新宿区9-9", 1,
)
if err != nil {
	panic(err)
}
n, _ := res.RowsAffected()
fmt.Printf("佐藤さんの住所変更で更新された行数: %d 行\n", n)
fmt.Println("→ 佐藤さんは3回注文しているので、たった1つの事実（住所）を直すのに3行触る必要があった")

佐藤さんの住所変更で更新された行数: 3 行
→ 佐藤さんは3回注文しているので、たった1つの事実（住所）を直すのに3行触る必要があった

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

%%
gonbui.DisplayHTML(renderAnomalySummary([][3]string{
	{"orders_flat の全行数", "6 行", "顧客は3人だけなのに行数はそれより多い"},
	{"customer_id の重複行数", "3 行", "同じ顧客の情報が複数行にコピーされている"},
	{"住所変更(customer_id=1)で更新された行数", "3 行", "1つの事実を直すのに3行触る必要があった"},
}))
gonbui.Sync()

func renderKeyCheck(rows [][3]string) string {
	var b strings.Builder
	b.WriteString(`<table border="1" cellpadding="4" style="border-collapse:collapse">`)
	b.WriteString(`<tr><th>列名</th><th>全行数 / 異なる値の数</th><th>候補キーか</th></tr>`)
	for _, r := range rows {
		b.WriteString(fmt.Sprintf(`<tr><td>%s</td><td>%s</td><td>%s</td></tr>`, r[0], r[1], r[2]))
	}
	b.WriteString(`</table>`)
	return b.String()
}

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

columns := []string{"order_id", "customer_id", "customer_email"}
var rows [][3]string
for _, col := range columns {
	query := fmt.Sprintf(`SELECT COUNT(*), COUNT(DISTINCT %s) FROM orders_flat`, col)
	var total, distinct int
	if err := db.QueryRow(query).Scan(&total, &distinct); err != nil {
		panic(err)
	}
	verdict := "❌ 候補キーではない（重複あり）"
	if total == distinct {
		verdict = "✅ 候補キーの条件を満たす"
	}
	rows = append(rows, [3]string{col, fmt.Sprintf("%d / %d", total, distinct), verdict})
}
gonbui.DisplayHTML(renderKeyCheck(rows))
gonbui.Sync()

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

// WHERE customer_id = ? が「選択」、SELECT product_name, price が「射影」に対応する。
rows, err := db.Query(
	`SELECT product_name, price FROM orders_flat WHERE customer_id = ?`, 1,
)
if err != nil {
	panic(err)
}
defer rows.Close()

fmt.Println("佐藤さんの注文（商品名・価格のみ）:")
for rows.Next() {
	var name string
	var price int
	if err := rows.Scan(&name, &price); err != nil {
		panic(err)
	}
	fmt.Printf("  - %s: %d円\n", name, price)
}

佐藤さんの注文（商品名・価格のみ）:
  - マグカップ: 800円
  - ノート: 300円
  - ペン: 150円

func isCandidateKey(db *sql.DB, column string) (bool, error)

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

ok1, err1 := isCandidateKey(db, "order_id")
if errors.Is(err1, ErrUnanswered) {
	fmt.Println("⚠️ 未回答: 練習問題を解いてから、このセルを再度実行してください")
} else {
	mustEqual(err1, nil, "order_id はエラーなし")
	mustEqual(ok1, true, "order_id は候補キーの条件を満たす（重複なし）")

	ok2, err2 := isCandidateKey(db, "customer_id")
	mustEqual(err2, nil, "customer_id はエラーなし")
	mustEqual(ok2, false, "customer_id は候補キーではない（重複あり）")

	ok3, err3 := isCandidateKey(db, "customer_email")
	mustEqual(err3, nil, "customer_email はエラーなし")
	mustEqual(ok3, false, "customer_email も候補キーではない（重複あり）")

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

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

02.1 リレーショナルモデル — テーブルとキーの基礎¶

1. リレーション（関係）とキー¶

2. 非自明な点: 「今のデータで重複していない」だけでは候補キーと言い切れない¶

3. 動かしてみる: 顧客情報と注文情報を1つのテーブルに詰め込む¶

4. 重複を実測する¶

5. 更新異常を実測する（住所変更）¶

6. キーの候補を実データで検証する¶

7. 関係代数の考え方: 選択・射影・結合¶

8. 直感・類推: 同じチラシを配ってから誤字に気づく¶

練習問題 2.1: isCandidateKey（候補キー判定）を実装しよう¶

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

まとめ¶

項目	実測値	観察点
orders_flat の全行数	6 行	顧客は3人だけなのに行数はそれより多い
customer_id の重複行数	3 行	同じ顧客の情報が複数行にコピーされている
住所変更(customer_id=1)で更新された行数	3 行	1つの事実を直すのに3行触る必要があった

列名	全行数 / 異なる値の数	候補キーか
order_id	6 / 6	✅ 候補キーの条件を満たす
customer_id	6 / 3	❌ 候補キーではない（重複あり）
customer_email	6 / 3	❌ 候補キーではない（重複あり）