workspace/apps/go-crm/internal/db/db.go

package db

import (
	"database/sql"
	"fmt"
	"golang.org/x/text/encoding/charmap"
	"os"
	"path/filepath"
	"strings"
	"unicode/utf8"

	_ "github.com/glebarez/sqlite"
)

const schema = `
CREATE TABLE IF NOT EXISTS accounts (
    account_id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    password TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    session_id TEXT PRIMARY KEY,
    account_id INTEGER NOT NULL,
    expires INTEGER NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

CREATE TABLE IF NOT EXISTS clients (
    client_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    phone TEXT,
    email TEXT,
    address TEXT,
    notes TEXT,
    whatsapp_number TEXT,
    whatsapp_connected INTEGER DEFAULT 0,
    created_at INTEGER NOT NULL,
    whatsapp_jid TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    phone TEXT,
    birth_date TEXT,
    instagram TEXT,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE IF NOT EXISTS services (
    service_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    price REAL,
    description TEXT,
    duration TEXT,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE IF NOT EXISTS scheduling (
    schedule_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    plan_date TEXT,
    time TEXT,
    status TEXT DEFAULT 'pending',
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (service_id) REFERENCES services(service_id)
);

CREATE TABLE IF NOT EXISTS payments (
    payment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    schedule_id INTEGER,
    has_paid INTEGER DEFAULT 0,
    amount REAL,
    payment_date TEXT,
    payment_method TEXT,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (schedule_id) REFERENCES scheduling(schedule_id)
);

CREATE TABLE IF NOT EXISTS questions (
    question_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    question TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE IF NOT EXISTS answers (
    answer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    question_id INTEGER NOT NULL,
    answer TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    status TEXT DEFAULT 'active',
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id),
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);

CREATE TABLE IF NOT EXISTS leads (
    lead_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    name TEXT NOT NULL DEFAULT '',
    phone_raw TEXT NOT NULL DEFAULT '',
    phone_normalized TEXT NOT NULL DEFAULT '',
    service_interest TEXT NOT NULL DEFAULT 'Não especificou',
    status TEXT NOT NULL DEFAULT 'Não agendou',
    needs_review INTEGER NOT NULL DEFAULT 1,
    appointment_date TEXT,
    appointment_time TEXT,
    payment_status TEXT NOT NULL DEFAULT 'Não pago',
    payment_amount REAL,
    payment_date TEXT,
    created_at INTEGER NOT NULL,
    last_contact_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE IF NOT EXISTS processed_messages (
    message_id TEXT NOT NULL,
    client_id INTEGER NOT NULL,
    PRIMARY KEY (message_id, client_id)
);

CREATE TABLE IF NOT EXISTS service_keywords (
    keyword_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    service_name TEXT NOT NULL,
    keyword TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

CREATE TABLE IF NOT EXISTS lead_statuses (
    status_id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    status_name TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

`

var dbPath = "data/go-crm.db"

func Init(path string) (*sql.DB, error) {
	if path != "" {
		dbPath = path
	}

	dir := filepath.Dir(dbPath)
	fmt.Println("db.Init: data dir", dir)
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("failed to create data directory: %w", err)
	}

	dsn := fmt.Sprintf("file:%s?mode=rwc&_pragma=encoding(UTF8)", filepath.ToSlash(dbPath))
	fmt.Println("db.Init: opening database", dbPath)
	database, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	fmt.Println("db.Init: ping database")
	if err := database.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	fmt.Println("db.Init: executing schema")
	if _, err := database.Exec(schema); err != nil {
		return nil, fmt.Errorf("failed to create schema: %w", err)
	}

	fmt.Println("db.Init: running migrate")
	if err := migrate(database); err != nil {
		return nil, fmt.Errorf("failed to migrate: %w", err)
	}

	fmt.Println("db.Init: migration complete")
	return database, nil
}

func migrate(db *sql.DB) error {
	fmt.Println("db.migrate: add account_id column")
	_, err := db.Exec("ALTER TABLE clients ADD COLUMN account_id INTEGER")
	if err != nil {
		if !strings.Contains(err.Error(), "duplicate column name") {
			return err
		}
	}

	fmt.Println("db.migrate: add whatsapp_number column")
	_, err = db.Exec("ALTER TABLE clients ADD COLUMN whatsapp_number TEXT")
	if err != nil {
		if !strings.Contains(err.Error(), "duplicate column name") {
			return err
		}
	}

	fmt.Println("db.migrate: add whatsapp_connected column")
	_, err = db.Exec("ALTER TABLE clients ADD COLUMN whatsapp_connected INTEGER DEFAULT 0")
	if err != nil {
		if !strings.Contains(err.Error(), "duplicate column name") {
			return err
		}
	}

	fmt.Println("db.migrate: add whatsapp_jid column")
	_, err = db.Exec("ALTER TABLE clients ADD COLUMN whatsapp_jid TEXT")
	if err != nil {
		if !strings.Contains(err.Error(), "duplicate column name") {
			return err
		}
	}

	fmt.Println("db.migrate: seeding default keywords")
	// Seed default service keywords per client if none exist yet.
	if err := seedDefaultKeywords(db); err != nil {
		return err
	}

	fmt.Println("db.migrate: seeding default statuses")
	// Seed default lead statuses per client if none exist yet.
	if err := seedDefaultStatuses(db); err != nil {
		return err
	}

	fmt.Println("db.migrate: sanitizing encodings")
	// Sanitize mojibake from legacy data: decode Latin1->UTF8
	if err := sanitizeEncoding(db); err != nil {
		return err
	}

	return nil
}

func seedDefaultKeywords(db *sql.DB) error {
	fmt.Println("seedDefaultKeywords: querying clients")
	// Fetch all client IDs.
	rows, err := db.Query("SELECT client_id FROM clients")
	if err != nil {
		return nil // table may not exist yet on very first run — ignore
	}
	defer rows.Close()

	defaults := map[string][]string{
		"Head Spa":           {"head spa", "head-spa", "headspa"},
		"Massagem completa":  {"massagem", "massagem completa"},
		"Drenagem linfatica": {"drenagem", "linfática", "linfahtica", "drenagem linfatica"},
		"Hydra Boost":        {"hydra", "hydra boost", "hydraboost"},
		"Design Henna":       {"henna", "design henna"},
		"Masculino":          {"masculino", "masc"},
		"Não especificou":    {"nao especificou", "não especificou"},
	}

	now := int64(0)
	for rows.Next() {
		var clientID int64
		if err := rows.Scan(&clientID); err != nil {
			continue
		}
		fmt.Println("seedDefaultKeywords: client", clientID)
		var count int
		db.QueryRow("SELECT COUNT(*) FROM service_keywords WHERE client_id = ?", clientID).Scan(&count)
		if count > 0 {
			continue
		}
		for svc, keywords := range defaults {
			for _, kw := range keywords {
				db.Exec(
					"INSERT INTO service_keywords (client_id, service_name, keyword, created_at) VALUES (?, ?, ?, ?)",
					clientID, svc, kw, now,
				)
			}
		}
	}
	return nil
}

func seedDefaultStatuses(db *sql.DB) error {
	rows, err := db.Query("SELECT client_id FROM clients")
	if err != nil {
		return nil
	}
	defer rows.Close()

	defaults := []string{"Não agendou", "Agendou", "Sem retorno / não evoluiu"}
	now := int64(0)

	for rows.Next() {
		var clientID int64
		if err := rows.Scan(&clientID); err != nil {
			continue
		}
		var count int
		db.QueryRow("SELECT COUNT(*) FROM lead_statuses WHERE client_id = ?", clientID).Scan(&count)
		if count > 0 {
			continue
		}
		for _, s := range defaults {
			db.Exec(
				"INSERT INTO lead_statuses (client_id, status_name, created_at) VALUES (?, ?, ?)",
				clientID, s, now,
			)
		}
	}
	return nil
}

func sanitizeEncoding(db *sql.DB) error {
	type statusRow struct {
		id      int64
		name    string
		created int64
	}
	type keywordRow struct {
		id      int64
		keyword string
		created int64
	}

	// Read all statuses first, then update to avoid SQLite read/write deadlock.
	var statuses []statusRow
	if rows, err := db.Query("SELECT status_id, status_name, created_at FROM lead_statuses"); err == nil {
		for rows.Next() {
			var r statusRow
			if err := rows.Scan(&r.id, &r.name, &r.created); err == nil && r.created != 0 {
				statuses = append(statuses, r)
			}
		}
		rows.Close()
	}
	for _, r := range statuses {
		cleaned := decodeLatin1(r.name)
		if cleaned != r.name {
			db.Exec("UPDATE lead_statuses SET status_name = ? WHERE status_id = ?", cleaned, r.id)
		}
	}

	// Read all keywords first, then update.
	var keywords []keywordRow
	if rows2, err := db.Query("SELECT keyword_id, keyword, created_at FROM service_keywords"); err == nil {
		for rows2.Next() {
			var r keywordRow
			if err := rows2.Scan(&r.id, &r.keyword, &r.created); err == nil && r.created != 0 {
				keywords = append(keywords, r)
			}
		}
		rows2.Close()
	}
	for _, r := range keywords {
		cleaned := decodeLatin1(r.keyword)
		if cleaned != r.keyword {
			db.Exec("UPDATE service_keywords SET keyword = ? WHERE keyword_id = ?", cleaned, r.id)
		}
	}

	return nil
}

func decodeLatin1(value string) string {
	if utf8.ValidString(value) {
		return value
	}
	dec := charmap.ISO8859_1.NewDecoder()
	cleaned := value
	for {
		next, err := dec.String(cleaned)
		if err != nil || next == cleaned {
			return cleaned
		}
		cleaned = next
	}
}