// Package repository 数据访问层，定义数据仓储接口和 SQLite 实现
package repository

import (
	"crypto/aes"
	"crypto/cipher"
	"crypto/rand"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"encoding/json"
	"errors"
	"io"
	"log"
	"os"
	"regexp"
	"resume-platform/internal/model"
	"time"

	"github.com/google/uuid"
	"golang.org/x/crypto/pbkdf2"

	_ "modernc.org/sqlite"
)

// formatDateTime 统一日期时间格式为 "2006-01-02 15:04:05"
// @param dt 原始日期字符串
// @return string 格式化后的日期时间
// @author sunct
func formatDateTime(dt string) string {
	if dt == "" {
		return ""
	}
	formats := []string{
		"2006-01-02 15:04:05",
		time.RFC3339,
		"2006-01-02T15:04:05Z07:00",
		"2006-01-02",
	}
	for _, f := range formats {
		if t, err := time.Parse(f, dt); err == nil {
			return t.Format("2006-01-02 15:04:05")
		}
	}
	re := regexp.MustCompile(`^(\d{4}-\d{2}-\d{2})\s*T?\s*(\d{2}:\d{2}:\d{2})`)
	if matches := re.FindStringSubmatch(dt); len(matches) >= 3 {
		return matches[1] + " " + matches[2]
	}
	return dt
}

// sqliteRepository SQLite 数据访问层实现
type sqliteRepository struct {
	db      *sql.DB     // SQLite 数据库连接
	cipher  cipher.AEAD // AES 加密器（字段加密用）
	enabled bool        // 是否启用字段加密
}

// NewSQLiteRepository 创建 SQLite 数据访问层实例，初始化数据库连接和表结构
// @param dbPath 数据库文件路径
// @param dbPassword 数据库加密密码（空则不加密）
// @return ResumeRepository 仓储接口实例
// @return error 初始化错误
// @author sunct
func NewSQLiteRepository(dbPath string, dbPassword string) (ResumeRepository, error) {
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, err
	}

	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	repo := &sqliteRepository{db: db, enabled: false}

	if dbPassword != "" {
		key := deriveKey(dbPassword)
		block, err := aes.NewCipher(key)
		if err != nil {
			return nil, err
		}
		gcm, err := cipher.NewGCM(block)
		if err != nil {
			return nil, err
		}
		repo.cipher = gcm
		repo.enabled = true
	}

	_, err = db.Exec(`PRAGMA timezone = 'localtime';`)
	if err != nil {
		return nil, err
	}

	err = initSchema(db)
	if err != nil {
		return nil, err
	}

	err = repo.populateResumeNames()
	if err != nil {
		return nil, err
	}

	return repo, nil
}

// deriveKey 使用 PBKDF2 从密码派生 AES 密钥
// @param password 原始密码
// @return []byte 32字节密钥
// @author sunct
func deriveKey(password string) []byte {
	salt := []byte("resume-platform-salt-2024")
	return pbkdf2.Key([]byte(password), salt, 4096, 32, sha256.New)
}

// encrypt 使用 AES-GCM 加密字符串
// @param data 明文
// @return string Base64编码的密文
// @return error 加密错误
// @author sunct
func (r *sqliteRepository) encrypt(data string) (string, error) {
	if !r.enabled || r.cipher == nil {
		return data, nil
	}
	nonce := make([]byte, r.cipher.NonceSize())
	if _, err := io.ReadFull(rand.Reader, nonce); err != nil {
		return "", err
	}
	ciphertext := r.cipher.Seal(nil, nonce, []byte(data), nil)
	return base64.StdEncoding.EncodeToString(append(nonce, ciphertext...)), nil
}

// decrypt 使用 AES-GCM 解密字符串
// @param data Base64编码的密文
// @return string 明文
// @return error 解密错误
// @author sunct
func (r *sqliteRepository) decrypt(data string) (string, error) {
	if !r.enabled || r.cipher == nil {
		return data, nil
	}
	raw, err := base64.StdEncoding.DecodeString(data)
	if err != nil {
		return data, nil
	}
	nonceSize := r.cipher.NonceSize()
	if len(raw) < nonceSize {
		return data, nil
	}
	nonce, ciphertext := raw[:nonceSize], raw[nonceSize:]
	plaintext, err := r.cipher.Open(nil, nonce, ciphertext, nil)
	if err != nil {
		return data, nil
	}
	return string(plaintext), nil
}

// initSchema 初始化数据库表结构，包含简历、用户、工作经历、教育背景、技能、项目、作品集等表
// 同时处理数据库迁移，为旧表添加缺失的字段
// initSchema 初始化数据库表结构，创建所有业务表
// @param db 数据库连接
// @return error 执行错误
// @author sunct
func initSchema(db *sql.DB) error {
	schema := `
	CREATE TABLE IF NOT EXISTS resumes (
		id TEXT PRIMARY KEY,
		user_id TEXT,
		route TEXT UNIQUE,
		password TEXT,
		basic_info TEXT,
		name TEXT,
		template TEXT DEFAULT 'modern',
		show_in_home INTEGER DEFAULT 1,
		created_at TEXT,
		updated_at TEXT
	);

	CREATE TABLE IF NOT EXISTS users (
		id TEXT PRIMARY KEY,
		username TEXT UNIQUE,
		password_hash TEXT,
		email TEXT,
		name TEXT,
		phone TEXT,
		role TEXT DEFAULT 'user',
		created_at TEXT,
		updated_at TEXT
	);

	CREATE TABLE IF NOT EXISTS experiences (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		resume_id TEXT,
		company TEXT,
		position TEXT,
		start_date TEXT,
		end_date TEXT,
		description TEXT,
		highlights TEXT,
		platforms TEXT
	);

	CREATE TABLE IF NOT EXISTS educations (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		resume_id TEXT,
		school TEXT,
		degree TEXT,
		major TEXT,
		start_date TEXT,
		end_date TEXT,
		gpa TEXT
	);

	CREATE TABLE IF NOT EXISTS skills (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		resume_id TEXT,
		name TEXT,
		level TEXT,
		category TEXT
	);

	CREATE TABLE IF NOT EXISTS projects (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		resume_id TEXT,
		name TEXT,
		description TEXT,
		tech_stack TEXT,
		url TEXT,
		highlights TEXT,
		achievements TEXT,
		start_date TEXT,
		end_date TEXT,
		show_in_resume INTEGER DEFAULT 1
	);

	CREATE TABLE IF NOT EXISTS portfolio_items (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id TEXT,
		name TEXT,
		description TEXT,
		tech_stack TEXT,
		url TEXT,
		start_date TEXT,
		end_date TEXT,
		doc TEXT,
		show_in_resume INTEGER DEFAULT 0,
		resume_id TEXT,
		project_id INTEGER DEFAULT 0,
		password TEXT,
		hidden INTEGER DEFAULT 0
	);

	CREATE TABLE IF NOT EXISTS quiz_records (
		id TEXT PRIMARY KEY,
		resume_id TEXT,
		resume_route TEXT,
		title TEXT,
		questions TEXT,
		user_answers TEXT,
		score REAL,
		correct_count INTEGER,
		total_count INTEGER,
		created_at TEXT
	);

	CREATE TABLE IF NOT EXISTS favorite_questions (
		id TEXT PRIMARY KEY,
		user_id TEXT,
		resume_id TEXT,
		resume_route TEXT,
		question TEXT,
		user_answer TEXT,
		is_correct INTEGER,
		is_favorite INTEGER,
		created_at TEXT
	);

	CREATE TABLE IF NOT EXISTS documents (
		id TEXT PRIMARY KEY,
		user_id TEXT,
		name TEXT,
		type TEXT,
		size INTEGER,
		mime_type TEXT,
		content TEXT,
		status TEXT DEFAULT 'uploading',
		error_msg TEXT,
		created_at TEXT DEFAULT (datetime('now', 'localtime')),
		updated_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS document_chunks (
		id TEXT PRIMARY KEY,
		document_id TEXT,
		chunk_index INTEGER,
		content TEXT,
		embedding TEXT,
		metadata TEXT,
		created_at TEXT DEFAULT (datetime('now', 'localtime')),
		FOREIGN KEY (document_id) REFERENCES documents(id)
	);

	CREATE TABLE IF NOT EXISTS embeddings (
		id TEXT PRIMARY KEY,
		content_hash TEXT UNIQUE,
		embedding TEXT,
		source_type TEXT,
		source_id TEXT,
		source_name TEXT,
		user_id TEXT,
		created_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS chat_history (
		id TEXT PRIMARY KEY,
		user_id TEXT,
		question TEXT,
		answer TEXT,
		context TEXT,
		created_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS menus (
		id TEXT PRIMARY KEY,
		name TEXT NOT NULL,
		icon TEXT,
		path TEXT,
		parent_id TEXT,
		sort_order INTEGER DEFAULT 0,
		is_fixed INTEGER DEFAULT 0,
		is_enabled INTEGER DEFAULT 1,
		created_at TEXT DEFAULT (datetime('now', 'localtime')),
		updated_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS system_config (
		id TEXT PRIMARY KEY,
		website_domain TEXT,
		website_logo TEXT,
		logo_path TEXT,
		website_title TEXT,
		website_desc TEXT,
		website_description TEXT,
		website_keywords TEXT,
		admin_email TEXT,
		admin_name TEXT,
		seo_keywords TEXT,
		seo_description TEXT,
		database_path TEXT,
		admin_page_size INTEGER DEFAULT 20,
		created_at TEXT DEFAULT (datetime('now', 'localtime')),
		updated_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS login_history (
		id TEXT PRIMARY KEY,
		user_id TEXT,
		user_name TEXT,
		ip TEXT,
		location TEXT,
		user_agent TEXT,
		success INTEGER DEFAULT 0,
		message TEXT,
		created_at TEXT DEFAULT (datetime('now', 'localtime'))
	);

	CREATE TABLE IF NOT EXISTS notifications (
		id TEXT PRIMARY KEY,
		type TEXT NOT NULL DEFAULT 'system',
		title TEXT NOT NULL,
		message TEXT,
		url TEXT,
		is_read INTEGER DEFAULT 0,
		created_at TEXT DEFAULT (datetime('now', 'localtime'))
	);
	`
	if _, err := db.Exec(schema); err != nil {
		return err
	}

	return migrateSchema(db)
}

// migrateSchema 数据库迁移，为旧表添加缺失的字段
// migrateSchema 数据库迁移，新增字段和表（兼容旧版本）
// @param db 数据库连接
// @return error 执行错误
// @author sunct
func migrateSchema(db *sql.DB) error {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('resumes') WHERE name = 'route'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE resumes ADD COLUMN route TEXT"); err != nil {
			return err
		}
		if _, err := db.Exec("UPDATE resumes SET route = id WHERE route IS NULL"); err != nil {
			return err
		}
		if _, err := db.Exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_resumes_route ON resumes(route)"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('resumes') WHERE name = 'name'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE resumes ADD COLUMN name TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('resumes') WHERE name = 'show_in_home'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE resumes ADD COLUMN show_in_home INTEGER DEFAULT 1"); err != nil {
			return err
		}
		if _, err := db.Exec("UPDATE resumes SET show_in_home = 1 WHERE show_in_home IS NULL"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('resumes') WHERE name = 'password'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE resumes ADD COLUMN password TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('resumes') WHERE name = 'user_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE resumes ADD COLUMN user_id TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('users') WHERE name = 'name'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE users ADD COLUMN name TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('users') WHERE name = 'phone'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE users ADD COLUMN phone TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('users') WHERE name = 'resume_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count > 0 {
		if _, err := db.Exec("ALTER TABLE users DROP COLUMN resume_id"); err != nil {
			if _, err2 := db.Exec("UPDATE users SET resume_id = NULL WHERE resume_id IS NOT NULL"); err2 != nil {
				return err2
			}
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('portfolio_items') WHERE name = 'user_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE portfolio_items ADD COLUMN user_id TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('portfolio_items') WHERE name = 'password'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE portfolio_items ADD COLUMN password TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('portfolio_items') WHERE name = 'hidden'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE portfolio_items ADD COLUMN hidden INTEGER DEFAULT 0"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('portfolio_items') WHERE name = 'resume_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE portfolio_items ADD COLUMN resume_id TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('portfolio_items') WHERE name = 'project_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE portfolio_items ADD COLUMN project_id INTEGER DEFAULT 0"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('quiz_records') WHERE name = 'title'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE quiz_records ADD COLUMN title TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('quiz_records') WHERE name = 'user_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE quiz_records ADD COLUMN user_id TEXT"); err != nil {
			return err
		}
		if _, err := db.Exec(`UPDATE quiz_records q SET user_id = (
			SELECT r.user_id FROM resumes r 
			WHERE r.id = q.resume_id OR r.route = q.resume_route
			LIMIT 1
		) WHERE q.user_id IS NULL`); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'logo_path'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN logo_path TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'admin_page_size'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN admin_page_size INTEGER DEFAULT 20"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'admin_email'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN admin_email TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'admin_name'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN admin_name TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'website_description'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN website_description TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'seo_keywords'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN seo_keywords TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('system_config') WHERE name = 'seo_description'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE system_config ADD COLUMN seo_description TEXT"); err != nil {
			return err
		}
	}

	err = db.QueryRow("SELECT COUNT(*) FROM pragma_table_info('favorite_questions') WHERE name = 'user_id'").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		if _, err := db.Exec("ALTER TABLE favorite_questions ADD COLUMN user_id TEXT"); err != nil {
			return err
		}
	}

	return nil
}

// GetResume 根据ID获取简历，包含关联的工作经历、教育背景、技能和项目
// GetResume 根据ID获取简历，包含所有关联数据
// @param id 简历ID
// @return *model.Resume 简历实体
// @return error 查询错误
// @author sunct
func (r *sqliteRepository) GetResume(id string) (*model.Resume, error) {
	var resume model.Resume
	var basicInfoJSON, createdAt, updatedAt, password string
	var showInHome int

	err := r.db.QueryRow(`
		SELECT id, COALESCE(user_id, ''), route, COALESCE(password, ''), basic_info, template, show_in_home, created_at, updated_at 
		FROM resumes WHERE id = ?`, id).Scan(
		&resume.ID, &resume.UserID, &resume.Route, &password, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
	if err != nil {
		return nil, err
	}

	resume.Password, _ = r.decrypt(password)
	resume.CreatedAt = formatDateTime(createdAt)
	resume.UpdatedAt = formatDateTime(updatedAt)

	if basicInfoJSON != "" {
		decryptedInfo, _ := r.decrypt(basicInfoJSON)
		json.Unmarshal([]byte(decryptedInfo), &resume.BasicInfo)
	}

	resume.ShowInHome = showInHome == 1
	resume.Experience, _ = r.getExperiences(id)
	resume.Education, _ = r.getEducations(id)
	resume.Skills, _ = r.getSkills(id)
	resume.Projects, _ = r.getProjects(id)

	return &resume, nil
}

// getExperiences 根据简历ID获取工作经历列表
func (r *sqliteRepository) getExperiences(resumeID string) ([]model.Experience, error) {
	rows, err := r.db.Query(`
		SELECT id, company, position, start_date, end_date, description, highlights, platforms
		FROM experiences WHERE resume_id = ?`, resumeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var experiences []model.Experience
	for rows.Next() {
		var e model.Experience
		var highlightsJSON, platformsJSON string
		err := rows.Scan(&e.ID, &e.Company, &e.Position, &e.StartDate, &e.EndDate, &e.Description, &highlightsJSON, &platformsJSON)
		if err != nil {
			return nil, err
		}
		e.ResumeID = resumeID
		if highlightsJSON != "" {
			json.Unmarshal([]byte(highlightsJSON), &e.Highlights)
		}
		if platformsJSON != "" {
			json.Unmarshal([]byte(platformsJSON), &e.Platforms)
		}
		experiences = append(experiences, e)
	}
	return experiences, nil
}

// getEducations 根据简历ID获取教育背景列表
func (r *sqliteRepository) getEducations(resumeID string) ([]model.Education, error) {
	rows, err := r.db.Query(`
		SELECT id, school, degree, major, start_date, end_date, gpa
		FROM educations WHERE resume_id = ?`, resumeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var educations []model.Education
	for rows.Next() {
		var e model.Education
		err := rows.Scan(&e.ID, &e.School, &e.Degree, &e.Major, &e.StartDate, &e.EndDate, &e.GPA)
		if err != nil {
			return nil, err
		}
		e.ResumeID = resumeID
		educations = append(educations, e)
	}
	return educations, nil
}

// getSkills 根据简历ID获取技能列表
func (r *sqliteRepository) getSkills(resumeID string) ([]model.Skill, error) {
	rows, err := r.db.Query(`
		SELECT id, name, level, category
		FROM skills WHERE resume_id = ?`, resumeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var skills []model.Skill
	for rows.Next() {
		var s model.Skill
		err := rows.Scan(&s.ID, &s.Name, &s.Level, &s.Category)
		if err != nil {
			return nil, err
		}
		s.ResumeID = resumeID
		skills = append(skills, s)
	}
	return skills, nil
}

// getProjects 根据简历ID获取项目列表
func (r *sqliteRepository) getProjects(resumeID string) ([]model.Project, error) {
	rows, err := r.db.Query(`
		SELECT id, name, description, tech_stack, url, highlights, achievements, start_date, end_date, show_in_resume
		FROM projects WHERE resume_id = ?`, resumeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var projects []model.Project
	for rows.Next() {
		var p model.Project
		var techStackJSON, highlightsJSON, achievementsJSON string
		var showInResume int
		err := rows.Scan(&p.ID, &p.Name, &p.Description, &techStackJSON, &p.URL, &highlightsJSON, &achievementsJSON, &p.StartDate, &p.EndDate, &showInResume)
		if err != nil {
			return nil, err
		}
		p.ResumeID = resumeID
		p.ShowInResume = showInResume == 1
		if techStackJSON != "" {
			json.Unmarshal([]byte(techStackJSON), &p.TechStack)
		}
		if highlightsJSON != "" {
			json.Unmarshal([]byte(highlightsJSON), &p.Highlights)
		}
		if achievementsJSON != "" {
			json.Unmarshal([]byte(achievementsJSON), &p.Achievements)
		}
		projects = append(projects, p)
	}
	return projects, nil
}

// CreateResume 创建简历，包含所有关联数据，使用事务保证数据一致性
func (r *sqliteRepository) CreateResume(resume *model.Resume) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	basicInfoJSON, _ := json.Marshal(resume.BasicInfo)
	encryptedInfo, _ := r.encrypt(string(basicInfoJSON))
	encryptedPassword, _ := r.encrypt(resume.Password)

	showInHome := 0
	if resume.ShowInHome {
		showInHome = 1
	}
	name := ""
	if resume.BasicInfo.Name != "" {
		name = resume.BasicInfo.Name
	}

	_, err = tx.Exec(`
		INSERT INTO resumes (id, user_id, route, password, basic_info, name, template, show_in_home, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
		resume.ID, resume.UserID, resume.Route, encryptedPassword, encryptedInfo, name, resume.Template, showInHome)
	if err != nil {
		tx.Rollback()
		return err
	}

	for _, e := range resume.Experience {
		highlightsJSON, _ := json.Marshal(e.Highlights)
		platformsJSON, _ := json.Marshal(e.Platforms)
		_, err = tx.Exec(`
			INSERT INTO experiences (resume_id, company, position, start_date, end_date, description, highlights, platforms)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
			resume.ID, e.Company, e.Position, e.StartDate, e.EndDate, e.Description, string(highlightsJSON), string(platformsJSON))
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	for _, e := range resume.Education {
		_, err = tx.Exec(`
			INSERT INTO educations (resume_id, school, degree, major, start_date, end_date, gpa)
			VALUES (?, ?, ?, ?, ?, ?, ?)`,
			resume.ID, e.School, e.Degree, e.Major, e.StartDate, e.EndDate, e.GPA)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	for _, s := range resume.Skills {
		_, err = tx.Exec(`
			INSERT INTO skills (resume_id, name, level, category)
			VALUES (?, ?, ?, ?)`,
			resume.ID, s.Name, s.Level, s.Category)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	for _, p := range resume.Projects {
		techStackJSON, _ := json.Marshal(p.TechStack)
		highlightsJSON, _ := json.Marshal(p.Highlights)
		achievementsJSON, _ := json.Marshal(p.Achievements)
		showInResume := 0
		if p.ShowInResume {
			showInResume = 1
		}
		_, err = tx.Exec(`
			INSERT INTO projects (resume_id, name, description, tech_stack, url, highlights, achievements, start_date, end_date, show_in_resume)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			resume.ID, p.Name, p.Description, string(techStackJSON), p.URL, string(highlightsJSON), string(achievementsJSON), p.StartDate, p.EndDate, showInResume)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}

// UpdateResume 更新简历，支持部分字段更新，使用事务保证数据一致性
func (r *sqliteRepository) UpdateResume(id string, update *model.ResumeUpdate) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	if update.BasicInfo != nil {
		basicInfoJSON, _ := json.Marshal(*update.BasicInfo)
		encryptedInfo, _ := r.encrypt(string(basicInfoJSON))
		name := update.BasicInfo.Name
		_, err = tx.Exec(`UPDATE resumes SET basic_info = ?, name = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`,
			encryptedInfo, name, id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if update.Template != nil {
		_, err = tx.Exec(`UPDATE resumes SET template = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`,
			*update.Template, id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if update.ShowInHome != nil {
		showInHome := 0
		if *update.ShowInHome {
			showInHome = 1
		}
		_, err = tx.Exec(`UPDATE resumes SET show_in_home = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`,
			showInHome, id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if update.Route != nil {
		_, err = tx.Exec(`UPDATE resumes SET route = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`,
			*update.Route, id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if update.Password != nil {
		encryptedPassword, _ := r.encrypt(*update.Password)
		_, err = tx.Exec(`UPDATE resumes SET password = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`,
			encryptedPassword, id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if update.Experience != nil {
		_, err = tx.Exec(`DELETE FROM experiences WHERE resume_id = ?`, id)
		if err != nil {
			tx.Rollback()
			return err
		}
		for _, e := range *update.Experience {
			highlightsJSON, _ := json.Marshal(e.Highlights)
			platformsJSON, _ := json.Marshal(e.Platforms)
			_, err = tx.Exec(`
				INSERT INTO experiences (resume_id, company, position, start_date, end_date, description, highlights, platforms)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
				id, e.Company, e.Position, e.StartDate, e.EndDate, e.Description, string(highlightsJSON), string(platformsJSON))
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	if update.Education != nil {
		_, err = tx.Exec(`DELETE FROM educations WHERE resume_id = ?`, id)
		if err != nil {
			tx.Rollback()
			return err
		}
		for _, e := range *update.Education {
			_, err = tx.Exec(`
				INSERT INTO educations (resume_id, school, degree, major, start_date, end_date, gpa)
				VALUES (?, ?, ?, ?, ?, ?, ?)`,
				id, e.School, e.Degree, e.Major, e.StartDate, e.EndDate, e.GPA)
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	if update.Skills != nil {
		_, err = tx.Exec(`DELETE FROM skills WHERE resume_id = ?`, id)
		if err != nil {
			tx.Rollback()
			return err
		}
		for _, s := range *update.Skills {
			_, err = tx.Exec(`
				INSERT INTO skills (resume_id, name, level, category)
				VALUES (?, ?, ?, ?)`,
				id, s.Name, s.Level, s.Category)
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	if update.Projects != nil {
		_, err = tx.Exec(`DELETE FROM projects WHERE resume_id = ?`, id)
		if err != nil {
			tx.Rollback()
			return err
		}
		for _, p := range *update.Projects {
			techStackJSON, _ := json.Marshal(p.TechStack)
			highlightsJSON, _ := json.Marshal(p.Highlights)
			achievementsJSON, _ := json.Marshal(p.Achievements)
			showInResume := 0
			if p.ShowInResume {
				showInResume = 1
			}
			_, err = tx.Exec(`
				INSERT INTO projects (resume_id, name, description, tech_stack, url, highlights, achievements, start_date, end_date, show_in_resume)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
				id, p.Name, p.Description, string(techStackJSON), p.URL, string(highlightsJSON), string(achievementsJSON), p.StartDate, p.EndDate, showInResume)
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	return tx.Commit()
}

// DeleteResume 删除简历及其所有关联数据，使用事务保证数据一致性
func (r *sqliteRepository) DeleteResume(id string) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	_, err = tx.Exec(`DELETE FROM experiences WHERE resume_id = ?`, id)
	if err != nil {
		tx.Rollback()
		return err
	}
	_, err = tx.Exec(`DELETE FROM educations WHERE resume_id = ?`, id)
	if err != nil {
		tx.Rollback()
		return err
	}
	_, err = tx.Exec(`DELETE FROM skills WHERE resume_id = ?`, id)
	if err != nil {
		tx.Rollback()
		return err
	}
	_, err = tx.Exec(`DELETE FROM projects WHERE resume_id = ?`, id)
	if err != nil {
		tx.Rollback()
		return err
	}
	_, err = tx.Exec(`DELETE FROM resumes WHERE id = ?`, id)
	if err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit()
}

// UpdateResumeUserID 更新简历的用户绑定
func (r *sqliteRepository) UpdateResumeUserID(resumeID, userID string) error {
	_, err := r.db.Exec(`UPDATE resumes SET user_id = ?, updated_at = datetime('now', 'localtime') WHERE id = ?`, userID, resumeID)
	return err
}

// GetResumeByRoute 根据访问路由获取简历
func (r *sqliteRepository) GetResumeByRoute(route string) (*model.Resume, error) {
	var resume model.Resume
	var basicInfoJSON, createdAt, updatedAt, password string
	var showInHome int

	err := r.db.QueryRow(`
		SELECT id, COALESCE(user_id, ''), route, COALESCE(password, ''), basic_info, template, show_in_home, created_at, updated_at 
		FROM resumes WHERE route = ?`, route).Scan(
		&resume.ID, &resume.UserID, &resume.Route, &password, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			err2 := r.db.QueryRow(`
				SELECT id, COALESCE(user_id, ''), route, COALESCE(password, ''), basic_info, template, show_in_home, created_at, updated_at 
				FROM resumes WHERE id = ?`, route).Scan(
				&resume.ID, &resume.UserID, &resume.Route, &password, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
			if err2 != nil {
				return nil, err2
			}
		} else {
			return nil, err
		}
	}

	resume.Password, _ = r.decrypt(password)
	resume.CreatedAt = formatDateTime(createdAt)
	resume.UpdatedAt = formatDateTime(updatedAt)

	if basicInfoJSON != "" {
		decryptedInfo, _ := r.decrypt(basicInfoJSON)
		json.Unmarshal([]byte(decryptedInfo), &resume.BasicInfo)
	}

	resume.ShowInHome = showInHome == 1
	resume.Experience, _ = r.getExperiences(resume.ID)
	resume.Education, _ = r.getEducations(resume.ID)
	resume.Skills, _ = r.getSkills(resume.ID)
	resume.Projects, _ = r.getProjects(resume.ID)

	return &resume, nil
}

// RouteExists 检查路由是否已存在
func (r *sqliteRepository) RouteExists(route string) bool {
	var count int
	r.db.QueryRow(`SELECT COUNT(*) FROM resumes WHERE route = ?`, route).Scan(&count)
	return count > 0
}

// GetAllResumes 获取所有简历列表（不包含详细关联数据）
func (r *sqliteRepository) GetAllResumes() ([]*model.Resume, error) {
	rows, err := r.db.Query(`SELECT id, COALESCE(user_id, ''), route, basic_info, template, show_in_home, created_at, updated_at FROM resumes`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var resumes []*model.Resume
	for rows.Next() {
		var resume model.Resume
		var basicInfoJSON, createdAt, updatedAt string
		var showInHome int
		err := rows.Scan(&resume.ID, &resume.UserID, &resume.Route, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}
		if basicInfoJSON != "" {
			decryptedInfo, _ := r.decrypt(basicInfoJSON)
			json.Unmarshal([]byte(decryptedInfo), &resume.BasicInfo)
		}
		resume.ShowInHome = showInHome == 1
		resume.CreatedAt = formatDateTime(createdAt)
		resume.UpdatedAt = formatDateTime(updatedAt)
		resumes = append(resumes, &resume)
	}
	return resumes, nil
}

// GetResumesWithPagination 获取简历列表（分页）
func (r *sqliteRepository) GetResumesWithPagination(page, pageSize int) ([]*model.Resume, int, error) {
	return r.GetResumesWithFilter(page, pageSize, "", "", "", nil)
}

// GetResumesWithFilter 带筛选条件的简历分页查询
func (r *sqliteRepository) GetResumesWithFilter(page, pageSize int, userID, keyword, template string, showInHome *bool) ([]*model.Resume, int, error) {
	offset := (page - 1) * pageSize

	where := "WHERE 1=1"
	args := []interface{}{}
	if userID != "" {
		where += " AND user_id = ?"
		args = append(args, userID)
	}
	if keyword != "" {
		where += " AND (route LIKE ? OR name LIKE ?)"
		kw := "%" + keyword + "%"
		args = append(args, kw, kw)
	}
	if template != "" {
		where += " AND template = ?"
		args = append(args, template)
	}
	if showInHome != nil {
		if *showInHome {
			where += " AND show_in_home = 1"
		} else {
			where += " AND show_in_home = 0"
		}
	}

	var total int
	countArgs := make([]interface{}, len(args))
	copy(countArgs, args)
	err := r.db.QueryRow("SELECT COUNT(*) FROM resumes "+where, countArgs...).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	queryArgs := append(args, pageSize, offset)
	rows, err := r.db.Query("SELECT id, COALESCE(user_id, ''), route, basic_info, template, show_in_home, created_at, updated_at FROM resumes "+where+" ORDER BY updated_at DESC LIMIT ? OFFSET ?", queryArgs...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var resumes []*model.Resume
	for rows.Next() {
		var resume model.Resume
		var basicInfoJSON, createdAt, updatedAt string
		var showInHome int
		err := rows.Scan(&resume.ID, &resume.UserID, &resume.Route, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
		if err != nil {
			return nil, 0, err
		}
		if basicInfoJSON != "" {
			decryptedInfo, _ := r.decrypt(basicInfoJSON)
			json.Unmarshal([]byte(decryptedInfo), &resume.BasicInfo)
		}
		resume.ShowInHome = showInHome == 1
		resume.CreatedAt = formatDateTime(createdAt)
		resume.UpdatedAt = formatDateTime(updatedAt)
		resumes = append(resumes, &resume)
	}
	return resumes, total, nil
}

// CreateUser 创建用户
func (r *sqliteRepository) CreateUser(user *model.User) error {
	_, err := r.db.Exec(`
		INSERT INTO users (id, username, password_hash, email, name, phone, role, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
		user.ID, user.Username, user.PasswordHash, user.Email, user.Name, user.Phone, user.Role)
	return err
}

// GetUserByUsername 根据用户名获取用户
func (r *sqliteRepository) GetUserByUsername(username string) (*model.User, error) {
	var user model.User
	var createdAt, updatedAt string
	err := r.db.QueryRow(`
		SELECT id, username, password_hash, email, name, phone, role, created_at, updated_at
		FROM users WHERE username = ?`, username).Scan(
		&user.ID, &user.Username, &user.PasswordHash, &user.Email, &user.Name, &user.Phone, &user.Role, &createdAt, &updatedAt)
	if err != nil {
		return nil, err
	}
	user.CreatedAt = formatDateTime(createdAt)
	user.UpdatedAt = formatDateTime(updatedAt)
	return &user, nil
}

// GetUserByID 根据ID获取用户
func (r *sqliteRepository) GetUserByID(id string) (*model.User, error) {
	var user model.User
	var createdAt, updatedAt string
	err := r.db.QueryRow(`
		SELECT id, username, password_hash, email, name, phone, role, created_at, updated_at
		FROM users WHERE id = ?`, id).Scan(
		&user.ID, &user.Username, &user.PasswordHash, &user.Email, &user.Name, &user.Phone, &user.Role, &createdAt, &updatedAt)
	if err != nil {
		return nil, err
	}
	user.CreatedAt = formatDateTime(createdAt)
	user.UpdatedAt = formatDateTime(updatedAt)
	return &user, nil
}

// GetAllUsers 获取所有用户列表
func (r *sqliteRepository) GetAllUsers() ([]*model.User, error) {
	rows, err := r.db.Query(`SELECT id, username, email, name, phone, role, created_at FROM users ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*model.User
	for rows.Next() {
		var user model.User
		var createdAt string
		err := rows.Scan(&user.ID, &user.Username, &user.Email, &user.Name, &user.Phone, &user.Role, &createdAt)
		if err != nil {
			return nil, err
		}
		user.CreatedAt = formatDateTime(createdAt)
		users = append(users, &user)
	}
	return users, nil
}

// GetUsersWithPagination 获取用户列表（分页）
func (r *sqliteRepository) GetUsersWithPagination(page, pageSize int) ([]*model.User, int, error) {
	offset := (page - 1) * pageSize

	var total int
	err := r.db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	rows, err := r.db.Query(`SELECT id, username, email, name, phone, role, created_at FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?`, pageSize, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var users []*model.User
	for rows.Next() {
		var user model.User
		var createdAt string
		err := rows.Scan(&user.ID, &user.Username, &user.Email, &user.Name, &user.Phone, &user.Role, &createdAt)
		if err != nil {
			return nil, 0, err
		}
		user.CreatedAt = formatDateTime(createdAt)
		users = append(users, &user)
	}
	return users, total, nil
}

// UpdateUser 更新用户信息
func (r *sqliteRepository) UpdateUser(id string, user *model.User) error {
	// 若未传入 password_hash 或 role，则保留原值，避免被空字符串覆盖
	if user.PasswordHash == "" || user.Role == "" {
		var existingPwd, existingRole string
		err := r.db.QueryRow("SELECT password_hash, role FROM users WHERE id = ?", id).Scan(&existingPwd, &existingRole)
		if err == nil {
			if user.PasswordHash == "" {
				user.PasswordHash = existingPwd
			}
			if user.Role == "" {
				user.Role = existingRole
			}
		}
	}
	_, err := r.db.Exec(`
		UPDATE users SET username = ?, password_hash = ?, email = ?, name = ?, phone = ?, role = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`,
		user.Username, user.PasswordHash, user.Email, user.Name, user.Phone, user.Role, id)
	return err
}

// DeleteUser 删除用户
func (r *sqliteRepository) DeleteUser(id string) error {
	_, err := r.db.Exec(`DELETE FROM users WHERE id = ?`, id)
	return err
}

// GetResumesByUserID 根据用户ID获取简历列表
func (r *sqliteRepository) GetResumesByUserID(userID string) ([]*model.Resume, error) {
	rows, err := r.db.Query(`SELECT id, COALESCE(user_id, ''), route, basic_info, template, show_in_home, created_at, updated_at FROM resumes WHERE user_id = ?`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var resumes []*model.Resume
	for rows.Next() {
		var resume model.Resume
		var basicInfoJSON, createdAt, updatedAt string
		var showInHome int
		err := rows.Scan(&resume.ID, &resume.UserID, &resume.Route, &basicInfoJSON, &resume.Template, &showInHome, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}
		if basicInfoJSON != "" {
			decryptedInfo, _ := r.decrypt(basicInfoJSON)
			json.Unmarshal([]byte(decryptedInfo), &resume.BasicInfo)
		}
		resume.ShowInHome = showInHome == 1
		resume.CreatedAt = formatDateTime(createdAt)
		resume.UpdatedAt = formatDateTime(updatedAt)
		resumes = append(resumes, &resume)
	}
	return resumes, nil
}

// GetPortfolioItemsByUserID 根据用户ID获取作品集列表
func (r *sqliteRepository) GetPortfolioItemsByUserID(userID string) ([]*model.PortfolioItem, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, name, description, tech_stack, url, start_date, end_date, doc, show_in_resume, resume_id, project_id, password, hidden
		FROM portfolio_items WHERE user_id = ? ORDER BY id DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []*model.PortfolioItem
	for rows.Next() {
		var item model.PortfolioItem
		var techStackJSON string
		var showInResume, hidden int
		err := rows.Scan(&item.ID, &item.UserID, &item.Name, &item.Description, &techStackJSON, &item.URL, &item.StartDate, &item.EndDate, &item.Doc, &showInResume, &item.ResumeID, &item.ProjectID, &item.Password, &hidden)
		if err != nil {
			return nil, err
		}
		item.ShowInResume = showInResume == 1
		item.Hidden = hidden == 1
		if techStackJSON != "" {
			json.Unmarshal([]byte(techStackJSON), &item.TechStack)
		}
		items = append(items, &item)
	}
	return items, nil
}

// Exists 检查简历是否存在
func (r *sqliteRepository) Exists(id string) bool {
	var count int
	r.db.QueryRow(`SELECT COUNT(*) FROM resumes WHERE id = ?`, id).Scan(&count)
	return count > 0
}

// GetPortfolioItems 获取所有作品集项目列表
func (r *sqliteRepository) GetPortfolioItems() ([]*model.PortfolioItem, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, name, description, tech_stack, url, start_date, end_date, doc, show_in_resume, resume_id, project_id, password, hidden
		FROM portfolio_items ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []*model.PortfolioItem
	for rows.Next() {
		var item model.PortfolioItem
		var techStackJSON string
		var showInResume, hidden int
		err := rows.Scan(&item.ID, &item.UserID, &item.Name, &item.Description, &techStackJSON, &item.URL, &item.StartDate, &item.EndDate, &item.Doc, &showInResume, &item.ResumeID, &item.ProjectID, &item.Password, &hidden)
		if err != nil {
			return nil, err
		}
		item.ShowInResume = showInResume == 1
		item.Hidden = hidden == 1
		if techStackJSON != "" {
			json.Unmarshal([]byte(techStackJSON), &item.TechStack)
		}
		items = append(items, &item)
	}
	return items, nil
}

// GetPortfolioItemsWithPagination 获取作品集项目列表（分页）
func (r *sqliteRepository) GetPortfolioItemsWithPagination(page, pageSize int) ([]*model.PortfolioItem, int, error) {
	offset := (page - 1) * pageSize

	var total int
	err := r.db.QueryRow(`SELECT COUNT(*) FROM portfolio_items`).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	rows, err := r.db.Query(`
		SELECT id, user_id, name, description, tech_stack, url, start_date, end_date, doc, show_in_resume, resume_id, project_id, password, hidden
		FROM portfolio_items ORDER BY id DESC LIMIT ? OFFSET ?`, pageSize, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var items []*model.PortfolioItem
	for rows.Next() {
		var item model.PortfolioItem
		var techStackJSON string
		var showInResume, hidden int
		err := rows.Scan(&item.ID, &item.UserID, &item.Name, &item.Description, &techStackJSON, &item.URL, &item.StartDate, &item.EndDate, &item.Doc, &showInResume, &item.ResumeID, &item.ProjectID, &item.Password, &hidden)
		if err != nil {
			return nil, 0, err
		}
		item.ShowInResume = showInResume == 1
		item.Hidden = hidden == 1
		if techStackJSON != "" {
			json.Unmarshal([]byte(techStackJSON), &item.TechStack)
		}
		items = append(items, &item)
	}
	return items, total, nil
}

// GetPortfolioItem 根据ID获取单个作品集项目
func (r *sqliteRepository) GetPortfolioItem(id uint) (*model.PortfolioItem, error) {
	var item model.PortfolioItem
	var techStackJSON string
	var showInResume, hidden int

	err := r.db.QueryRow(`
		SELECT id, user_id, name, description, tech_stack, url, start_date, end_date, doc, show_in_resume, resume_id, project_id, password, hidden
		FROM portfolio_items WHERE id = ?`, id).Scan(
		&item.ID, &item.UserID, &item.Name, &item.Description, &techStackJSON, &item.URL, &item.StartDate, &item.EndDate, &item.Doc, &showInResume, &item.ResumeID, &item.ProjectID, &item.Password, &hidden)
	if err != nil {
		return nil, err
	}

	item.ShowInResume = showInResume == 1
	item.Hidden = hidden == 1
	if techStackJSON != "" {
		json.Unmarshal([]byte(techStackJSON), &item.TechStack)
	}

	return &item, nil
}

// CreatePortfolioItem 创建作品集项目
func (r *sqliteRepository) CreatePortfolioItem(item *model.PortfolioItem) error {
	techStackJSON, _ := json.Marshal(item.TechStack)
	showInResume := 0
	if item.ShowInResume {
		showInResume = 1
	}
	hidden := 0
	if item.Hidden {
		hidden = 1
	}

	_, err := r.db.Exec(`
		INSERT INTO portfolio_items (user_id, name, description, tech_stack, url, start_date, end_date, doc, show_in_resume, resume_id, project_id, password, hidden)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.UserID, item.Name, item.Description, string(techStackJSON), item.URL, item.StartDate, item.EndDate, item.Doc, showInResume, item.ResumeID, item.ProjectID, item.Password, hidden)
	return err
}

// UpdatePortfolioItem 更新作品集项目
func (r *sqliteRepository) UpdatePortfolioItem(id uint, item *model.PortfolioItem) error {
	techStackJSON, _ := json.Marshal(item.TechStack)
	showInResume := 0
	if item.ShowInResume {
		showInResume = 1
	}
	hidden := 0
	if item.Hidden {
		hidden = 1
	}

	_, err := r.db.Exec(`
		UPDATE portfolio_items SET user_id = ?, name = ?, description = ?, tech_stack = ?, url = ?, start_date = ?, end_date = ?, doc = ?, show_in_resume = ?, resume_id = ?, project_id = ?, password = ?, hidden = ?
		WHERE id = ?`,
		item.UserID, item.Name, item.Description, string(techStackJSON), item.URL, item.StartDate, item.EndDate, item.Doc, showInResume, item.ResumeID, item.ProjectID, item.Password, hidden, id)
	return err
}

// DeletePortfolioItem 删除作品集项目
func (r *sqliteRepository) DeletePortfolioItem(id uint) error {
	_, err := r.db.Exec(`DELETE FROM portfolio_items WHERE id = ?`, id)
	return err
}

// CreateQuizRecord 创建面试题历史记录
func (r *sqliteRepository) CreateQuizRecord(record *model.QuizRecord) error {
	questionsJSON, _ := json.Marshal(record.Questions)
	userAnswersJSON, _ := json.Marshal(record.UserAnswers)

	log.Printf("[DEBUG] CreateQuizRecord called with id: %s, resume_route: %s, resume_id: %s, title: %s, score: %f, correct_count: %d, total_count: %d",
		record.ID, record.ResumeRoute, record.ResumeID, record.Title, record.Score, record.CorrectCount, record.TotalCount)

	result, err := r.db.Exec(`
		INSERT INTO quiz_records (id, user_id, resume_id, resume_route, title, questions, user_answers, score, correct_count, total_count, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		record.ID, record.UserID, record.ResumeID, record.ResumeRoute, record.Title,
		string(questionsJSON), string(userAnswersJSON),
		record.Score, record.CorrectCount, record.TotalCount)

	if err != nil {
		log.Printf("[ERROR] CreateQuizRecord error: %v", err)
		return err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Printf("[ERROR] CreateQuizRecord rows affected error: %v", err)
	} else {
		log.Printf("[DEBUG] CreateQuizRecord success, rows affected: %d", rowsAffected)
	}

	var count int
	err = r.db.QueryRow("SELECT COUNT(*) FROM quiz_records").Scan(&count)
	if err != nil {
		log.Printf("[ERROR] CreateQuizRecord count error: %v", err)
	} else {
		log.Printf("[DEBUG] CreateQuizRecord total records: %d", count)
	}

	return nil
}

// GetQuizRecordsByResume 根据简历路由获取面试题历史记录
func (r *sqliteRepository) GetQuizRecordsByResume(resumeRoute string) ([]*model.QuizRecord, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, resume_id, resume_route, title, questions, user_answers, score, correct_count, total_count, created_at
		FROM quiz_records WHERE resume_route = ? ORDER BY created_at DESC`, resumeRoute)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var records []*model.QuizRecord
	for rows.Next() {
		var record model.QuizRecord
		var questionsJSON, userAnswersJSON, createdAt string

		err := rows.Scan(&record.ID, &record.UserID, &record.ResumeID, &record.ResumeRoute, &record.Title,
			&questionsJSON, &userAnswersJSON, &record.Score,
			&record.CorrectCount, &record.TotalCount, &createdAt)
		if err != nil {
			return nil, err
		}

		if questionsJSON != "" {
			json.Unmarshal([]byte(questionsJSON), &record.Questions)
		}
		if userAnswersJSON != "" {
			json.Unmarshal([]byte(userAnswersJSON), &record.UserAnswers)
		}

		records = append(records, &record)
	}
	return records, nil
}

// GetQuizRecord 根据ID获取面试题记录
func (r *sqliteRepository) GetQuizRecord(id string) (*model.QuizRecord, error) {
	var record model.QuizRecord
	var questionsJSON, userAnswersJSON string
	var title sql.NullString

	err := r.db.QueryRow(`
		SELECT id, user_id, resume_id, resume_route, title, questions, user_answers, score, correct_count, total_count, created_at
		FROM quiz_records WHERE id = ?`, id).Scan(
		&record.ID, &record.UserID, &record.ResumeID, &record.ResumeRoute, &title,
		&questionsJSON, &userAnswersJSON, &record.Score,
		&record.CorrectCount, &record.TotalCount, &record.CreatedAt)
	if err != nil {
		return nil, err
	}

	if title.Valid {
		record.Title = title.String
	}

	if questionsJSON != "" {
		json.Unmarshal([]byte(questionsJSON), &record.Questions)
	}
	if userAnswersJSON != "" {
		json.Unmarshal([]byte(userAnswersJSON), &record.UserAnswers)
	}

	return &record, nil
}

// DeleteQuizRecord 删除面试题记录
func (r *sqliteRepository) DeleteQuizRecord(id string) error {
	_, err := r.db.Exec(`DELETE FROM quiz_records WHERE id = ?`, id)
	return err
}

// GetQuizRecordsByUserID 根据用户ID获取面试题记录
func (r *sqliteRepository) GetQuizRecordsByUserID(userID string) ([]*model.QuizRecord, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, resume_id, resume_route, title, questions, user_answers, score, correct_count, total_count, created_at
		FROM quiz_records
		WHERE user_id = ?
		ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var records []*model.QuizRecord
	for rows.Next() {
		var record model.QuizRecord
		var questionsJSON, userAnswersJSON, createdAt string
		var title sql.NullString

		err := rows.Scan(&record.ID, &record.UserID, &record.ResumeID, &record.ResumeRoute, &title,
			&questionsJSON, &userAnswersJSON, &record.Score,
			&record.CorrectCount, &record.TotalCount, &createdAt)
		if err != nil {
			return nil, err
		}

		if title.Valid {
			record.Title = title.String
		}

		if questionsJSON != "" {
			json.Unmarshal([]byte(questionsJSON), &record.Questions)
		}
		if userAnswersJSON != "" {
			json.Unmarshal([]byte(userAnswersJSON), &record.UserAnswers)
		}

		records = append(records, &record)
	}

	return records, nil
}

// GetQuizRecordsByUserIDWithPagination 根据用户ID分页获取面试题记录
func (r *sqliteRepository) GetQuizRecordsByUserIDWithPagination(userID string, page, pageSize int) ([]*model.QuizRecord, int, error) {
	var total int
	err := r.db.QueryRow(`
		SELECT COUNT(*) FROM quiz_records
		WHERE user_id = ?`, userID).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	offset := (page - 1) * pageSize
	rows, err := r.db.Query(`
		SELECT id, user_id, resume_id, resume_route, title, questions, user_answers, score, correct_count, total_count, created_at
		FROM quiz_records
		WHERE user_id = ?
		ORDER BY created_at DESC
		LIMIT ? OFFSET ?`, userID, pageSize, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var records []*model.QuizRecord
	for rows.Next() {
		var record model.QuizRecord
		var questionsJSON, userAnswersJSON, createdAt string
		var title sql.NullString

		err := rows.Scan(&record.ID, &record.UserID, &record.ResumeID, &record.ResumeRoute, &title,
			&questionsJSON, &userAnswersJSON, &record.Score,
			&record.CorrectCount, &record.TotalCount, &createdAt)
		if err != nil {
			return nil, 0, err
		}

		if title.Valid {
			record.Title = title.String
		}

		if questionsJSON != "" {
			json.Unmarshal([]byte(questionsJSON), &record.Questions)
		}
		if userAnswersJSON != "" {
			json.Unmarshal([]byte(userAnswersJSON), &record.UserAnswers)
		}

		records = append(records, &record)
	}

	return records, total, nil
}

// GetFavoriteQuestionsByUserID 根据用户ID获取收藏/错题记录
func (r *sqliteRepository) GetFavoriteQuestionsByUserID(userID string) ([]*model.FavoriteQuestion, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, resume_id, resume_route, question, user_answer, is_correct, is_favorite, created_at 
		FROM favorite_questions WHERE user_id = ? ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var favorites []*model.FavoriteQuestion
	for rows.Next() {
		var fav model.FavoriteQuestion
		var questionJSON, createdAt string
		var isCorrect, isFavorite int

		err := rows.Scan(&fav.ID, &fav.UserID, &fav.ResumeID, &fav.ResumeRoute,
			&questionJSON, &fav.UserAnswer,
			&isCorrect, &isFavorite, &createdAt)
		if err != nil {
			return nil, err
		}

		fav.IsCorrect = isCorrect == 1
		fav.IsFavorite = isFavorite == 1

		if questionJSON != "" {
			json.Unmarshal([]byte(questionJSON), &fav.Question)
		}

		favorites = append(favorites, &fav)
	}
	return favorites, nil
}

// CreateFavoriteQuestion 创建收藏/错题记录
func (r *sqliteRepository) CreateFavoriteQuestion(fav *model.FavoriteQuestion) error {
	questionJSON, _ := json.Marshal(fav.Question)

	isCorrect := 0
	if fav.IsCorrect {
		isCorrect = 1
	}
	isFavorite := 0
	if fav.IsFavorite {
		isFavorite = 1
	}

	_, err := r.db.Exec(`
		INSERT INTO favorite_questions (id, user_id, resume_id, resume_route, question, user_answer, is_correct, is_favorite, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		fav.ID, fav.UserID, fav.ResumeID, fav.ResumeRoute,
		string(questionJSON), fav.UserAnswer,
		isCorrect, isFavorite)
	return err
}

// GetFavoriteQuestions 获取收藏/错题记录
func (r *sqliteRepository) GetFavoriteQuestions(resumeRoute string, isFavorite, isCorrect *bool) ([]*model.FavoriteQuestion, error) {
	query := `SELECT id, user_id, resume_id, resume_route, question, user_answer, is_correct, is_favorite, created_at 
			  FROM favorite_questions WHERE resume_route = ?`
	params := []interface{}{resumeRoute}

	if isFavorite != nil {
		query += " AND is_favorite = ?"
		if *isFavorite {
			params = append(params, 1)
		} else {
			params = append(params, 0)
		}
	}

	if isCorrect != nil {
		query += " AND is_correct = ?"
		if *isCorrect {
			params = append(params, 1)
		} else {
			params = append(params, 0)
		}
	}

	query += " ORDER BY created_at DESC"

	rows, err := r.db.Query(query, params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var favorites []*model.FavoriteQuestion
	for rows.Next() {
		var fav model.FavoriteQuestion
		var questionJSON, createdAt string
		var isCorrect, isFavorite int

		err := rows.Scan(&fav.ID, &fav.UserID, &fav.ResumeID, &fav.ResumeRoute,
			&questionJSON, &fav.UserAnswer,
			&isCorrect, &isFavorite, &createdAt)
		if err != nil {
			return nil, err
		}

		fav.IsCorrect = isCorrect == 1
		fav.IsFavorite = isFavorite == 1

		if questionJSON != "" {
			json.Unmarshal([]byte(questionJSON), &fav.Question)
		}

		favorites = append(favorites, &fav)
	}
	return favorites, nil
}

// DeleteFavoriteQuestion 删除收藏/错题记录
func (r *sqliteRepository) DeleteFavoriteQuestion(id string) error {
	_, err := r.db.Exec(`DELETE FROM favorite_questions WHERE id = ?`, id)
	return err
}

// ToggleFavorite 切换收藏状态
func (r *sqliteRepository) ToggleFavorite(id string) error {
	_, err := r.db.Exec(`UPDATE favorite_questions SET is_favorite = 1 - is_favorite WHERE id = ?`, id)
	return err
}

// CreateDocument 创建文档
func (r *sqliteRepository) CreateDocument(doc *model.Document) error {
	_, err := r.db.Exec(`
		INSERT INTO documents (id, user_id, name, type, size, mime_type, content, status, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
		doc.ID, doc.UserID, doc.Name, doc.Type, doc.Size, doc.MIMEType, doc.Content, doc.Status)
	return err
}

// GetDocument 根据ID和用户ID获取文档
func (r *sqliteRepository) GetDocument(id, userID string) (*model.Document, error) {
	var doc model.Document
	var createdAt, updatedAt string
	err := r.db.QueryRow(`
		SELECT id, user_id, name, type, size, mime_type, content, status, error_msg, created_at, updated_at
		FROM documents WHERE id = ? AND user_id = ?`, id, userID).Scan(
		&doc.ID, &doc.UserID, &doc.Name, &doc.Type, &doc.Size, &doc.MIMEType, &doc.Content, &doc.Status, &doc.ErrorMsg, &createdAt, &updatedAt)
	if err != nil {
		return nil, err
	}
	doc.CreatedAt = formatDateTime(createdAt)
	doc.UpdatedAt = formatDateTime(updatedAt)
	return &doc, nil
}

// GetDocumentByID 根据ID获取文档
func (r *sqliteRepository) GetDocumentByID(id string) (*model.Document, error) {
	var doc model.Document
	var createdAt, updatedAt string
	err := r.db.QueryRow(`
		SELECT id, user_id, name, type, size, mime_type, content, status, error_msg, created_at, updated_at
		FROM documents WHERE id = ?`, id).Scan(
		&doc.ID, &doc.UserID, &doc.Name, &doc.Type, &doc.Size, &doc.MIMEType, &doc.Content, &doc.Status, &doc.ErrorMsg, &createdAt, &updatedAt)
	if err != nil {
		return nil, err
	}
	doc.CreatedAt = formatDateTime(createdAt)
	doc.UpdatedAt = formatDateTime(updatedAt)
	return &doc, nil
}

// GetDocumentsByUserID 根据用户ID获取文档列表
func (r *sqliteRepository) GetDocumentsByUserID(userID string) ([]*model.Document, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, name, type, size, mime_type, content, status, error_msg, created_at, updated_at
		FROM documents WHERE user_id = ? ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var docs []*model.Document
	for rows.Next() {
		var doc model.Document
		var createdAt, updatedAt string
		err := rows.Scan(&doc.ID, &doc.UserID, &doc.Name, &doc.Type, &doc.Size, &doc.MIMEType, &doc.Content, &doc.Status, &doc.ErrorMsg, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}
		doc.CreatedAt = formatDateTime(createdAt)
		doc.UpdatedAt = formatDateTime(updatedAt)
		docs = append(docs, &doc)
	}
	return docs, nil
}

// UpdateDocument 更新文档
func (r *sqliteRepository) UpdateDocument(doc *model.Document) error {
	_, err := r.db.Exec(`
		UPDATE documents SET name = ?, type = ?, size = ?, mime_type = ?, content = ?, status = ?, error_msg = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`,
		doc.Name, doc.Type, doc.Size, doc.MIMEType, doc.Content, doc.Status, doc.ErrorMsg, doc.ID)
	return err
}

// DeleteDocument 删除文档
func (r *sqliteRepository) DeleteDocument(id, userID string) error {
	_, err := r.db.Exec(`DELETE FROM documents WHERE id = ? AND user_id = ?`, id, userID)
	if err != nil {
		return err
	}
	_, err = r.db.Exec(`DELETE FROM document_chunks WHERE document_id = ?`, id)
	return err
}

// CreateDocumentChunk 创建文档切片
func (r *sqliteRepository) CreateDocumentChunk(chunk *model.DocumentChunk) error {
	_, err := r.db.Exec(`
		INSERT INTO document_chunks (id, document_id, chunk_index, content, embedding, metadata, created_at)
		VALUES (?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		chunk.ID, chunk.DocumentID, chunk.ChunkIndex, chunk.Content, chunk.Embedding, chunk.Metadata)
	return err
}

// GetDocumentChunksByUserID 根据用户ID获取文档切片
func (r *sqliteRepository) GetDocumentChunksByUserID(userID string) ([]*model.DocumentChunk, error) {
	rows, err := r.db.Query(`
		SELECT dc.id, dc.document_id, dc.chunk_index, dc.content, dc.embedding, dc.metadata, dc.created_at
		FROM document_chunks dc
		JOIN documents d ON dc.document_id = d.id
		WHERE d.user_id = ?`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chunks []*model.DocumentChunk
	for rows.Next() {
		var chunk model.DocumentChunk
		var createdAt string
		err := rows.Scan(&chunk.ID, &chunk.DocumentID, &chunk.ChunkIndex, &chunk.Content, &chunk.Embedding, &chunk.Metadata, &createdAt)
		if err != nil {
			return nil, err
		}
		chunk.CreatedAt = formatDateTime(createdAt)
		chunks = append(chunks, &chunk)
	}
	return chunks, nil
}

// GetAllDocumentChunks 获取所有文档切片
func (r *sqliteRepository) GetAllDocumentChunks() ([]*model.DocumentChunk, error) {
	rows, err := r.db.Query(`
		SELECT id, document_id, chunk_index, content, embedding, metadata, created_at
		FROM document_chunks ORDER BY document_id, chunk_index`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chunks []*model.DocumentChunk
	for rows.Next() {
		var chunk model.DocumentChunk
		var createdAt string
		err := rows.Scan(&chunk.ID, &chunk.DocumentID, &chunk.ChunkIndex, &chunk.Content, &chunk.Embedding, &chunk.Metadata, &createdAt)
		if err != nil {
			return nil, err
		}
		chunk.CreatedAt = formatDateTime(createdAt)
		chunks = append(chunks, &chunk)
	}
	return chunks, nil
}

// GetDocumentChunksByDocumentID 根据文档ID获取文档切片
func (r *sqliteRepository) GetDocumentChunksByDocumentID(documentID string) ([]*model.DocumentChunk, error) {
	rows, err := r.db.Query(`
		SELECT id, document_id, chunk_index, content, embedding, metadata, created_at
		FROM document_chunks WHERE document_id = ? ORDER BY chunk_index`, documentID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chunks []*model.DocumentChunk
	for rows.Next() {
		var chunk model.DocumentChunk
		var createdAt string
		err := rows.Scan(&chunk.ID, &chunk.DocumentID, &chunk.ChunkIndex, &chunk.Content, &chunk.Embedding, &chunk.Metadata, &createdAt)
		if err != nil {
			return nil, err
		}
		chunk.CreatedAt = formatDateTime(createdAt)
		chunks = append(chunks, &chunk)
	}
	return chunks, nil
}

// CreateEmbedding 创建向量嵌入
func (r *sqliteRepository) CreateEmbedding(embedding *model.Embedding) error {
	_, err := r.db.Exec(`
		INSERT OR REPLACE INTO embeddings (id, content_hash, embedding, source_type, source_id, source_name, user_id, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		embedding.ID, embedding.ContentHash, embedding.Embedding, embedding.SourceType, embedding.SourceID, embedding.SourceName, embedding.UserID)
	return err
}

// GetEmbeddingByContentHash 根据内容哈希获取向量
func (r *sqliteRepository) GetEmbeddingByContentHash(contentHash string) (*model.Embedding, error) {
	var emb model.Embedding
	var createdAt string
	err := r.db.QueryRow(`
		SELECT id, content_hash, embedding, source_type, source_id, source_name, user_id, created_at
		FROM embeddings WHERE content_hash = ?`, contentHash).Scan(
		&emb.ID, &emb.ContentHash, &emb.Embedding, &emb.SourceType, &emb.SourceID, &emb.SourceName, &emb.UserID, &createdAt)
	if err != nil {
		return nil, err
	}
	emb.CreatedAt = formatDateTime(createdAt)
	return &emb, nil
}

// GetEmbeddingsByUserID 根据用户ID获取向量列表
func (r *sqliteRepository) GetEmbeddingsByUserID(userID string) ([]*model.Embedding, error) {
	rows, err := r.db.Query(`
		SELECT id, content_hash, embedding, source_type, source_id, source_name, user_id, created_at
		FROM embeddings WHERE user_id = ? ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var embeddings []*model.Embedding
	for rows.Next() {
		var emb model.Embedding
		var createdAt string
		err := rows.Scan(&emb.ID, &emb.ContentHash, &emb.Embedding, &emb.SourceType, &emb.SourceID, &emb.SourceName, &emb.UserID, &createdAt)
		if err != nil {
			return nil, err
		}
		emb.CreatedAt = formatDateTime(createdAt)
		embeddings = append(embeddings, &emb)
	}
	return embeddings, nil
}

// GetAllEmbeddings 获取所有向量
func (r *sqliteRepository) GetAllEmbeddings() ([]*model.Embedding, error) {
	rows, err := r.db.Query(`
		SELECT id, content_hash, embedding, source_type, source_id, source_name, user_id, created_at
		FROM embeddings ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var embeddings []*model.Embedding
	for rows.Next() {
		var emb model.Embedding
		var createdAt string
		err := rows.Scan(&emb.ID, &emb.ContentHash, &emb.Embedding, &emb.SourceType, &emb.SourceID, &emb.SourceName, &emb.UserID, &createdAt)
		if err != nil {
			return nil, err
		}
		emb.CreatedAt = formatDateTime(createdAt)
		embeddings = append(embeddings, &emb)
	}
	return embeddings, nil
}

// DeleteEmbedding 删除向量
func (r *sqliteRepository) DeleteEmbedding(id string) error {
	_, err := r.db.Exec(`DELETE FROM embeddings WHERE id = ?`, id)
	return err
}

// DeleteEmbeddingsBySource 根据来源类型和来源ID删除向量
func (r *sqliteRepository) DeleteEmbeddingsBySource(sourceType, sourceID string) error {
	_, err := r.db.Exec(`DELETE FROM embeddings WHERE source_type = ? AND source_id = ?`, sourceType, sourceID)
	return err
}

// CreateChatHistory 创建对话历史
func (r *sqliteRepository) CreateChatHistory(history *model.ChatHistory) error {
	_, err := r.db.Exec(`
		INSERT INTO chat_history (id, user_id, question, answer, context, created_at)
		VALUES (?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		history.ID, history.UserID, history.Question, history.Answer, history.Context)
	return err
}

// GetChatHistoryByUserID 根据用户ID获取对话历史
func (r *sqliteRepository) GetChatHistoryByUserID(userID string) ([]*model.ChatHistory, error) {
	rows, err := r.db.Query(`
		SELECT id, user_id, question, answer, context, created_at
		FROM chat_history WHERE user_id = ? ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var histories []*model.ChatHistory
	for rows.Next() {
		var history model.ChatHistory
		var createdAt string
		err := rows.Scan(&history.ID, &history.UserID, &history.Question, &history.Answer, &history.Context, &createdAt)
		if err != nil {
			return nil, err
		}
		history.CreatedAt = formatDateTime(createdAt)
		histories = append(histories, &history)
	}
	return histories, nil
}

func (r *sqliteRepository) GetAllMenus() ([]*model.Menu, error) {
	rows, err := r.db.Query(`SELECT id, name, icon, path, parent_id, sort_order, is_fixed, is_enabled, created_at, updated_at FROM menus ORDER BY sort_order ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var menus []*model.Menu
	for rows.Next() {
		var menu model.Menu
		var isFixed, isEnabled int
		var createdAt, updatedAt string
		err := rows.Scan(&menu.ID, &menu.Name, &menu.Icon, &menu.Path, &menu.ParentID, &menu.SortOrder, &isFixed, &isEnabled, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}
		menu.IsFixed = isFixed == 1
		menu.IsEnabled = isEnabled == 1
		menu.CreatedAt = formatDateTime(createdAt)
		menu.UpdatedAt = formatDateTime(updatedAt)
		menus = append(menus, &menu)
	}
	return menus, nil
}

func (r *sqliteRepository) CreateMenu(menu *model.Menu) error {
	_, err := r.db.Exec(`
		INSERT INTO menus (id, name, icon, path, parent_id, sort_order, is_fixed, is_enabled, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
		menu.ID, menu.Name, menu.Icon, menu.Path, menu.ParentID, menu.SortOrder,
		map[bool]int{true: 1, false: 0}[menu.IsFixed], map[bool]int{true: 1, false: 0}[menu.IsEnabled])
	return err
}

func (r *sqliteRepository) UpdateMenu(id string, menu *model.Menu) error {
	_, err := r.db.Exec(`
		UPDATE menus SET name = ?, icon = ?, path = ?, parent_id = ?, sort_order = ?, is_fixed = ?, is_enabled = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`,
		menu.Name, menu.Icon, menu.Path, menu.ParentID, menu.SortOrder,
		map[bool]int{true: 1, false: 0}[menu.IsFixed], map[bool]int{true: 1, false: 0}[menu.IsEnabled], id)
	return err
}

func (r *sqliteRepository) DeleteMenu(id string) error {
	var isFixed int
	err := r.db.QueryRow(`SELECT is_fixed FROM menus WHERE id = ?`, id).Scan(&isFixed)
	if err != nil {
		return err
	}
	if isFixed == 1 {
		return errors.New("cannot delete fixed menu")
	}
	_, err = r.db.Exec(`DELETE FROM menus WHERE id = ?`, id)
	return err
}

func (r *sqliteRepository) GetSystemConfig() (*model.SystemConfig, error) {
	var config model.SystemConfig
	var createdAt, updatedAt string
	err := r.db.QueryRow(`SELECT id, website_domain, website_logo, logo_path, website_title, website_desc, website_description, website_keywords, seo_keywords, seo_description, admin_email, admin_name, database_path, admin_page_size, created_at, updated_at FROM system_config LIMIT 1`).Scan(
		&config.ID, &config.WebsiteDomain, &config.WebsiteLogo, &config.LogoPath,
		&config.WebsiteTitle, &config.WebsiteDesc, &config.WebsiteDescription, &config.WebsiteKeywords,
		&config.SEOKeywords, &config.SEODescription, &config.AdminEmail, &config.AdminName,
		&config.DatabasePath, &config.AdminPageSize, &createdAt, &updatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, os.ErrNotExist
		}
		return nil, err
	}
	config.CreatedAt = formatDateTime(createdAt)
	config.UpdatedAt = formatDateTime(updatedAt)
	return &config, nil
}

func (r *sqliteRepository) CreateSystemConfig(config *model.SystemConfig) error {
	_, err := r.db.Exec(`
		INSERT INTO system_config (id, website_domain, website_logo, logo_path, website_title, website_desc, website_description, website_keywords, seo_keywords, seo_description, admin_email, admin_name, database_path, admin_page_size, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
		config.ID, config.WebsiteDomain, config.WebsiteLogo, config.LogoPath,
		config.WebsiteTitle, config.WebsiteDesc, config.WebsiteDescription, config.WebsiteKeywords,
		config.SEOKeywords, config.SEODescription, config.AdminEmail, config.AdminName,
		config.DatabasePath, config.AdminPageSize)
	return err
}

func (r *sqliteRepository) UpdateSystemConfig(id string, config *model.SystemConfig) error {
	_, err := r.db.Exec(`
		UPDATE system_config SET website_domain = ?, website_logo = ?, logo_path = ?, website_title = ?, website_desc = ?, website_description = ?, website_keywords = ?, seo_keywords = ?, seo_description = ?, admin_email = ?, admin_name = ?, database_path = ?, admin_page_size = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`,
		config.WebsiteDomain, config.WebsiteLogo, config.LogoPath,
		config.WebsiteTitle, config.WebsiteDesc, config.WebsiteDescription, config.WebsiteKeywords,
		config.SEOKeywords, config.SEODescription, config.AdminEmail, config.AdminName,
		config.DatabasePath, config.AdminPageSize, id)
	return err
}

func (r *sqliteRepository) CleanSystemConfig() error {
	_, err := r.db.Exec("DELETE FROM system_config WHERE id != 'default'")
	return err
}

func (r *sqliteRepository) CreateLoginHistory(history *model.LoginHistory) error {
	_, err := r.db.Exec(`
		INSERT INTO login_history (id, user_id, user_name, ip, location, user_agent, success, message, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))`,
		history.ID, history.UserID, history.UserName, history.IP,
		history.Location, history.UserAgent, history.Success, history.Message)
	return err
}

func (r *sqliteRepository) GetLoginHistory(page, pageSize int) ([]*model.LoginHistory, int, error) {
	var total int
	err := r.db.QueryRow("SELECT COUNT(*) FROM login_history").Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	offset := (page - 1) * pageSize
	rows, err := r.db.Query(`
		SELECT id, user_id, user_name, ip, location, user_agent, success, message, created_at
		FROM login_history ORDER BY created_at DESC LIMIT ? OFFSET ?`, pageSize, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var histories []*model.LoginHistory
	for rows.Next() {
		var history model.LoginHistory
		var success int
		err := rows.Scan(&history.ID, &history.UserID, &history.UserName, &history.IP,
			&history.Location, &history.UserAgent, &success, &history.Message, &history.CreatedAt)
		if err != nil {
			return nil, 0, err
		}
		history.Success = success == 1
		histories = append(histories, &history)
	}

	return histories, total, nil
}

// CreateNotification 创建通知
func (r *sqliteRepository) CreateNotification(n *model.Notification) error {
	if n.ID == "" {
		n.ID = uuid.NewString()
	}
	if n.CreatedAt == "" {
		n.CreatedAt = time.Now().Format("2006-01-02 15:04:05")
	}
	isRead := 0
	if n.IsRead {
		isRead = 1
	}
	_, err := r.db.Exec(`
		INSERT INTO notifications (id, type, title, message, url, is_read, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		n.ID, n.Type, n.Title, n.Message, n.URL, isRead, n.CreatedAt)
	return err
}

// GetNotifications 获取所有通知，按时间倒序
func (r *sqliteRepository) GetNotifications() ([]*model.Notification, error) {
	rows, err := r.db.Query(`
		SELECT id, type, title, message, url, is_read, created_at
		FROM notifications ORDER BY created_at DESC LIMIT 50`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var notifications []*model.Notification
	for rows.Next() {
		var n model.Notification
		var isRead int
		err := rows.Scan(&n.ID, &n.Type, &n.Title, &n.Message, &n.URL, &isRead, &n.CreatedAt)
		if err != nil {
			return nil, err
		}
		n.IsRead = isRead == 1
		notifications = append(notifications, &n)
	}
	return notifications, nil
}

// GetUnreadNotificationCount 获取未读通知数量
func (r *sqliteRepository) GetUnreadNotificationCount() (int, error) {
	var count int
	err := r.db.QueryRow("SELECT COUNT(*) FROM notifications WHERE is_read = 0").Scan(&count)
	return count, err
}

// MarkNotificationAsRead 标记单条通知为已读
func (r *sqliteRepository) MarkNotificationAsRead(id string) error {
	_, err := r.db.Exec("UPDATE notifications SET is_read = 1 WHERE id = ?", id)
	return err
}

// MarkAllNotificationsAsRead 标记所有通知为已读
func (r *sqliteRepository) MarkAllNotificationsAsRead() error {
	_, err := r.db.Exec("UPDATE notifications SET is_read = 1 WHERE is_read = 0")
	return err
}

// DeleteAllNotifications 删除所有通知
func (r *sqliteRepository) DeleteAllNotifications() error {
	_, err := r.db.Exec("DELETE FROM notifications")
	return err
}

// DeleteNotification 删除单条通知
func (r *sqliteRepository) DeleteNotification(id string) error {
	_, err := r.db.Exec("DELETE FROM notifications WHERE id = ?", id)
	return err
}

// populateResumeNames 迁移数据：从加密的basic_info中提取name字段并填充到name列
func (r *sqliteRepository) populateResumeNames() error {
	var count int
	err := r.db.QueryRow("SELECT COUNT(*) FROM resumes WHERE name IS NULL AND basic_info IS NOT NULL").Scan(&count)
	if err != nil {
		return err
	}

	if count == 0 {
		return nil
	}

	rows, err := r.db.Query("SELECT id, basic_info FROM resumes WHERE name IS NULL AND basic_info IS NOT NULL")
	if err != nil {
		return err
	}

	type nameUpdate struct {
		id   string
		name string
	}
	var updates []nameUpdate

	for rows.Next() {
		var id, basicInfoJSON string
		err := rows.Scan(&id, &basicInfoJSON)
		if err != nil {
			rows.Close()
			return err
		}

		var basicInfo model.BasicInfo
		if r.enabled && basicInfoJSON != "" {
			decrypted, decryptErr := r.decrypt(basicInfoJSON)
			if decryptErr == nil {
				json.Unmarshal([]byte(decrypted), &basicInfo)
			}
		} else if basicInfoJSON != "" {
			json.Unmarshal([]byte(basicInfoJSON), &basicInfo)
		}

		if basicInfo.Name != "" {
			updates = append(updates, nameUpdate{id: id, name: basicInfo.Name})
		}
	}
	rows.Close()

	if len(updates) == 0 {
		return nil
	}

	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	for _, u := range updates {
		_, err := tx.Exec("UPDATE resumes SET name = ? WHERE id = ?", u.name, u.id)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}