package db

import (
	"database/sql"
	"fmt"
	"time"

	"pdf-wizard/internal/models"
)

func GetPDFByMD5(db *sql.DB, md5Hash string) (*models.PDF, error) {
	var pdf models.PDF
	var errMsg, llmErrMsg sql.NullString
	var customMD sql.NullString
	err := db.QueryRow("SELECT id, url, md5_hash, org_id, status, error, custom_markdown, llm_status, llm_error, created_at FROM pdfs WHERE md5_hash = ?", md5Hash).
		Scan(&pdf.ID, &pdf.URL, &pdf.MD5Hash, &pdf.OrgID, &pdf.Status, &errMsg, &customMD, &pdf.LLMStatus, &llmErrMsg, &pdf.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying pdf by md5: %w", err)
	}
	if errMsg.Valid {
		pdf.Error = errMsg.String
	}
	if customMD.Valid {
		pdf.CustomMarkdown = &customMD.String
	}
	if llmErrMsg.Valid {
		pdf.LLMError = &llmErrMsg.String
	}
	return &pdf, nil
}

func CreatePDF(db *sql.DB, url, md5Hash string, orgID *int) (*models.PDF, error) {
	var pdf models.PDF
	var errMsg, customMD sql.NullString
	err := db.QueryRow(
		"INSERT INTO pdfs (url, md5_hash, org_id, status) VALUES (?, ?, ?, 'pending') RETURNING id, url, md5_hash, org_id, status, error, custom_markdown, llm_status, created_at",
		url, md5Hash, orgID,
	).Scan(&pdf.ID, &pdf.URL, &pdf.MD5Hash, &pdf.OrgID, &pdf.Status, &errMsg, &customMD, &pdf.LLMStatus, &pdf.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("creating pdf record: %w", err)
	}
	if errMsg.Valid {
		pdf.Error = errMsg.String
	}
	if customMD.Valid {
		v := customMD.String
		pdf.CustomMarkdown = &v
	}
	return &pdf, nil
}

func UpdatePDFStatus(db *sql.DB, id int, status string) error {
	_, err := db.Exec("UPDATE pdfs SET status = ? WHERE id = ?", status, id)
	return err
}

func UpdatePDFStatusWithError(db *sql.DB, id int, status, errMsg string) error {
	_, err := db.Exec("UPDATE pdfs SET status = ?, error = ? WHERE id = ?", status, errMsg, id)
	return err
}

func UpdatePDFLLMStatus(db *sql.DB, id int, status string) error {
	_, err := db.Exec("UPDATE pdfs SET llm_status = ? WHERE id = ?", status, id)
	return err
}

func UpdatePDFLLMStatusWithError(db *sql.DB, id int, status, errMsg string) error {
	_, err := db.Exec("UPDATE pdfs SET llm_status = ?, llm_error = ? WHERE id = ?", status, errMsg, id)
	return err
}

func GetPDF(db *sql.DB, id int) (*models.PDF, error) {
	var pdf models.PDF
	var errMsg, llmErrMsg sql.NullString
	var customMD sql.NullString
	err := db.QueryRow("SELECT id, url, md5_hash, org_id, status, error, custom_markdown, llm_status, llm_error, created_at FROM pdfs WHERE id = ?", id).
		Scan(&pdf.ID, &pdf.URL, &pdf.MD5Hash, &pdf.OrgID, &pdf.Status, &errMsg, &customMD, &pdf.LLMStatus, &llmErrMsg, &pdf.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("pdf not found: %d", id)
	}
	if err != nil {
		return nil, fmt.Errorf("querying pdf: %w", err)
	}
	if errMsg.Valid {
		pdf.Error = errMsg.String
	}
	if customMD.Valid {
		pdf.CustomMarkdown = &customMD.String
	}
	if llmErrMsg.Valid {
		pdf.LLMError = &llmErrMsg.String
	}
	return &pdf, nil
}

func CreateFormField(db *sql.DB, field models.FormField) error {
	_, err := db.Exec(
		"INSERT INTO form_fields (pdf_id, field_index, field_name, type, custom_label, group_id, llm_suggested_type) VALUES (?, ?, ?, ?, ?, ?, ?)",
		field.PDFID, field.FieldIndex, field.FieldName, field.Type, field.CustomLabel, field.GroupID, field.LLMSuggestedType,
	)
	if err != nil {
		return fmt.Errorf("creating form field: %w", err)
	}
	return nil
}

func GetFormFields(db *sql.DB, pdfID int) ([]models.FormField, error) {
	rows, err := db.Query(
		"SELECT id, pdf_id, field_index, field_name, type, custom_label, group_id, llm_suggested_type FROM form_fields WHERE pdf_id = ? ORDER BY field_index, id",
		pdfID,
	)
	if err != nil {
		return nil, fmt.Errorf("querying form fields: %w", err)
	}
	defer rows.Close()

	var fields []models.FormField
	for rows.Next() {
		var f models.FormField
		var cl sql.NullString
		var gid sql.NullInt64
		var lst sql.NullString
		err := rows.Scan(&f.ID, &f.PDFID, &f.FieldIndex, &f.FieldName, &f.Type, &cl, &gid, &lst)
		if err != nil {
			return nil, fmt.Errorf("scanning form field: %w", err)
		}
		if cl.Valid {
			f.CustomLabel = &cl.String
		}
		if gid.Valid {
			v := int(gid.Int64)
			f.GroupID = &v
		}
		if lst.Valid {
			f.LLMSuggestedType = &lst.String
		}
		fields = append(fields, f)
	}
	return fields, rows.Err()
}

func UpdateFormFieldLabel(db *sql.DB, fieldID int, label string) error {
	_, err := db.Exec("UPDATE form_fields SET custom_label = ? WHERE id = ?", label, fieldID)
	return err
}

func UpdateFormFieldType(db *sql.DB, fieldID int, fieldtype string) error {
	_, err := db.Exec("UPDATE form_fields SET type = ? WHERE id = ?", fieldtype, fieldID)
	return err
}

func UpdateFormFieldGroup(db *sql.DB, fieldID int, groupID int) error {
	_, err := db.Exec("UPDATE form_fields SET group_id = ? WHERE id = ?", groupID, fieldID)
	return err
}

func ClearFormFieldGroup(db *sql.DB, fieldID int) error {
	_, err := db.Exec("UPDATE form_fields SET group_id = NULL WHERE id = ?", fieldID)
	return err
}

func UpdateFormFieldLLMType(db *sql.DB, fieldID int, suggestedType string) error {
	_, err := db.Exec("UPDATE form_fields SET llm_suggested_type = ? WHERE id = ?", suggestedType, fieldID)
	return err
}

// Org functions

func CreateOrg(db *sql.DB, name string) (*models.Org, error) {
	var org models.Org
	err := db.QueryRow(
		"INSERT INTO orgs (name) VALUES (?) RETURNING id, name, created_at, updated_at",
		name,
	).Scan(&org.ID, &org.Name, &org.CreatedAt, &org.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("creating org: %w", err)
	}
	return &org, nil
}

func GetOrgByName(db *sql.DB, name string) (*models.Org, error) {
	var org models.Org
	err := db.QueryRow("SELECT id, name, created_at, updated_at FROM orgs WHERE name = ?", name).
		Scan(&org.ID, &org.Name, &org.CreatedAt, &org.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying org: %w", err)
	}
	return &org, nil
}

func GetOrg(db *sql.DB, id int) (*models.Org, error) {
	var org models.Org
	err := db.QueryRow("SELECT id, name, created_at, updated_at FROM orgs WHERE id = ?", id).
		Scan(&org.ID, &org.Name, &org.CreatedAt, &org.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("org not found: %d", id)
	}
	if err != nil {
		return nil, fmt.Errorf("querying org: %w", err)
	}
	return &org, nil
}

func ListOrgs(db *sql.DB) ([]models.Org, error) {
	rows, err := db.Query("SELECT id, name, created_at, updated_at FROM orgs ORDER BY name")
	if err != nil {
		return nil, fmt.Errorf("listing orgs: %w", err)
	}
	defer rows.Close()

	var orgs []models.Org
	for rows.Next() {
		var o models.Org
		if err := rows.Scan(&o.ID, &o.Name, &o.CreatedAt, &o.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scanning org: %w", err)
		}
		orgs = append(orgs, o)
	}
	return orgs, rows.Err()
}

func DeleteOrg(db *sql.DB, id int) error {
	_, err := db.Exec("DELETE FROM orgs WHERE id = ?", id)
	return err
}

// OrgDomain functions

func AddOrgDomain(db *sql.DB, orgID int, domain string) (*models.OrgDomain, error) {
	var od models.OrgDomain
	err := db.QueryRow(
		"INSERT INTO org_domains (org_id, domain) VALUES (?, ?) RETURNING id, org_id, domain, created_at, updated_at",
		orgID, domain,
	).Scan(&od.ID, &od.OrgID, &od.Domain, &od.CreatedAt, &od.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("adding org domain: %w", err)
	}
	return &od, nil
}

func GetOrgDomains(db *sql.DB, orgID int) ([]models.OrgDomain, error) {
	rows, err := db.Query("SELECT id, org_id, domain, created_at, updated_at FROM org_domains WHERE org_id = ? ORDER BY domain", orgID)
	if err != nil {
		return nil, fmt.Errorf("querying org domains: %w", err)
	}
	defer rows.Close()

	var domains []models.OrgDomain
	for rows.Next() {
		var d models.OrgDomain
		if err := rows.Scan(&d.ID, &d.OrgID, &d.Domain, &d.CreatedAt, &d.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scanning org domain: %w", err)
		}
		domains = append(domains, d)
	}
	return domains, rows.Err()
}

func DeleteOrgDomain(db *sql.DB, orgID int, domain string) error {
	_, err := db.Exec("DELETE FROM org_domains WHERE org_id = ? AND domain = ?", orgID, domain)
	return err
}

func ResolveOrgByDomain(db *sql.DB, domain string) (*int, error) {
	var orgID int
	err := db.QueryRow("SELECT org_id FROM org_domains WHERE domain = ?", domain).Scan(&orgID)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("resolving org by domain: %w", err)
	}
	return &orgID, nil
}

// Custom markdown

func UpdateCustomMarkdown(db *sql.DB, pdfID int, markdown string) error {
	_, err := db.Exec("UPDATE pdfs SET custom_markdown = ? WHERE id = ?", markdown, pdfID)
	return err
}

func ClearCustomMarkdown(db *sql.DB, pdfID int) error {
	_, err := db.Exec("UPDATE pdfs SET custom_markdown = NULL WHERE id = ?", pdfID)
	return err
}

// Markdown version history

func CreateMarkdownVersion(db *sql.DB, pdfID int, markdown string) (int64, error) {
	result, err := db.Exec("INSERT INTO markdown_versions (pdf_id, markdown) VALUES (?, ?)", pdfID, markdown)
	if err != nil {
		return 0, fmt.Errorf("creating markdown version: %w", err)
	}
	return result.LastInsertId()
}

func ListMarkdownVersions(db *sql.DB, pdfID int) ([]models.MarkdownVersion, error) {
	rows, err := db.Query(
		"SELECT id, pdf_id, created_at FROM markdown_versions WHERE pdf_id = ? ORDER BY created_at DESC",
		pdfID,
	)
	if err != nil {
		return nil, fmt.Errorf("querying markdown versions: %w", err)
	}
	defer rows.Close()

	var versions []models.MarkdownVersion
	for rows.Next() {
		var v models.MarkdownVersion
		if err := rows.Scan(&v.ID, &v.PDFID, &v.CreatedAt); err != nil {
			return nil, fmt.Errorf("scanning markdown version: %w", err)
		}
		versions = append(versions, v)
	}
	return versions, rows.Err()
}

func GetMarkdownVersion(db *sql.DB, versionID int) (*models.MarkdownVersion, error) {
	var v models.MarkdownVersion
	var markdown string
	err := db.QueryRow(
		"SELECT id, pdf_id, markdown, created_at FROM markdown_versions WHERE id = ?",
		versionID,
	).Scan(&v.ID, &v.PDFID, &markdown, &v.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying markdown version: %w", err)
	}
	v.Markdown = markdown
	return &v, nil
}

// Prompt version history

func CreatePromptVersion(db *sql.DB, overrideID int, content string) (int64, error) {
	result, err := db.Exec("INSERT INTO prompt_versions (prompt_override_id, prompt_content) VALUES (?, ?)", overrideID, content)
	if err != nil {
		return 0, fmt.Errorf("creating prompt version: %w", err)
	}
	return result.LastInsertId()
}

func ListPromptVersions(db *sql.DB, overrideID int) ([]models.PromptVersion, error) {
	rows, err := db.Query(
		"SELECT id, prompt_override_id, created_at FROM prompt_versions WHERE prompt_override_id = ? ORDER BY created_at DESC",
		overrideID,
	)
	if err != nil {
		return nil, fmt.Errorf("querying prompt versions: %w", err)
	}
	defer rows.Close()

	var versions []models.PromptVersion
	for rows.Next() {
		var v models.PromptVersion
		if err := rows.Scan(&v.ID, &v.OverrideID, &v.CreatedAt); err != nil {
			return nil, fmt.Errorf("scanning prompt version: %w", err)
		}
		versions = append(versions, v)
	}
	return versions, rows.Err()
}

func GetPromptVersion(db *sql.DB, versionID int) (*models.PromptVersion, error) {
	var v models.PromptVersion
	var content string
	err := db.QueryRow(
		"SELECT id, prompt_override_id, prompt_content, created_at FROM prompt_versions WHERE id = ?",
		versionID,
	).Scan(&v.ID, &v.OverrideID, &content, &v.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("querying prompt version: %w", err)
	}
	v.Content = content
	return &v, nil
}

// User functions

func CreateUser(db *sql.DB, username, passwordHash string) (*models.User, error) {
	var user models.User
	err := db.QueryRow(
		"INSERT INTO users (username, password_hash) VALUES (?, ?) RETURNING id, username, created_at",
		username, passwordHash,
	).Scan(&user.ID, &user.Username, &user.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("creating user: %w", err)
	}
	return &user, nil
}

func GetUserByUsername(db *sql.DB, username string) (*models.User, error) {
	var user models.User
	err := db.QueryRow("SELECT id, username, password_hash, created_at FROM users WHERE username = ?", username).
		Scan(&user.ID, &user.Username, &user.PasswordHash, &user.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying user: %w", err)
	}
	return &user, nil
}

func GetUser(db *sql.DB, id int) (*models.User, error) {
	var user models.User
	err := db.QueryRow("SELECT id, username, created_at FROM users WHERE id = ?", id).
		Scan(&user.ID, &user.Username, &user.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("user not found: %d", id)
	}
	if err != nil {
		return nil, fmt.Errorf("querying user: %w", err)
	}
	return &user, nil
}

func ListUsers(db *sql.DB) ([]models.User, error) {
	rows, err := db.Query("SELECT id, username, created_at FROM users ORDER BY username")
	if err != nil {
		return nil, fmt.Errorf("listing users: %w", err)
	}
	defer rows.Close()

	var users []models.User
	for rows.Next() {
		var u models.User
		if err := rows.Scan(&u.ID, &u.Username, &u.CreatedAt); err != nil {
			return nil, fmt.Errorf("scanning user: %w", err)
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func DeleteUser(db *sql.DB, id int) error {
	_, err := db.Exec("DELETE FROM users WHERE id = ?", id)
	return err
}

// UserOrg functions

func AssignUserToOrg(db *sql.DB, userID, orgID int) error {
	_, err := db.Exec("INSERT INTO user_orgs (user_id, org_id) VALUES (?, ?)", userID, orgID)
	if err != nil {
		return fmt.Errorf("assigning user to org: %w", err)
	}
	return nil
}

func RemoveUserFromOrg(db *sql.DB, userID, orgID int) error {
	_, err := db.Exec("DELETE FROM user_orgs WHERE user_id = ? AND org_id = ?", userID, orgID)
	return err
}

func GetUserOrgs(db *sql.DB, userID int) ([]models.Org, error) {
	rows, err := db.Query(
		"SELECT o.id, o.name, o.created_at, o.updated_at FROM orgs o JOIN user_orgs uo ON uo.org_id = o.id WHERE uo.user_id = ? ORDER BY o.name",
		userID,
	)
	if err != nil {
		return nil, fmt.Errorf("querying user orgs: %w", err)
	}
	defer rows.Close()

	var orgs []models.Org
	for rows.Next() {
		var o models.Org
		if err := rows.Scan(&o.ID, &o.Name, &o.CreatedAt, &o.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scanning org: %w", err)
		}
		orgs = append(orgs, o)
	}
	return orgs, rows.Err()
}

func IsUserInOrg(db *sql.DB, userID, orgID int) (bool, error) {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM user_orgs WHERE user_id = ? AND org_id = ?", userID, orgID).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("checking user org membership: %w", err)
	}
	return count > 0, nil
}

// Session functions

func CreateSession(db *sql.DB, userID int, token string, duration time.Duration) (*models.Session, error) {
	expiresAt := time.Now().Add(duration)
	var session models.Session
	err := db.QueryRow(
		"INSERT INTO sessions (user_id, token, expires_at) VALUES (?, ?, ?) RETURNING id, user_id, token, expires_at",
		userID, token, expiresAt,
	).Scan(&session.ID, &session.UserID, &session.Token, &session.ExpiresAt)
	if err != nil {
		return nil, fmt.Errorf("creating session: %w", err)
	}
	return &session, nil
}

func GetSessionByToken(db *sql.DB, token string) (*models.Session, error) {
	var session models.Session
	err := db.QueryRow("SELECT id, user_id, token, expires_at FROM sessions WHERE token = ?", token).
		Scan(&session.ID, &session.UserID, &session.Token, &session.ExpiresAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying session: %w", err)
	}
	return &session, nil
}

func DeleteSession(db *sql.DB, token string) error {
	_, err := db.Exec("DELETE FROM sessions WHERE token = ?", token)
	return err
}

func CleanupExpiredSessions(db *sql.DB) error {
	_, err := db.Exec("DELETE FROM sessions WHERE expires_at < ?", time.Now())
	return err
}

// Admin PDF queries

func ListOrgPDFs(db *sql.DB, orgID int) ([]models.AdminPDF, error) {
	rows, err := db.Query(
		"SELECT id, url, status, llm_status, created_at, org_id FROM pdfs WHERE org_id = ? AND status = 'completed' ORDER BY created_at DESC",
		orgID,
	)
	if err != nil {
		return nil, fmt.Errorf("listing org PDFs: %w", err)
	}
	defer rows.Close()

	var pdfs []models.AdminPDF
	for rows.Next() {
		var p models.AdminPDF
		if err := rows.Scan(&p.ID, &p.URL, &p.Status, &p.LLMStatus, &p.CreatedAt, &p.OrgID); err != nil {
			return nil, fmt.Errorf("scanning pdf: %w", err)
		}
		pdfs = append(pdfs, p)
	}
	if pdfs == nil {
		pdfs = []models.AdminPDF{}
	}
	return pdfs, rows.Err()
}

// Field group queries

func CreateFieldGroup(db *sql.DB, pdfID int, groupType string) (*models.FieldGroup, error) {
	var fg models.FieldGroup
	err := db.QueryRow(
		"INSERT INTO field_groups (pdf_id, group_type) VALUES (?, ?) RETURNING id, pdf_id, group_type, created_at",
		pdfID, groupType,
	).Scan(&fg.ID, &fg.PDFID, &fg.GroupType, &fg.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("creating field group: %w", err)
	}
	return &fg, nil
}

func GetFieldGroups(db *sql.DB, pdfID int) ([]models.FieldGroup, error) {
	rows, err := db.Query("SELECT id, pdf_id, group_type, created_at FROM field_groups WHERE pdf_id = ? ORDER BY id", pdfID)
	if err != nil {
		return nil, fmt.Errorf("querying field groups: %w", err)
	}
	defer rows.Close()

	var groups []models.FieldGroup
	for rows.Next() {
		var g models.FieldGroup
		if err := rows.Scan(&g.ID, &g.PDFID, &g.GroupType, &g.CreatedAt); err != nil {
			return nil, fmt.Errorf("scanning field group: %w", err)
		}
		groups = append(groups, g)
	}
	return groups, rows.Err()
}

func AddFieldGroupMember(db *sql.DB, groupID, fieldID int) error {
	_, err := db.Exec("INSERT INTO field_group_members (group_id, field_id) VALUES (?, ?)", groupID, fieldID)
	if err != nil {
		return fmt.Errorf("adding field group member: %w", err)
	}
	return nil
}

func GetFieldGroupMembers(db *sql.DB, groupID int) ([]models.FieldGroupMember, error) {
	rows, err := db.Query("SELECT id, group_id, field_id FROM field_group_members WHERE group_id = ? ORDER BY id", groupID)
	if err != nil {
		return nil, fmt.Errorf("querying field group members: %w", err)
	}
	defer rows.Close()

	var members []models.FieldGroupMember
	for rows.Next() {
		var m models.FieldGroupMember
		if err := rows.Scan(&m.ID, &m.GroupID, &m.FieldID); err != nil {
			return nil, fmt.Errorf("scanning field group member: %w", err)
		}
		members = append(members, m)
	}
	return members, rows.Err()
}

func ClearFieldGroups(db *sql.DB, pdfID int) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("beginning transaction: %w", err)
	}
	defer tx.Rollback()

	_, err = tx.Exec("DELETE FROM field_group_members WHERE group_id IN (SELECT id FROM field_groups WHERE pdf_id = ?)", pdfID)
	if err != nil {
		return fmt.Errorf("deleting field group members: %w", err)
	}

	_, err = tx.Exec("DELETE FROM field_groups WHERE pdf_id = ?", pdfID)
	if err != nil {
		return fmt.Errorf("deleting field groups: %w", err)
	}

	_, err = tx.Exec("UPDATE form_fields SET group_id = NULL WHERE pdf_id = ?", pdfID)
	if err != nil {
		return fmt.Errorf("clearing field group assignments: %w", err)
	}

	return tx.Commit()
}

// Prompt override queries

func SavePromptOverride(db *sql.DB, orgID *int, pdfID *int, promptType, content string) (int, error) {
	var existingID int
	var query string
	var args []interface{}

	if pdfID != nil {
		query = "SELECT id FROM prompt_overrides WHERE org_id = ? AND pdf_id = ? AND prompt_type = ?"
		args = append(args, *orgID, *pdfID, promptType)
	} else {
		query = "SELECT id FROM prompt_overrides WHERE org_id = ? AND pdf_id IS NULL AND prompt_type = ?"
		args = append(args, *orgID, promptType)
	}

	err := db.QueryRow(query, args...).Scan(&existingID)

	if err == nil {
		_, err = db.Exec(
			"UPDATE prompt_overrides SET prompt_content = ?, is_active = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
			content, existingID,
		)
		if err != nil {
			return 0, fmt.Errorf("updating prompt override: %w", err)
		}
		return existingID, nil
	}

	if err != sql.ErrNoRows {
		return 0, fmt.Errorf("querying prompt override: %w", err)
	}

	result, err := db.Exec(
		"INSERT INTO prompt_overrides (org_id, pdf_id, prompt_type, prompt_content, is_active) VALUES (?, ?, ?, ?, 1)",
		orgID, pdfID, promptType, content,
	)
	if err != nil {
		return 0, fmt.Errorf("creating prompt override: %w", err)
	}
	newID, _ := result.LastInsertId()
	return int(newID), nil
}

func TogglePromptOverride(db *sql.DB, overrideID int, active bool) error {
	_, err := db.Exec("UPDATE prompt_overrides SET is_active = ? WHERE id = ?", active, overrideID)
	return err
}

func GetPromptOverride(db *sql.DB, orgID *int, pdfID *int, promptType string) (*models.PromptOverride, error) {
	var po models.PromptOverride
	var args []interface{}
	var query string

	// Build WHERE clause handling NULL properly
	if pdfID != nil {
		query = "SELECT id, org_id, pdf_id, prompt_type, prompt_content, is_active, created_at, updated_at FROM prompt_overrides WHERE org_id = ? AND pdf_id = ? AND prompt_type = ?"
		args = append(args, *orgID, *pdfID, promptType)
	} else {
		query = "SELECT id, org_id, pdf_id, prompt_type, prompt_content, is_active, created_at, updated_at FROM prompt_overrides WHERE org_id = ? AND pdf_id IS NULL AND prompt_type = ?"
		args = append(args, *orgID, promptType)
	}

	err := db.QueryRow(query, args...).Scan(&po.ID, &po.OrgID, &po.PDFID, &po.PromptType, &po.PromptContent, &po.IsActive, &po.CreatedAt, &po.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying prompt override: %w", err)
	}
	return &po, nil
}

func ListPromptOverridesForOrg(db *sql.DB, orgID int) ([]models.PromptOverride, error) {
	rows, err := db.Query(
		"SELECT id, org_id, pdf_id, prompt_type, prompt_content, is_active, created_at, updated_at FROM prompt_overrides WHERE org_id = ? ORDER BY prompt_type, pdf_id",
		orgID,
	)
	if err != nil {
		return nil, fmt.Errorf("listing prompt overrides: %w", err)
	}
	defer rows.Close()

	var overrides []models.PromptOverride
	for rows.Next() {
		var po models.PromptOverride
		var pdfID sql.NullInt64
		err := rows.Scan(&po.ID, &po.OrgID, &pdfID, &po.PromptType, &po.PromptContent, &po.IsActive, &po.CreatedAt, &po.UpdatedAt)
		if err != nil {
			return nil, fmt.Errorf("scanning prompt override: %w", err)
		}
		if pdfID.Valid {
			v := int(pdfID.Int64)
			po.PDFID = &v
		}
		overrides = append(overrides, po)
	}
	return overrides, rows.Err()
}

func DeletePromptOverride(db *sql.DB, id int) error {
	_, err := db.Exec("DELETE FROM prompt_overrides WHERE id = ?", id)
	return err
}

func GetPromptOverrideByID(db *sql.DB, id int) (*models.PromptOverride, error) {
	var po models.PromptOverride
	var pdfID sql.NullInt64
	err := db.QueryRow(
		"SELECT id, org_id, pdf_id, prompt_type, prompt_content, is_active, created_at, updated_at FROM prompt_overrides WHERE id = ?", id,
	).Scan(&po.ID, &po.OrgID, &pdfID, &po.PromptType, &po.PromptContent, &po.IsActive, &po.CreatedAt, &po.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("querying prompt override by ID: %w", err)
	}
	if pdfID.Valid {
		v := int(pdfID.Int64)
		po.PDFID = &v
	}
	return &po, nil
}

// ClearFormFieldsLabels resets custom labels for all fields of a PDF.
func ClearFormFieldsLabels(db *sql.DB, pdfID int) error {
	_, err := db.Exec("UPDATE form_fields SET custom_label = NULL WHERE pdf_id = ?", pdfID)
	return err
}

// ClearFormFieldsLLMData resets LLM-derived metadata for all fields of a PDF.
func ClearFormFieldsLLMData(db *sql.DB, pdfID int) error {
	_, err := db.Exec("UPDATE form_fields SET llm_suggested_type = NULL, group_id = NULL WHERE pdf_id = ?", pdfID)
	return err
}

// ResolvePrompt returns the effective prompt for a PDF.
// Priority: active PDF-level override > org-level override (always active) > default (nil)
func ResolvePrompt(db *sql.DB, pdfID int, pdfOrgID *int, promptType string) (string, error) {
	// PDF-level override — only used when is_active is true
	if pdfOrgID != nil {
		if po, err := GetPromptOverride(db, pdfOrgID, &pdfID, promptType); err != nil {
			return "", err
		} else if po != nil && po.IsActive {
			return po.PromptContent, nil
		}
	}

	// Org-level override — always active (no is_active check)
	if pdfOrgID != nil {
		if po, err := GetPromptOverride(db, pdfOrgID, nil, promptType); err != nil {
			return "", err
		} else if po != nil {
			return po.PromptContent, nil
		}
	}

	return "", nil
}