package store

import (
	"context"
	"database/sql"
	"time"
)

func (db *DB) CountActiveLeads(ctx context.Context, userID int64) (int, error) {
	var n int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM user_leads
		WHERE user_id = ? AND stage NOT IN ('fechado','perdido','arquivado')`, userID).Scan(&n)
	return n, err
}

func (db *DB) CountFinishedLeads(ctx context.Context, userID int64) (int, error) {
	var n int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM user_leads
		WHERE user_id = ? AND stage IN ('fechado','perdido','arquivado')`, userID).Scan(&n)
	return n, err
}

func (db *DB) HasLeadCNPJ(ctx context.Context, userID int64, cnpj string) (bool, error) {
	var n int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM user_leads WHERE user_id = ? AND cnpj = ?`, userID, cnpj).Scan(&n)
	return n > 0, err
}

func (db *DB) WorkingCNPJs(ctx context.Context, userID int64) (map[string]bool, error) {
	rows, err := db.QueryContext(ctx, `SELECT cnpj FROM user_leads WHERE user_id = ?`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]bool)
	for rows.Next() {
		var cnpj string
		if err := rows.Scan(&cnpj); err != nil {
			return nil, err
		}
		out[cnpj] = true
	}
	return out, rows.Err()
}

func (db *DB) CreateUserLead(ctx context.Context, lead *UserLead) (int64, error) {
	now := time.Now()
	res, err := db.ExecContext(ctx, `
		INSERT INTO user_leads (user_id, cnpj, stage, company_name, city, uf,
			has_phone, has_email, worked_at, stage_changed_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		lead.UserID, lead.CNPJ, lead.Stage, lead.CompanyName, lead.City, lead.UF,
		lead.HasPhone, lead.HasEmail, now, now)
	if err != nil {
		return 0, err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	_, _ = db.ExecContext(ctx, `
		INSERT INTO lead_stage_history (user_lead_id, from_stage, to_stage)
		VALUES (?, NULL, ?)`, id, lead.Stage)
	return id, nil
}

func (db *DB) GetUserLead(ctx context.Context, userID, leadID int64) (*UserLead, error) {
	row := db.QueryRowContext(ctx, `
		SELECT id, user_id, cnpj, stage, company_name, city, uf,
		       has_phone, has_email, worked_at, stage_changed_at, notes
		FROM user_leads WHERE id = ? AND user_id = ?`, leadID, userID)
	return scanUserLead(row)
}

func (db *DB) ListUserLeads(ctx context.Context, userID int64, stage string) ([]UserLead, error) {
	q := `
		SELECT id, user_id, cnpj, stage, company_name, city, uf,
		       has_phone, has_email, worked_at, stage_changed_at, notes
		FROM user_leads WHERE user_id = ?`
	args := []any{userID}
	if stage != "" {
		q += ` AND stage = ?`
		args = append(args, stage)
	}
	q += ` ORDER BY stage_changed_at DESC`
	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []UserLead
	for rows.Next() {
		l, err := scanUserLeadRows(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *l)
	}
	return out, rows.Err()
}

func (db *DB) UpdateLeadStage(ctx context.Context, userID, leadID int64, newStage string) error {
	lead, err := db.GetUserLead(ctx, userID, leadID)
	if err != nil {
		return err
	}
	now := time.Now()
	_, err = db.ExecContext(ctx, `
		UPDATE user_leads SET stage = ?, stage_changed_at = ? WHERE id = ? AND user_id = ?`,
		newStage, now, leadID, userID)
	if err != nil {
		return err
	}
	_, err = db.ExecContext(ctx, `
		INSERT INTO lead_stage_history (user_lead_id, from_stage, to_stage)
		VALUES (?, ?, ?)`, leadID, lead.Stage, newStage)
	return err
}

func (db *DB) UpdateLeadNotes(ctx context.Context, userID, leadID int64, notes string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE user_leads SET notes = ? WHERE id = ? AND user_id = ?`, notes, leadID, userID)
	return err
}

func (db *DB) DeleteUserLead(ctx context.Context, userID, leadID int64) error {
	_, err := db.ExecContext(ctx, `
		DELETE FROM user_leads WHERE id = ? AND user_id = ? AND stage = 'arquivado'`, leadID, userID)
	return err
}

func (db *DB) CountLeadsByStage(ctx context.Context, userID int64) (map[string]int, error) {
	rows, err := db.QueryContext(ctx, `
		SELECT stage, COUNT(*) FROM user_leads WHERE user_id = ? GROUP BY stage`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]int)
	for _, s := range AllStages {
		out[s] = 0
	}
	for rows.Next() {
		var stage string
		var n int
		if err := rows.Scan(&stage, &n); err != nil {
			return nil, err
		}
		out[stage] = n
	}
	return out, rows.Err()
}

func (db *DB) CountContactsToday(ctx context.Context, userID int64) (int, error) {
	var n int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM contact_events ce
		JOIN user_leads ul ON ul.id = ce.user_lead_id
		WHERE ul.user_id = ? AND DATE(ce.created_at) = CURDATE()`, userID).Scan(&n)
	return n, err
}

func (db *DB) RecordContactEvent(ctx context.Context, leadID int64, channel string) error {
	_, err := db.ExecContext(ctx, `
		INSERT INTO contact_events (user_lead_id, channel) VALUES (?, ?)`, leadID, channel)
	return err
}

func (db *DB) HasContactEvent(ctx context.Context, leadID int64) (bool, error) {
	var n int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM contact_events WHERE user_lead_id = ?`, leadID).Scan(&n)
	return n > 0, err
}

func scanUserLead(row *sql.Row) (*UserLead, error) {
	var l UserLead
	var notes sql.NullString
	err := row.Scan(&l.ID, &l.UserID, &l.CNPJ, &l.Stage, &l.CompanyName, &l.City, &l.UF,
		&l.HasPhone, &l.HasEmail, &l.WorkedAt, &l.StageChangedAt, &notes)
	if err != nil {
		return nil, err
	}
	if notes.Valid {
		l.Notes = &notes.String
	}
	return &l, nil
}

func scanUserLeadRows(rows *sql.Rows) (*UserLead, error) {
	var l UserLead
	var notes sql.NullString
	err := rows.Scan(&l.ID, &l.UserID, &l.CNPJ, &l.Stage, &l.CompanyName, &l.City, &l.UF,
		&l.HasPhone, &l.HasEmail, &l.WorkedAt, &l.StageChangedAt, &notes)
	if err != nil {
		return nil, err
	}
	if notes.Valid {
		l.Notes = &notes.String
	}
	return &l, nil
}