package db

import (
	"database/sql"
	"time"
)

type Client struct {
	ClientID           int64  `json:"client_id"`
	AccountID          int64  `json:"account_id"`
	Name               string `json:"name"`
	Phone              string `json:"phone,omitempty"`
	Email              string `json:"email,omitempty"`
	Address            string `json:"address,omitempty"`
	Notes              string `json:"notes,omitempty"`
	WhatsAppNumber     string `json:"whatsapp_number,omitempty"`
	WhatsAppConnected int    `json:"whatsapp_connected"`
	WhatsAppJID       string `json:"whatsapp_jid,omitempty"`
	CreatedAt          int64  `json:"created_at"`
}

type Customer struct {
	CustomerID          int64  `json:"customer_id"`
	ClientID            int64  `json:"client_id"`
	Name                string `json:"name"`
	Phone               string `json:"phone,omitempty"`
	BirthDate           string `json:"birth_date,omitempty"`
	Instagram           string `json:"instagram,omitempty"`
	CreatedAt           int64  `json:"created_at"`
	WhatsAppConnected  int    `json:"whatsapp_connected"`
	WhatsAppNumber      string `json:"whatsapp_number,omitempty"`
}

type Service struct {
	ServiceID  int64   `json:"service_id"`
	ClientID   int64   `json:"client_id"`
	Name       string  `json:"name"`
	Price      float64 `json:"price,omitempty"`
	Description string `json:"description,omitempty"`
	Duration   string `json:"duration,omitempty"`
	CreatedAt  int64   `json:"created_at"`
}

type Schedule struct {
	ScheduleID  int64  `json:"schedule_id"`
	ClientID    int64  `json:"client_id"`
	CustomerID int64  `json:"customer_id"`
	ServiceID  int64  `json:"service_id"`
	PlanDate    string `json:"plan_date,omitempty"`
	Time       string `json:"time,omitempty"`
	Status     string `json:"status"`
	CreatedAt  int64  `json:"created_at"`
}

type Payment struct {
	PaymentID     int64   `json:"payment_id"`
	ClientID      int64   `json:"client_id"`
	CustomerID    int64   `json:"customer_id"`
	ScheduleID    int64   `json:"schedule_id,omitempty"`
	HasPaid       bool    `json:"has_paid"`
	Amount        float64 `json:"amount,omitempty"`
	PaymentDate   string  `json:"payment_date,omitempty"`
	PaymentMethod string  `json:"payment_method,omitempty"`
	CreatedAt     int64   `json:"created_at"`
}

func (c *Client) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO clients (account_id, name, phone, email, address, notes, whatsapp_number, whatsapp_connected, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
		c.AccountID, c.Name, c.Phone, c.Email, c.Address, c.Notes, c.WhatsAppNumber, c.WhatsAppConnected, c.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	c.ClientID = id
	return nil
}

func (c *Client) Read(db *sql.DB, id int64) error {
	return db.QueryRow(
		"SELECT client_id, account_id, name, COALESCE(phone,''), COALESCE(email,''), COALESCE(address,''), COALESCE(notes,''), COALESCE(whatsapp_number,''), COALESCE(whatsapp_connected,0), COALESCE(whatsapp_jid,''), created_at FROM clients WHERE client_id = ?",
		id,
	).Scan(&c.ClientID, &c.AccountID, &c.Name, &c.Phone, &c.Email, &c.Address, &c.Notes, &c.WhatsAppNumber, &c.WhatsAppConnected, &c.WhatsAppJID, &c.CreatedAt)
}

func (c *Client) Update(db *sql.DB) error {
	_, err := db.Exec(
		"UPDATE clients SET name = ?, phone = ?, email = ?, address = ?, notes = ?, whatsapp_number = ?, whatsapp_connected = ?, whatsapp_jid = ? WHERE client_id = ? AND account_id = ?",
		c.Name, c.Phone, c.Email, c.Address, c.Notes, c.WhatsAppNumber, c.WhatsAppConnected, c.WhatsAppJID, c.ClientID, c.AccountID,
	)
	return err
}

func (c *Client) Delete(db *sql.DB) error {
	_, err := db.Exec("DELETE FROM clients WHERE client_id = ? AND account_id = ?", c.ClientID, c.AccountID)
	return err
}

func ListClients(db *sql.DB, accountID int64, limit, offset int) ([]Client, error) {
	query := "SELECT client_id, account_id, name, COALESCE(phone,''), COALESCE(email,''), COALESCE(address,''), COALESCE(notes,''), COALESCE(whatsapp_number,''), COALESCE(whatsapp_connected,0), COALESCE(whatsapp_jid,''), created_at FROM clients"
	args := []interface{}{}
	if accountID > 0 {
		query += " WHERE account_id = ?"
		args = append(args, accountID)
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var clients []Client
	for rows.Next() {
		var c Client
		if err := rows.Scan(&c.ClientID, &c.AccountID, &c.Name, &c.Phone, &c.Email, &c.Address, &c.Notes, &c.WhatsAppNumber, &c.WhatsAppConnected, &c.WhatsAppJID, &c.CreatedAt); err != nil {
			return nil, err
		}
		clients = append(clients, c)
	}
	return clients, rows.Err()
}

func GetClientByID(db *sql.DB, accountID, id int64) (*Client, error) {
	var c Client
	err := db.QueryRow(
		"SELECT client_id, account_id, name, COALESCE(phone,''), COALESCE(email,''), COALESCE(address,''), COALESCE(notes,''), COALESCE(whatsapp_number,''), COALESCE(whatsapp_connected,0), COALESCE(whatsapp_jid,''), created_at FROM clients WHERE client_id = ? AND account_id = ?",
		id, accountID,
	).Scan(&c.ClientID, &c.AccountID, &c.Name, &c.Phone, &c.Email, &c.Address, &c.Notes, &c.WhatsAppNumber, &c.WhatsAppConnected, &c.WhatsAppJID, &c.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &c, nil
}

func (cu *Customer) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO customers (client_id, name, phone, birth_date, instagram, created_at) VALUES (?, ?, ?, ?, ?, ?)",
		cu.ClientID, cu.Name, cu.Phone, cu.BirthDate, cu.Instagram, cu.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	cu.CustomerID = id
	return nil
}

func ListCustomers(db *sql.DB, accountID, clientID int64, limit, offset int) ([]Customer, error) {
	args := []interface{}{}
	query := "SELECT customer_id, client_id, name, phone, birth_date, instagram, created_at FROM customers"
	cond := ""
	if clientID > 0 {
		cond = "client_id = ?"
		args = append(args, clientID)
	}
	if accountID > 0 {
		if cond != "" {
			cond += " AND "
		}
		cond += "client_id IN (SELECT client_id FROM clients WHERE account_id = ?)"
		args = append(args, accountID)
	}
	if cond != "" {
		query += " WHERE " + cond
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var customers []Customer
	for rows.Next() {
		var cu Customer
		if err := rows.Scan(&cu.CustomerID, &cu.ClientID, &cu.Name, &cu.Phone, &cu.BirthDate, &cu.Instagram, &cu.CreatedAt); err != nil {
			return nil, err
		}
		customers = append(customers, cu)
	}
	return customers, rows.Err()
}

func (s *Service) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO services (client_id, name, price, description, duration, created_at) VALUES (?, ?, ?, ?, ?, ?)",
		s.ClientID, s.Name, s.Price, s.Description, s.Duration, s.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	s.ServiceID = id
	return nil
}

func ListServices(db *sql.DB, accountID, clientID int64, limit, offset int) ([]Service, error) {
	args := []interface{}{}
	query := "SELECT service_id, client_id, name, price, description, duration, created_at FROM services"
	cond := ""
	if clientID > 0 {
		cond = "client_id = ?"
		args = append(args, clientID)
	}
	if accountID > 0 {
		if cond != "" {
			cond += " AND "
		}
		cond += "client_id IN (SELECT client_id FROM clients WHERE account_id = ?)"
		args = append(args, accountID)
	}
	if cond != "" {
		query += " WHERE " + cond
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var services []Service
	for rows.Next() {
		var s Service
		if err := rows.Scan(&s.ServiceID, &s.ClientID, &s.Name, &s.Price, &s.Description, &s.Duration, &s.CreatedAt); err != nil {
			return nil, err
		}
		services = append(services, s)
	}
	return services, rows.Err()
}

func (sch *Schedule) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO scheduling (client_id, customer_id, service_id, plan_date, time, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
		sch.ClientID, sch.CustomerID, sch.ServiceID, sch.PlanDate, sch.Time, sch.Status, sch.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	sch.ScheduleID = id
	return nil
}

func ListSchedules(db *sql.DB, clientID, customerID int64, limit, offset int) ([]Schedule, error) {
	query := "SELECT schedule_id, client_id, customer_id, service_id, plan_date, time, status, created_at FROM scheduling WHERE 1=1"
	var args []interface{}
	if clientID > 0 {
		query += " AND client_id = ?"
		args = append(args, clientID)
	}
	if customerID > 0 {
		query += " AND customer_id = ?"
		args = append(args, customerID)
	}
	query += " ORDER BY plan_date DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var schedules []Schedule
	for rows.Next() {
		var sch Schedule
		if err := rows.Scan(&sch.ScheduleID, &sch.ClientID, &sch.CustomerID, &sch.ServiceID, &sch.PlanDate, &sch.Time, &sch.Status, &sch.CreatedAt); err != nil {
			return nil, err
		}
		schedules = append(schedules, sch)
	}
	return schedules, rows.Err()
}

func (p *Payment) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO payments (client_id, customer_id, schedule_id, has_paid, amount, payment_date, payment_method, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
		p.ClientID, p.CustomerID, p.ScheduleID, boolToInt(p.HasPaid), p.Amount, p.PaymentDate, p.PaymentMethod, p.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	p.PaymentID = id
	return nil
}

func ListPayments(db *sql.DB, clientID int64, limit, offset int) ([]Payment, error) {
	query := "SELECT payment_id, client_id, customer_id, schedule_id, has_paid, amount, payment_date, payment_method, created_at FROM payments"
	var args []interface{}
	if clientID > 0 {
		query += " WHERE client_id = ?"
		args = append(args, clientID)
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var payments []Payment
	for rows.Next() {
		var p Payment
		var hasPaid int
		if err := rows.Scan(&p.PaymentID, &p.ClientID, &p.CustomerID, &p.ScheduleID, &hasPaid, &p.Amount, &p.PaymentDate, &p.PaymentMethod, &p.CreatedAt); err != nil {
			return nil, err
		}
		p.HasPaid = hasPaid == 1
		payments = append(payments, p)
	}
	return payments, rows.Err()
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

// ---------------------------------------------------------------------------
// Lead
// ---------------------------------------------------------------------------

type Lead struct {
	LeadID          int64   `json:"lead_id"`
	ClientID        int64   `json:"client_id"`
	Name            string  `json:"name"`
	PhoneRaw        string  `json:"phone_raw"`
	PhoneNormalized string  `json:"phone_normalized"`
	ServiceInterest string  `json:"service_interest"`
	Status          string  `json:"status"`
	NeedsReview     bool    `json:"needs_review"`
	AppointmentDate string  `json:"appointment_date,omitempty"`
	AppointmentTime string  `json:"appointment_time,omitempty"`
	PaymentStatus   string  `json:"payment_status"`
	PaymentAmount   float64 `json:"payment_amount,omitempty"`
	PaymentDate     string  `json:"payment_date,omitempty"`
	CreatedAt       int64   `json:"created_at"`
	LastContactAt   int64   `json:"last_contact_at"`
}

func CreateLead(db *sql.DB, l *Lead) error {
	now := time.Now().Unix()
	l.CreatedAt = now
	l.LastContactAt = now
	result, err := db.Exec(
		`INSERT INTO leads
			(client_id, name, phone_raw, phone_normalized, service_interest, status, needs_review,
			 appointment_date, appointment_time, payment_status, payment_amount, payment_date, created_at, last_contact_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		l.ClientID, l.Name, l.PhoneRaw, l.PhoneNormalized, l.ServiceInterest, l.Status, boolToInt(l.NeedsReview),
		l.AppointmentDate, l.AppointmentTime, l.PaymentStatus, l.PaymentAmount, l.PaymentDate,
		l.CreatedAt, l.LastContactAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	l.LeadID = id
	return nil
}

func GetLeadByPhone(db *sql.DB, clientID int64, phoneNormalized string) (*Lead, error) {
	var l Lead
	var needsReview int
	err := db.QueryRow(
		`SELECT lead_id, client_id, name, phone_raw, phone_normalized, service_interest, status, needs_review,
		        COALESCE(appointment_date,''), COALESCE(appointment_time,''), payment_status,
		        COALESCE(payment_amount,0), COALESCE(payment_date,''), created_at, last_contact_at
		 FROM leads WHERE client_id = ? AND phone_normalized = ?`,
		clientID, phoneNormalized,
	).Scan(
		&l.LeadID, &l.ClientID, &l.Name, &l.PhoneRaw, &l.PhoneNormalized,
		&l.ServiceInterest, &l.Status, &needsReview,
		&l.AppointmentDate, &l.AppointmentTime, &l.PaymentStatus,
		&l.PaymentAmount, &l.PaymentDate, &l.CreatedAt, &l.LastContactAt,
	)
	if err != nil {
		return nil, err
	}
	l.NeedsReview = needsReview == 1
	return &l, nil
}

func GetLeadByID(db *sql.DB, clientID, leadID int64) (*Lead, error) {
	var l Lead
	var needsReview int
	err := db.QueryRow(
		`SELECT lead_id, client_id, name, phone_raw, phone_normalized, service_interest, status, needs_review,
		        COALESCE(appointment_date,''), COALESCE(appointment_time,''), payment_status,
		        COALESCE(payment_amount,0), COALESCE(payment_date,''), created_at, last_contact_at
		 FROM leads WHERE lead_id = ? AND client_id = ?`,
		leadID, clientID,
	).Scan(
		&l.LeadID, &l.ClientID, &l.Name, &l.PhoneRaw, &l.PhoneNormalized,
		&l.ServiceInterest, &l.Status, &needsReview,
		&l.AppointmentDate, &l.AppointmentTime, &l.PaymentStatus,
		&l.PaymentAmount, &l.PaymentDate, &l.CreatedAt, &l.LastContactAt,
	)
	if err != nil {
		return nil, err
	}
	l.NeedsReview = needsReview == 1
	return &l, nil
}

func TouchLeadContact(db *sql.DB, leadID int64) error {
	_, err := db.Exec(
		"UPDATE leads SET last_contact_at = ? WHERE lead_id = ?",
		time.Now().Unix(), leadID,
	)
	return err
}

func UpdateLead(db *sql.DB, l *Lead) error {
	_, err := db.Exec(
		`UPDATE leads SET name=?, service_interest=?, status=?, needs_review=?,
		 appointment_date=?, appointment_time=?, payment_status=?, payment_amount=?, payment_date=?
		 WHERE lead_id=? AND client_id=?`,
		l.Name, l.ServiceInterest, l.Status, boolToInt(l.NeedsReview),
		l.AppointmentDate, l.AppointmentTime, l.PaymentStatus, l.PaymentAmount, l.PaymentDate,
		l.LeadID, l.ClientID,
	)
	return err
}

func ListLeadsForReview(db *sql.DB, clientID int64, limit, offset int) ([]Lead, error) {
	rows, err := db.Query(
		`SELECT lead_id, client_id, name, phone_raw, phone_normalized, service_interest, status, needs_review,
		        COALESCE(appointment_date,''), COALESCE(appointment_time,''), payment_status,
		        COALESCE(payment_amount,0), COALESCE(payment_date,''), created_at, last_contact_at
		 FROM leads WHERE client_id = ? AND needs_review = 1
		 ORDER BY created_at DESC LIMIT ? OFFSET ?`,
		clientID, limit, offset,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanLeads(rows)
}

func ListAllLeads(db *sql.DB, clientID int64, limit, offset int) ([]Lead, error) {
	rows, err := db.Query(
		`SELECT lead_id, client_id, name, phone_raw, phone_normalized, service_interest, status, needs_review,
		        COALESCE(appointment_date,''), COALESCE(appointment_time,''), payment_status,
		        COALESCE(payment_amount,0), COALESCE(payment_date,''), created_at, last_contact_at
		 FROM leads WHERE client_id = ?
		 ORDER BY created_at DESC LIMIT ? OFFSET ?`,
		clientID, limit, offset,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanLeads(rows)
}

func CountLeadsNeedingReview(db *sql.DB, clientID int64) (int, error) {
	var n int
	err := db.QueryRow(
		"SELECT COUNT(*) FROM leads WHERE client_id = ? AND needs_review = 1",
		clientID,
	).Scan(&n)
	return n, err
}

func scanLeads(rows *sql.Rows) ([]Lead, error) {
	var leads []Lead
	for rows.Next() {
		var l Lead
		var needsReview int
		if err := rows.Scan(
			&l.LeadID, &l.ClientID, &l.Name, &l.PhoneRaw, &l.PhoneNormalized,
			&l.ServiceInterest, &l.Status, &needsReview,
			&l.AppointmentDate, &l.AppointmentTime, &l.PaymentStatus,
			&l.PaymentAmount, &l.PaymentDate, &l.CreatedAt, &l.LastContactAt,
		); err != nil {
			return nil, err
		}
		l.NeedsReview = needsReview == 1
		leads = append(leads, l)
	}
	return leads, rows.Err()
}

// ---------------------------------------------------------------------------
// ServiceKeyword
// ---------------------------------------------------------------------------

type ServiceKeyword struct {
	KeywordID   int64  `json:"keyword_id"`
	ClientID    int64  `json:"client_id"`
	ServiceName string `json:"service_name"`
	Keyword     string `json:"keyword"`
	CreatedAt   int64  `json:"created_at"`
}

func ListServiceKeywords(db *sql.DB, clientID int64) ([]ServiceKeyword, error) {
	rows, err := db.Query(
		"SELECT keyword_id, client_id, service_name, keyword, created_at FROM service_keywords WHERE client_id = ? ORDER BY service_name, keyword",
		clientID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var kws []ServiceKeyword
	for rows.Next() {
		var kw ServiceKeyword
		if err := rows.Scan(&kw.KeywordID, &kw.ClientID, &kw.ServiceName, &kw.Keyword, &kw.CreatedAt); err != nil {
			return nil, err
		}
		kws = append(kws, kw)
	}
	return kws, rows.Err()
}

func LoadKeywordMapping(db *sql.DB, clientID int64) (map[string]string, error) {
	kws, err := ListServiceKeywords(db, clientID)
	if err != nil {
		return nil, err
	}
	m := make(map[string]string, len(kws))
	for _, kw := range kws {
		m[kw.Keyword] = kw.ServiceName
	}
	return m, nil
}

func AddServiceKeyword(db *sql.DB, clientID int64, serviceName, keyword string) error {
	_, err := db.Exec(
		"INSERT INTO service_keywords (client_id, service_name, keyword, created_at) VALUES (?, ?, ?, ?)",
		clientID, serviceName, keyword, time.Now().Unix(),
	)
	return err
}

func DeleteServiceKeyword(db *sql.DB, clientID, keywordID int64) error {
	_, err := db.Exec(
		"DELETE FROM service_keywords WHERE keyword_id = ? AND client_id = ?",
		keywordID, clientID,
	)
	return err
}

// ---------------------------------------------------------------------------
// LeadStatus
// ---------------------------------------------------------------------------

type LeadStatus struct {
	StatusID   int64  `json:"status_id"`
	ClientID   int64  `json:"client_id"`
	StatusName string `json:"status_name"`
	CreatedAt  int64  `json:"created_at"`
}

func ListLeadStatuses(db *sql.DB, clientID int64) ([]LeadStatus, error) {
	rows, err := db.Query(
		"SELECT status_id, client_id, status_name, created_at FROM lead_statuses WHERE client_id = ? ORDER BY created_at",
		clientID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var statuses []LeadStatus
	for rows.Next() {
		var s LeadStatus
		if err := rows.Scan(&s.StatusID, &s.ClientID, &s.StatusName, &s.CreatedAt); err != nil {
			return nil, err
		}
		statuses = append(statuses, s)
	}
	return statuses, rows.Err()
}

func AddLeadStatus(db *sql.DB, clientID int64, statusName string) error {
	_, err := db.Exec(
		"INSERT INTO lead_statuses (client_id, status_name, created_at) VALUES (?, ?, ?)",
		clientID, statusName, time.Now().Unix(),
	)
	return err
}

func DeleteLeadStatus(db *sql.DB, clientID, statusID int64) error {
	_, err := db.Exec(
		"DELETE FROM lead_statuses WHERE status_id = ? AND client_id = ?",
		statusID, clientID,
	)
	return err
}

type Question struct {
	QuestionID  int64  `json:"question_id"`
	ClientID    int64  `json:"client_id"`
	CustomerID  int64  `json:"customer_id"`
	Question    string `json:"question"`
	Timestamp   int64  `json:"timestamp"`
	Status      string `json:"status"`
	CreatedAt   int64  `json:"created_at"`
}

func (q *Question) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO questions (client_id, customer_id, question, timestamp, status, created_at) VALUES (?, ?, ?, ?, ?, ?)",
		q.ClientID, q.CustomerID, q.Question, q.Timestamp, q.Status, q.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	q.QuestionID = id
	return nil
}

func ListQuestions(db *sql.DB, clientID int64, limit, offset int) ([]Question, error) {
	query := "SELECT question_id, client_id, customer_id, question, timestamp, status, created_at FROM questions WHERE 1=1"
	var args []interface{}
	if clientID > 0 {
		query += " AND client_id = ?"
		args = append(args, clientID)
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var questions []Question
	for rows.Next() {
		var q Question
		if err := rows.Scan(&q.QuestionID, &q.ClientID, &q.CustomerID, &q.Question, &q.Timestamp, &q.Status, &q.CreatedAt); err != nil {
			return nil, err
		}
		questions = append(questions, q)
	}
	return questions, rows.Err()
}

type Answer struct {
	AnswerID    int64  `json:"answer_id"`
	ClientID    int64  `json:"client_id"`
	QuestionID int64  `json:"question_id"`
	Answer      string `json:"answer"`
	Timestamp  int64  `json:"timestamp"`
	Status      string `json:"status"`
	CreatedAt   int64  `json:"created_at"`
}

func (a *Answer) Create(db *sql.DB) error {
	result, err := db.Exec(
		"INSERT INTO answers (client_id, question_id, answer, timestamp, status, created_at) VALUES (?, ?, ?, ?, ?, ?)",
		a.ClientID, a.QuestionID, a.Answer, a.Timestamp, a.Status, a.CreatedAt,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	a.AnswerID = id
	return nil
}

func ListAnswers(db *sql.DB, questionID int64, limit, offset int) ([]Answer, error) {
	query := "SELECT answer_id, client_id, question_id, answer, timestamp, status, created_at FROM answers WHERE 1=1"
	var args []interface{}
	if questionID > 0 {
		query += " AND question_id = ?"
		args = append(args, questionID)
	}
	query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var answers []Answer
	for rows.Next() {
		var a Answer
		if err := rows.Scan(&a.AnswerID, &a.ClientID, &a.QuestionID, &a.Answer, &a.Timestamp, &a.Status, &a.CreatedAt); err != nil {
			return nil, err
		}
		answers = append(answers, a)
	}
	return answers, rows.Err()
}

type AnswerWithDetails struct {
	AnswerID    int64  `json:"answer_id"`
	ClientID   int64  `json:"client_id"`
	QuestionID int64  `json:"question_id"`
	Answer    string `json:"answer"`
	Timestamp int64  `json:"timestamp"`
	Status    string `json:"status"`
	CreatedAt int64  `json:"created_at"`
	ClientName   string `json:"client_name"`
	CustomerName string `json:"customer_name"`
	QuestionText string `json:"question_text"`
}

func ListAnswersWithDetails(db *sql.DB, accountID, questionID int64, limit, offset int) ([]AnswerWithDetails, error) {
	args := []interface{}{accountID}
	query := `
		SELECT
			a.answer_id, a.client_id, a.question_id, a.answer, a.timestamp, a.status, a.created_at,
			c.name as client_name,
			cu.name as customer_name,
			q.question as question_text
		FROM answers a
		JOIN questions q ON a.question_id = q.question_id
		JOIN customers cu ON q.customer_id = cu.customer_id
		JOIN clients c ON q.client_id = c.client_id
		WHERE c.account_id = ?`
	if questionID > 0 {
		query += " AND a.question_id = ?"
		args = append(args, questionID)
	}
	query += " ORDER BY a.created_at DESC LIMIT ? OFFSET ?"
	args = append(args, limit, offset)

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

	var answers []AnswerWithDetails
	for rows.Next() {
		var a AnswerWithDetails
		if err := rows.Scan(&a.AnswerID, &a.ClientID, &a.QuestionID, &a.Answer, &a.Timestamp, &a.Status, &a.CreatedAt, &a.ClientName, &a.CustomerName, &a.QuestionText); err != nil {
			return nil, err
		}
		answers = append(answers, a)
	}
	return answers, rows.Err()
}

// ---------------------------------------------------------------------------
// Processed Messages (dedup)
// ---------------------------------------------------------------------------

func IsMessageProcessed(db *sql.DB, clientID int64, messageID string) (bool, error) {
	var count int
	err := db.QueryRow(
		`SELECT COUNT(*) FROM processed_messages WHERE client_id = ? AND message_id = ?`,
		clientID, messageID,
	).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func RecordProcessedMessage(db *sql.DB, clientID int64, messageID string) error {
	_, err := db.Exec(
		`INSERT OR IGNORE INTO processed_messages (message_id, client_id) VALUES (?, ?)`,
		messageID, clientID,
	)
	return err
}