import (
	"bytes"
	"database/sql"
	"errors"
	"fmt"
	"image/color"
	"strings"
	"time"

	"github.com/janpfeifer/gonb/gonbui"
	"gonum.org/v1/plot"
	"gonum.org/v1/plot/plotter"
	"gonum.org/v1/plot/vg"

	_ "modernc.org/sqlite"
)

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

// openCourseDB は、このコース共通の SQLite 接続を開く。
// GoNB はセル単位で実行されるため、DB は「1セル完結」（open→defer Close→操作）で使う。
func openCourseDB(path string) *sql.DB {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	return db
}

const dbPath = "file:_02_5_transactions_indexes.db"

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

db.Exec(`DROP TABLE IF EXISTS accounts`)
db.Exec(`CREATE TABLE accounts (id INTEGER PRIMARY KEY, code TEXT UNIQUE, balance INTEGER)`)
db.Exec(`INSERT INTO accounts (code, balance) VALUES ('A', 1000), ('B', 500)`)

var count int
db.QueryRow(`SELECT COUNT(*) FROM accounts`).Scan(&count)
fmt.Println("✅ accounts をシードしました。行数:", count)

✅ accounts をシードしました。行数: 2

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

tx, err := db.Begin()
if err != nil {
	panic(err)
}
defer tx.Rollback() // Commit 後は no-op なので安全網として常に書く

if _, err := tx.Exec(`UPDATE accounts SET balance = balance - 100 WHERE code = 'A'`); err != nil {
	panic(err)
}
if _, err := tx.Exec(`UPDATE accounts SET balance = balance + 100 WHERE code = 'B'`); err != nil {
	panic(err)
}
if err := tx.Commit(); err != nil {
	panic(err)
}

var balA, balB int
db.QueryRow(`SELECT balance FROM accounts WHERE code = 'A'`).Scan(&balA)
db.QueryRow(`SELECT balance FROM accounts WHERE code = 'B'`).Scan(&balB)
fmt.Printf("Commit 後: A=%d, B=%d（合計は変わらず1500）\n", balA, balB)

Commit 後: A=900, B=600（合計は変わらず1500）

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

var balABefore int
db.QueryRow(`SELECT balance FROM accounts WHERE code = 'A'`).Scan(&balABefore)

tx, err := db.Begin()
if err != nil {
	panic(err)
}
defer tx.Rollback()

// 1つ目のUPDATEは成功する。
if _, err := tx.Exec(`UPDATE accounts SET balance = balance - 100 WHERE code = 'A'`); err != nil {
	panic(err)
}

var midBalA int
tx.QueryRow(`SELECT balance FROM accounts WHERE code = 'A'`).Scan(&midBalA)
fmt.Println("トランザクション内（Commit前）のA残高:", midBalA, "← すでに減っている")

// 2つ目でUNIQUE制約違反を起こす（'A' は既に存在する）。
_, insErr := tx.Exec(`INSERT INTO accounts (code, balance) VALUES ('A', 0)`)
fmt.Println("INSERT の結果（UNIQUE違反を期待）:", insErr)

if insErr != nil {
	tx.Rollback()
	fmt.Println("→ Rollback しました")
} else {
	tx.Commit()
}

var balAAfter int
db.QueryRow(`SELECT balance FROM accounts WHERE code = 'A'`).Scan(&balAAfter)
fmt.Printf("Rollback後のA残高: %d（Before=%d と同じはず）\n", balAAfter, balABefore)

トランザクション内（Commit前）のA残高: 800 ← すでに減っている
INSERT の結果（UNIQUE違反を期待）: constraint failed: UNIQUE constraint failed: accounts.code (2067)

→ Rollback しました

Rollback後のA残高: 900（Before=900 と同じはず）

func renderTxComparison(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>Aの残高</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(renderTxComparison([][3]string{
	{"Commit（3節）", "900", "反映される"},
	{"Rollback（4節、UNIQUE違反）", "900のまま", "トランザクション内の変更は破棄される"},
}))
gonbui.Sync()

func renderPlan(rows *sql.Rows) string {
	var b strings.Builder
	b.WriteString(`<table border="1" cellpadding="4" style="border-collapse:collapse">`)
	b.WriteString(`<tr><th>id</th><th>parent</th><th>notused</th><th>detail</th></tr>`)
	for rows.Next() {
		var id, parent, notused int
		var detail string
		if err := rows.Scan(&id, &parent, &notused, &detail); err != nil {
			panic(err)
		}
		b.WriteString(fmt.Sprintf(`<tr><td>%d</td><td>%d</td><td>%d</td><td>%s</td></tr>`, id, parent, notused, detail))
	}
	b.WriteString(`</table>`)
	return b.String()
}

%%
// 2万行の users テーブルをシードする（決定的データセット、等価述語で索引効果を確認するため十分な行数）。
db := openCourseDB(dbPath)
defer db.Close()

db.Exec(`DROP TABLE IF EXISTS users`)
db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)`)

tx, err := db.Begin()
if err != nil {
	panic(err)
}
for i := 0; i < 20000; i++ {
	if _, err := tx.Exec(`INSERT INTO users (email) VALUES (?)`, fmt.Sprintf("user%d@example.com", i)); err != nil {
		panic(err)
	}
}
if err := tx.Commit(); err != nil {
	panic(err)
}

var userCount int
db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&userCount)
fmt.Println("✅ users をシードしました。行数:", userCount)

✅ users をシードしました。行数: 20000

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

rows, err := db.Query(`EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'user9999@example.com'`)
if err != nil {
	panic(err)
}
defer rows.Close()
gonbui.DisplayHTML("<b>索引を作る前の EXPLAIN QUERY PLAN:</b>" + renderPlan(rows))
gonbui.Sync()

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

if _, err := db.Exec(`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`); err != nil {
	panic(err)
}

rows, err := db.Query(`EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'user9999@example.com'`)
if err != nil {
	panic(err)
}
defer rows.Close()
gonbui.DisplayHTML("<b>索引を作った後の EXPLAIN QUERY PLAN:</b>" + renderPlan(rows))
gonbui.Sync()

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

const query = `SELECT * FROM users WHERE email = 'user9999@example.com'`
const repeat = 200

measure := func() time.Duration {
	start := time.Now()
	for i := 0; i < repeat; i++ {
		rows, err := db.Query(query)
		if err != nil {
			panic(err)
		}
		for rows.Next() {
		}
		rows.Close()
	}
	return time.Since(start)
}

db.Exec(`DROP INDEX IF EXISTS idx_users_email`)
noIndexDur := measure()
fmt.Printf("索引なし: %v（%d回実行）\n", noIndexDur, repeat)

db.Exec(`CREATE INDEX idx_users_email ON users(email)`)
withIndexDur := measure()
fmt.Printf("索引あり: %v（%d回実行）\n", withIndexDur, repeat)

索引なし: 310.210708ms（200回実行）

索引あり: 2.637ms（200回実行）

%%
// 上のセルの実測値を棒グラフに描く。
db := openCourseDB(dbPath)
defer db.Close()

const query = `SELECT * FROM users WHERE email = 'user9999@example.com'`
const repeat = 200

measure := func() time.Duration {
	start := time.Now()
	for i := 0; i < repeat; i++ {
		rows, err := db.Query(query)
		if err != nil {
			panic(err)
		}
		for rows.Next() {
		}
		rows.Close()
	}
	return time.Since(start)
}

db.Exec(`DROP INDEX IF EXISTS idx_users_email`)
noIndexMs := float64(measure().Microseconds()) / 1000.0

db.Exec(`CREATE INDEX idx_users_email ON users(email)`)
withIndexMs := float64(measure().Microseconds()) / 1000.0

// PNG内は短い英字ラベルにする（gonum/plot の既定フォントは日本語グリフを描けず、
// 全角文字を混ぜると文字化けして消えるため。日本語の説明は上の Markdown 側で行う）。
p := plot.New()
p.Title.Text = fmt.Sprintf("Query time: no index vs index (total of %d runs)", repeat)
p.Y.Label.Text = "milliseconds"

w := vg.Points(40)
barsNoIndex, err := plotter.NewBarChart(plotter.Values{noIndexMs}, w)
if err != nil {
	panic(err)
}
barsNoIndex.Offset = -w / 2
barsNoIndex.Color = color.RGBA{R: 220, G: 80, B: 80, A: 255}

barsWithIndex, err := plotter.NewBarChart(plotter.Values{withIndexMs}, w)
if err != nil {
	panic(err)
}
barsWithIndex.Offset = w / 2
barsWithIndex.Color = color.RGBA{R: 60, G: 140, B: 220, A: 255}

p.Add(barsNoIndex, barsWithIndex)
p.NominalX("no index / with index")
p.Legend.Add("no index", barsNoIndex)
p.Legend.Add("with index", barsWithIndex)

wr, err := p.WriterTo(9*vg.Centimeter, 6*vg.Centimeter, "png")
if err != nil {
	panic(err)
}
var pngBuf bytes.Buffer
wr.WriteTo(&pngBuf)
gonbui.DisplayPNG(pngBuf.Bytes())
gonbui.Sync()
fmt.Printf("索引なし=%.3fms 索引あり=%.3fms\n", noIndexMs, withIndexMs)

索引なし=281.977ms 索引あり=2.183ms

func countUsersByEmailDomain(db *sql.DB, domain string) (int, error)

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

n1, err1 := countUsersByEmailDomain(db, "example.com")
if errors.Is(err1, ErrUnanswered) {
	fmt.Println("⚠️ 未回答: 練習問題を解いてから、このセルを再度実行してください")
} else {
	mustEqual(err1, nil, "example.com はエラーなし")
	mustEqual(n1, 20000, "全ユーザーが example.com ドメイン")

	n2, err2 := countUsersByEmailDomain(db, "not-exist.example")
	mustEqual(err2, nil, "存在しないドメインはエラーではない")
	mustEqual(n2, 0, "存在しないドメインは0件")

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

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

02.5 トランザクションとインデックス — 途中失敗と検索の速さ¶

1. トランザクションが必要な理由¶

2. 非自明な点: SQLite の外部キー制約は既定で無効¶

3. 成功するトランザクション¶

4. 失敗してロールバックするトランザクション¶

5. 前後を並べて比較する¶

6. インデックス: EXPLAIN QUERY PLAN で検索方式を見る¶

7. 実行時間を測って可視化する¶

8. 直感・類推: 索引は本の目次、トランザクションは荷造り¶

練習問題 2.5: ドメイン別のユーザー数を数える関数を実装しよう¶

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

まとめ¶

シナリオ	Aの残高	結果
Commit（3節）	900	反映される
Rollback（4節、UNIQUE違反）	900のまま	トランザクション内の変更は破棄される