package fixture

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
)

// 本文件是第三批的读写层：从 `yuansiwei_i` 只读取数 → 写入 `yuansiwei_2026`。
//
// 取样纪律：`t_school` 有 61905 行，**只按 id 列表取 6 行**，绝不 `SELECT *` 全表。

// extractSchools 按 id 列表取机构（只取这几个 id）。
func extractSchools(ctx context.Context, db *sql.DB, ids []int64) ([]LegacySchool, error) {
	if len(ids) == 0 {
		return nil, fmt.Errorf("institution id 列表为空")
	}
	ph := placeholders(len(ids))
	args := make([]any, len(ids))
	for i, v := range ids {
		args[i] = v
	}
	q := fmt.Sprintf(`
SELECT id, name, province_id, city_id, district_id, grade_period, status, ifnull(is_open_user_login,0)
  FROM t_school WHERE id IN (%s) ORDER BY id`, ph)
	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacySchool
	for rows.Next() {
		var r LegacySchool
		if err := rows.Scan(&r.ID, &r.Name, &r.ProvinceID, &r.CityID, &r.DistrictID,
			&r.GradePeriod, &r.Status, &r.IsOpenUserLogin); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// extractCampuses 取所选机构下的校区。
//
// self_support 是 `bit(1)`：go-sql-driver 会把它当 []byte（0x00/0x01）返回，
// 直接 Scan 进 int8 会得到 48/49（'0'/'1' 的 ASCII）——这里显式 `+0` 让 MySQL 先转成整数。
func extractCampuses(ctx context.Context, db *sql.DB, schoolIDs []int64) ([]LegacyCampus, error) {
	if len(schoolIDs) == 0 {
		return nil, fmt.Errorf("school id 列表为空")
	}
	ph := placeholders(len(schoolIDs))
	args := make([]any, len(schoolIDs))
	for i, v := range schoolIDs {
		args[i] = v
	}
	q := fmt.Sprintf(`
SELECT id, name, school_id, province_id, city_id, district_id, status, code,
       self_support+0, ifnull(is_open_user_login,0)
  FROM t_school_campus WHERE school_id IN (%s) ORDER BY school_id, id`, ph)
	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyCampus
	for rows.Next() {
		var r LegacyCampus
		if err := rows.Scan(&r.ID, &r.Name, &r.SchoolID, &r.ProvinceID, &r.CityID, &r.DistrictID,
			&r.Status, &r.Code, &r.SelfSupport, &r.IsOpenUserLogin); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// extractReferencedCampuses 取「数据里真正出现过的校区 id」集合（用于 WithAllCampuses=false）。
func extractReferencedCampuses(ctx context.Context, db *sql.DB) (map[int64]bool, error) {
	out := map[int64]bool{}
	for _, q := range refCampusQueries() {
		rows, err := db.QueryContext(ctx, q)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var id sql.NullInt64
			if err := rows.Scan(&id); err != nil {
				rows.Close()
				return nil, err
			}
			if id.Valid && id.Int64 != 0 {
				out[id.Int64] = true
			}
		}
		rows.Close()
	}
	return out, nil
}

// refCampusQueries 返回各来源的 distinct campusid 查询（只取非 0）。
func refCampusQueries() []string {
	var out []string
	for _, s := range orgRefSources() {
		if s.CampusCol == "" {
			continue
		}
		out = append(out, fmt.Sprintf(
			"SELECT DISTINCT %s FROM `%s` WHERE %s <> 0", s.CampusCol, s.Table, s.CampusCol))
	}
	return out
}

// orgRefSources 是「带机构/校区引用的数据来源」清单（验收 §3 要逐个来源统计孤儿）。
func orgRefSources() []RefSource {
	return []RefSource{
		{Table: "t_courses", InstCol: "schoolid", CampusCol: "campusid", Note: "课程"},
		{Table: "t_courses_class", InstCol: "schoolid", CampusCol: "campusid", Note: "班次"},
		{Table: "t_courses_record", InstCol: "schoolid", CampusCol: "campusid", Note: "上课记录"},
		{Table: "t_courses_activity", InstCol: "schoolid", CampusCol: "campusid", Note: "活动"},
		{Table: "t_user_expense_school", InstCol: "schoolid", CampusCol: "campusid", Note: "校区账户"},
		{Table: "t_expenditure_log", InstCol: "schoolid", CampusCol: "campusid", Note: "收支记录"},
		{Table: "t_user_balance_request", InstCol: "schoolid", CampusCol: "campusid", Note: "充值工单"},
		// 课程分类只有机构列
		{Table: "t_courses_classify", InstCol: "schoolid", CampusCol: "", Note: "课程分类（无校区列）"},
	}
}

// ---------- 写入（D1） ----------

// upsertInstitution 写机构（D1：显式插 institution_id）。
func upsertInstitution(ctx context.Context, db *sql.DB, r TargetInstitution) error {
	const q = `
INSERT INTO t_institution
  (institution_id, institution_name, institution_code, address, phone, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  institution_name = VALUES(institution_name),
  institution_code = VALUES(institution_code),
  address          = VALUES(address),
  phone            = VALUES(phone),
  status           = VALUES(status)`
	_, err := db.ExecContext(ctx, q, r.InstitutionID, r.InstitutionName,
		nullStr(r.InstitutionCode), nullStr(r.Address), nullStr(r.Phone), r.Status,
		r.CreatedAt.Format("2006-01-02 15:04:05"), r.UpdatedAt.Format("2006-01-02 15:04:05"))
	if err != nil {
		return fmt.Errorf("写 t_institution(id=%d) 失败：%w", r.InstitutionID, err)
	}
	return nil
}

// upsertCampus 写校区（D1：显式插 campus_id）。
func upsertCampus(ctx context.Context, db *sql.DB, r TargetCampus) error {
	const q = `
INSERT INTO t_campus
  (campus_id, institution_id, campus_name, campus_code, address, phone,
   status, self_support, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  institution_id = VALUES(institution_id),
  campus_name    = VALUES(campus_name),
  campus_code    = VALUES(campus_code),
  address        = VALUES(address),
  phone          = VALUES(phone),
  status         = VALUES(status),
  self_support   = VALUES(self_support)`
	_, err := db.ExecContext(ctx, q, r.CampusID, r.InstitutionID, r.CampusName,
		nullStr(r.CampusCode), nullStr(r.Address), nullStr(r.Phone), r.Status, r.SelfSupport,
		r.CreatedAt.Format("2006-01-02 15:04:05"), r.UpdatedAt.Format("2006-01-02 15:04:05"))
	if err != nil {
		return fmt.Errorf("写 t_campus(id=%d) 失败：%w", r.CampusID, err)
	}
	return nil
}

// countTargetOrg 统计 target 两张表的行数。
func countTargetOrg(ctx context.Context, db *sql.DB) (inst, camp int64, err error) {
	if err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM t_institution").Scan(&inst); err != nil {
		return 0, 0, err
	}
	if err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM t_campus").Scan(&camp); err != nil {
		return 0, 0, err
	}
	return inst, camp, nil
}

// joinList 把 id 列表拼成 "1,63317" 形式（仅用于报告与 SQL 的 IN 列表构造）。
func joinList(ids []int64) string {
	parts := make([]string, len(ids))
	for i, v := range ids {
		parts[i] = fmt.Sprint(v)
	}
	return strings.Join(parts, ",")
}