package db

import (
	"fmt"
)

// Profile operations

func (d *Database) GetOrCreateProfile() (*Profile, error) {
	p := &Profile{}
	err := d.db.QueryRow(`SELECT id, first_name, last_name, email, phone, location, website, summary, updated_at FROM profiles ORDER BY id DESC LIMIT 1`).Scan(
		&p.ID, &p.FirstName, &p.LastName, &p.Email, &p.Phone,
		&p.Location, &p.Website, &p.Summary, &p.UpdatedAt)
	if err != nil {
		_, err = d.db.Exec(`INSERT INTO profiles (first_name, last_name, email, phone, location, website, summary) VALUES ('','','','','','','')`)
		if err != nil {
			return nil, fmt.Errorf("failed to create default profile: %w", err)
		}
		// Direct fetch after insert (no recursion)
		return scanProfile(d.db.QueryRow(`SELECT id, first_name, last_name, email, phone, location, website, summary, updated_at FROM profiles ORDER BY id DESC LIMIT 1`))
	}
	return p, nil
}

func (d *Database) UpdateProfile(p *Profile) error {
	_, err := d.db.Exec(`UPDATE profiles SET first_name=?, last_name=?, email=?, phone=?, location=?, website=?, summary=?, updated_at=datetime('now') WHERE id=?`,
		p.FirstName, p.LastName, p.Email, p.Phone, p.Location, p.Website, p.Summary, p.ID)
	return err
}

// Experience operations

func (d *Database) GetExperiences() ([]Experience, error) {
	rows, err := d.db.Query(`SELECT id, company, title, start_date, end_date, location, description, sort_order, created_at FROM experiences ORDER BY sort_order ASC, start_date DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []Experience{}
	for rows.Next() {
		e := &Experience{}
		if err := rows.Scan(&e.ID, &e.Company, &e.Title, &e.StartDate, &e.EndDate,
			&e.Location, &e.Description, &e.SortOrder, &e.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, *e)
	}
	return result, nil
}

func (d *Database) CreateExperience(e *Experience) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO experiences (company, title, start_date, end_date, location, description, sort_order) VALUES (?,?,?,?,?,?,?)`,
		e.Company, e.Title, e.StartDate, e.EndDate, e.Location, e.Description, e.SortOrder)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateExperience(e *Experience) error {
	_, err := d.db.Exec(`UPDATE experiences SET company=?, title=?, start_date=?, end_date=?, location=?, description=?, sort_order=? WHERE id=?`,
		e.Company, e.Title, e.StartDate, e.EndDate, e.Location, e.Description, e.SortOrder, e.ID)
	return err
}

func (d *Database) DeleteExperience(id int64) error {
	_, err := d.db.Exec(`DELETE FROM experiences WHERE id=?`, id)
	return err
}

// Education operations

func (d *Database) GetEducations() ([]Education, error) {
	rows, err := d.db.Query(`SELECT id, institution, degree, field_of_study, start_date, end_date, location, gpa, description, sort_order, created_at FROM educations ORDER BY sort_order ASC, start_date DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []Education{}
	for rows.Next() {
		e := &Education{}
		if err := rows.Scan(&e.ID, &e.Institution, &e.Degree, &e.FieldOfStudy,
			&e.StartDate, &e.EndDate, &e.Location, &e.GPA, &e.Description,
			&e.SortOrder, &e.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, *e)
	}
	return result, nil
}

func (d *Database) CreateEducation(e *Education) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO educations (institution, degree, field_of_study, start_date, end_date, location, gpa, description, sort_order) VALUES (?,?,?,?,?,?,?,?,?)`,
		e.Institution, e.Degree, e.FieldOfStudy, e.StartDate, e.EndDate, e.Location, e.GPA, e.Description, e.SortOrder)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateEducation(e *Education) error {
	_, err := d.db.Exec(`UPDATE educations SET institution=?, degree=?, field_of_study=?, start_date=?, end_date=?, location=?, gpa=?, description=?, sort_order=? WHERE id=?`,
		e.Institution, e.Degree, e.FieldOfStudy, e.StartDate, e.EndDate, e.Location, e.GPA, e.Description, e.SortOrder, e.ID)
	return err
}

func (d *Database) DeleteEducation(id int64) error {
	_, err := d.db.Exec(`DELETE FROM educations WHERE id=?`, id)
	return err
}

// Skill operations

func (d *Database) GetSkills() ([]Skill, error) {
	rows, err := d.db.Query(`SELECT id, category, name, level, description, sort_order, created_at FROM skills ORDER BY category, sort_order ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []Skill{}
	for rows.Next() {
		s := &Skill{}
		if err := rows.Scan(&s.ID, &s.Category, &s.Name, &s.Level,
			&s.Description, &s.SortOrder, &s.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, *s)
	}
	return result, nil
}

func (d *Database) CreateSkill(s *Skill) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO skills (category, name, level, description, sort_order) VALUES (?,?,?,?,?)`,
		s.Category, s.Name, s.Level, s.Description, s.SortOrder)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateSkill(s *Skill) error {
	_, err := d.db.Exec(`UPDATE skills SET category=?, name=?, level=?, description=?, sort_order=? WHERE id=?`,
		s.Category, s.Name, s.Level, s.Description, s.SortOrder, s.ID)
	return err
}

func (d *Database) DeleteSkill(id int64) error {
	_, err := d.db.Exec(`DELETE FROM skills WHERE id=?`, id)
	return err
}

// Work sample operations

func (d *Database) GetWorkSamples() ([]WorkSample, error) {
	rows, err := d.db.Query(`SELECT id, title, description, url, content, created_at FROM work_samples ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []WorkSample{}
	for rows.Next() {
		w := &WorkSample{}
		if err := rows.Scan(&w.ID, &w.Title, &w.Description, &w.URL, &w.Content, &w.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, *w)
	}
	return result, nil
}

func (d *Database) CreateWorkSample(w *WorkSample) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO work_samples (title, description, url, content) VALUES (?,?,?,?)`,
		w.Title, w.Description, w.URL, w.Content)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateWorkSample(w *WorkSample) error {
	_, err := d.db.Exec(`UPDATE work_samples SET title=?, description=?, url=?, content=? WHERE id=?`,
		w.Title, w.Description, w.URL, w.Content, w.ID)
	return err
}

func (d *Database) DeleteWorkSample(id int64) error {
	_, err := d.db.Exec(`DELETE FROM work_samples WHERE id=?`, id)
	return err
}

// CSS Template operations

func (d *Database) GetCsTemplates() ([]CsTemplate, error) {
	rows, err := d.db.Query(`SELECT id, name, html_template, is_active, created_at FROM css_templates ORDER BY is_active DESC, name ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []CsTemplate{}
	for rows.Next() {
		t := &CsTemplate{}
		var active int
		if err := rows.Scan(&t.ID, &t.Name, &t.HTMLTemplate, &active, &t.CreatedAt); err != nil {
			return nil, err
		}
		t.IsActive = active == 1
		result = append(result, *t)
	}
	return result, nil
}

func (d *Database) CreateCsTemplate(t *CsTemplate) (int64, error) {
	active := 0
	if t.IsActive {
		active = 1
	}
	result, err := d.db.Exec(`INSERT INTO css_templates (name, html_template, is_active) VALUES (?,?,?)`,
		t.Name, t.HTMLTemplate, active)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateCsTemplate(t *CsTemplate) error {
	active := 0
	if t.IsActive {
		active = 1
	}
	_, err := d.db.Exec(`UPDATE css_templates SET name=?, html_template=?, is_active=? WHERE id=?`,
		t.Name, t.HTMLTemplate, active, t.ID)
	return err
}

func (d *Database) DeleteCsTemplate(id int64) error {
	_, err := d.db.Exec(`DELETE FROM css_templates WHERE id=?`, id)
	return err
}

func (d *Database) SetActiveCsTemplate(id int64) error {
	_, err := d.db.Exec(`UPDATE css_templates SET is_active = (id = ?)`, id)
	return err
}

func (d *Database) GetActiveCsTemplate() (*CsTemplate, error) {
	t := &CsTemplate{}
	var active int
	err := d.db.QueryRow(`SELECT id, name, html_template, is_active, created_at FROM css_templates WHERE is_active = 1 LIMIT 1`).Scan(
		&t.ID, &t.Name, &t.HTMLTemplate, &active, &t.CreatedAt)
	if err != nil {
		return nil, err
	}
	t.IsActive = true
	return t, nil
}

// Application operations

func (d *Database) GetApplications() ([]Application, error) {
	rows, err := d.db.Query(`SELECT id, company, position, job_url, job_posting, status, notes, created_at, updated_at FROM applications ORDER BY updated_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []Application{}
	for rows.Next() {
		a := &Application{}
		if err := rows.Scan(&a.ID, &a.Company, &a.Position, &a.JobURL, &a.JobPosting,
			&a.Status, &a.Notes, &a.CreatedAt, &a.UpdatedAt); err != nil {
			return nil, err
		}
		result = append(result, *a)
	}
	return result, nil
}

func (d *Database) GetApplication(id int64) (*Application, error) {
	a := &Application{}
	err := d.db.QueryRow(`SELECT id, company, position, job_url, job_posting, status, notes, created_at, updated_at FROM applications WHERE id=?`, id).Scan(
		&a.ID, &a.Company, &a.Position, &a.JobURL, &a.JobPosting,
		&a.Status, &a.Notes, &a.CreatedAt, &a.UpdatedAt)
	if err != nil {
		return nil, err
	}
	return a, nil
}

func (d *Database) CreateApplication(a *Application) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO applications (company, position, job_url, job_posting, status) VALUES (?,?,?,?,?)`,
		a.Company, a.Position, a.JobURL, a.JobPosting, "new")
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateApplication(a *Application) error {
	_, err := d.db.Exec(`UPDATE applications SET company=?, position=?, job_url=?, job_posting=?, status=?, notes=?, updated_at=datetime('now') WHERE id=?`,
		a.Company, a.Position, a.JobURL, a.JobPosting, a.Status, a.Notes, a.ID)
	return err
}

func (d *Database) DeleteApplication(id int64) error {
	_, err := d.db.Exec(`DELETE FROM applications WHERE id=?`, id)
	return err
}

// Q&A operations

func (d *Database) GetQuestionAnswers(applicationID int64) ([]QuestionAnswer, error) {
	rows, err := d.db.Query(`SELECT id, application_id, question, answer, created_at FROM questions_answers WHERE application_id=? ORDER BY created_at DESC`, applicationID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := []QuestionAnswer{}
	for rows.Next() {
		qa := &QuestionAnswer{}
		if err := rows.Scan(&qa.ID, &qa.ApplicationID, &qa.Question, &qa.Answer, &qa.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, *qa)
	}
	return result, nil
}

func (d *Database) CreateQuestionAnswer(qa *QuestionAnswer) (int64, error) {
	result, err := d.db.Exec(`INSERT INTO questions_answers (application_id, question, answer) VALUES (?,?,?)`,
		qa.ApplicationID, qa.Question, qa.Answer)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func (d *Database) UpdateQuestionAnswer(qa *QuestionAnswer) error {
	_, err := d.db.Exec(`UPDATE questions_answers SET question=?, answer=? WHERE id=?`,
		qa.Question, qa.Answer, qa.ID)
	return err
}

func (d *Database) DeleteQuestionAnswer(id int64) error {
	_, err := d.db.Exec(`DELETE FROM questions_answers WHERE id=?`, id)
	return err
}

// Resume data aggregation

func (d *Database) GetResumeData() (*ResumeData, error) {
	profile, err := d.GetOrCreateProfile()
	if err != nil {
		return nil, fmt.Errorf("failed to get profile: %w", err)
	}

	experiences, err := d.GetExperiences()
	if err != nil {
		return nil, fmt.Errorf("failed to get experiences: %w", err)
	}

	educations, err := d.GetEducations()
	if err != nil {
		return nil, fmt.Errorf("failed to get educations: %w", err)
	}

	skills, err := d.GetSkills()
	if err != nil {
		return nil, fmt.Errorf("failed to get skills: %w", err)
	}

	workSamples, err := d.GetWorkSamples()
	if err != nil {
		return nil, fmt.Errorf("failed to get work samples: %w", err)
	}

	return &ResumeData{
		Profile:     *profile,
		Experiences: experiences,
		Educations:  educations,
		Skills:      skills,
		WorkSamples: workSamples,
	}, nil
}

// Saved resume operations

func (d *Database) GetSavedResume(applicationID int64) (*SavedResume, error) {
	sr := &SavedResume{}
	err := d.db.QueryRow(`SELECT id, application_id, yaml_content, created_at, updated_at FROM saved_resumes WHERE application_id=?`, applicationID).Scan(
		&sr.ID, &sr.ApplicationID, &sr.YAMLContent, &sr.CreatedAt, &sr.UpdatedAt)
	if err != nil {
		return nil, err
	}
	return sr, nil
}

func (d *Database) SaveResume(sr *SavedResume) error {
	result, err := d.db.Exec(`INSERT INTO saved_resumes (application_id, yaml_content) VALUES (?,?)`,
		sr.ApplicationID, sr.YAMLContent)
	if err != nil {
		// Already exists, update instead
		_, err = d.db.Exec(`UPDATE saved_resumes SET yaml_content=?, updated_at=datetime('now') WHERE application_id=?`,
			sr.YAMLContent, sr.ApplicationID)
		return err
	}
	sr.ID, _ = result.LastInsertId()
	return nil
}

func (d *Database) UpdateSavedResume(sr *SavedResume) error {
	_, err := d.db.Exec(`UPDATE saved_resumes SET yaml_content=?, updated_at=datetime('now') WHERE id=?`,
		sr.YAMLContent, sr.ID)
	return err
}

func (d *Database) DeleteSavedResume(applicationID int64) error {
	_, err := d.db.Exec(`DELETE FROM saved_resumes WHERE application_id=?`, applicationID)
	return err
}