crm-native/src/server/db/repo/client.go

package repo

import (
	"database/sql"
	"fmt"
	"saura/src/common"
)

const (
	ClientTypePhysical     string = "Физ"
	ClientTypeLegal        string = "Юр"
	ClientTypeSupplier     string = "Поставщик"
	ClientTypeEmployee     string = "Сотрудник"
	ClientTypeCounterparty string = "Контрагент"
	ClientTypeCustomer     string = "Покупатель"
)

type ClientInfo struct {
	Id               string       `db:"id" json:"id" form:"id"`
	Id2              string       `db:"id2" json:"id2" form:"id2"`
	Mark             string       `db:"mark" json:"mark" form:"mark"`
	Contractor       bool         `db:"contractor" json:"contractor" form:"contractor"`
	FullName         string       `db:"full_name" json:"full_name" form:"full_name"`
	Type             string       `db:"type" json:"type" form:"type"`
	Phones           []string     `json:"phones" form:"phones"`
	Email            string       `db:"email" json:"email" form:"email"`
	LegalAddress     string       `db:"legal_address" json:"legal_address" form:"legal_address"`
	PhysicalAddress  string       `db:"physical_address" json:"physical_address" form:"physical_address"`
	RegistrationDate string       `db:"registration_date" json:"registration_date" form:"registration_date"`
	AdChannel        string       `db:"ad_channel" json:"ad_channel" form:"ad_channel"`
	RegData1         string       `db:"reg_data_1" json:"reg_data_1" form:"reg_data_1"`
	RegData2         string       `db:"reg_data_2" json:"reg_data_2" form:"reg_data_2"`
	Note             string       `db:"note" json:"note" form:"note"`
	RequestCount     int          `db:"request_count" json:"request_count" form:"request_count"`
	Birthday         string       `db:"birthday" json:"birthday" form:"birthday"`
	Income           common.Money `db:"income" json:"income" form:"income"`
}

type ClientRepo struct {
	db    *sql.DB
	dbUrl string
}

func NewClientRepo(db *sql.DB, dbUrl string) *ClientRepo {
	return &ClientRepo{db: db, dbUrl: dbUrl}
}

func (r *ClientRepo) InsertClient(client ClientInfo) error {
	var err error
	var exists bool

	err = r.db.QueryRow("SELECT EXISTS(SELECT 1 FROM clients WHERE id = ? OR id2 = ?)", client.Id, client.Id2).Scan(&exists)
	if err != nil {
		return fmt.Errorf("failed to check if client exists: %w", err)
	}
	if exists {
		return fmt.Errorf("client with id %s already exists", client.Id)
	}

	_, err = r.db.Exec(
		`
    INSERT INTO clients (
      id, id2, mark, contractor, full_name, type, email,
      legal_address, physical_address, registration_date, ad_channel,
      reg_data_1, reg_data_2, note, request_count, birthday, income
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
		client.Id, client.Id2, client.Mark, client.Contractor, client.FullName,
		client.Type, client.Email, client.LegalAddress,
		client.PhysicalAddress, client.RegistrationDate, client.AdChannel,
		client.RegData1, client.RegData2, client.Note, client.RequestCount,
		client.Birthday, client.Income,
	)
	if err != nil {
		return fmt.Errorf("failed to insert client: %w", err)
	}

	return nil
}

func (r *ClientRepo) FetchClientByID(id string) (*ClientInfo, error) {
	var err error

	row := r.db.QueryRow(`
		SELECT
    	id, id2, mark, contractor, full_name, type, email,
      legal_address, physical_address, registration_date, ad_channel,
      reg_data_1, reg_data_2, note, request_count, birthday, income
    FROM clients
    WHERE id = ?
    `, id)

	var client ClientInfo
	err = row.Scan(
		&client.Id, &client.Id2, &client.Mark, &client.Contractor, &client.FullName,
		&client.Type, &client.Email, &client.LegalAddress,
		&client.PhysicalAddress, &client.RegistrationDate, &client.AdChannel,
		&client.RegData1, &client.RegData2, &client.Note, &client.RequestCount,
		&client.Birthday, &client.Income,
	)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("client with id %s not found", id)
	} else if err != nil {
		return nil, fmt.Errorf("failed to scan row: %w", err)
	}

	phones, err := r.FetchClientPhones(client.Id)
	if err != nil {
		return nil, fmt.Errorf("error: %w", err)
	}
	client.Phones = phones

	return &client, nil
}

func (r *ClientRepo) InsertClientPhones(client_id string, phones []string) error {
	for _, phone := range phones {
		_, err := r.db.Exec("INSERT INTO client_phones VALUES(?, ?)", client_id, phone)
		if err != nil {
			return err
		}
	}
	return nil
}

func (r *ClientRepo) FetchClientPhones(client_id string) ([]string, error) {
	rows, err := r.db.Query("SELECT phone FROM client_phones WHERE client_id = ?", client_id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []string
	for rows.Next() {
		var tag string
		rows.Scan(&tag)
		tags = append(tags, tag)
	}
	return tags, nil
}

func (r *ClientRepo) FetchClients(count int, offset int) ([]ClientInfo, error) {
	rows, err := r.db.Query(`
        SELECT
            id, id2, mark, contractor, full_name, type, email,
            legal_address, physical_address, registration_date, ad_channel,
            reg_data_1, reg_data_2, note, request_count, birthday, income
        FROM clients
        LIMIT ? OFFSET ?
    `, count, offset)
	if err != nil {
		return nil, fmt.Errorf("failed to execute query: %w", err)
	}
	defer rows.Close()

	var clients []ClientInfo
	for rows.Next() {
		var client ClientInfo
		err := rows.Scan(
			&client.Id,
			&client.Id2,
			&client.Mark,
			&client.Contractor,
			&client.FullName,
			&client.Type,
			&client.Email,
			&client.LegalAddress,
			&client.PhysicalAddress,
			&client.RegistrationDate, // time.Time или sql.NullTime
			&client.AdChannel,
			&client.RegData1,
			&client.RegData2,
			&client.Note,         // sql.NullString
			&client.RequestCount, // int или sql.NullInt32
			&client.Birthday,     // time.Time или sql.NullTime
			&client.Income,       // float64 или sql.NullFloat64
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}

		phones, err := r.FetchClientPhones(client.Id)
		if err != nil {
			return nil, fmt.Errorf("rows error: %w", err)
		}
		client.Phones = phones

		clients = append(clients, client)
	}

	// Проверка ошибок после итерации
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("rows error: %w", err)
	}

	return clients, nil
}

func (r *ClientRepo) FetchClientsCount() (int, error) {
	var count int
	query := "SELECT COUNT(*) FROM clients"
	err := r.db.QueryRow(query).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("failed to fetch count: %w", err)
	}
	return count, nil
}

func (r *ClientRepo) DeleteClient(id string) error {
	query := "DELETE FROM clients WHERE id = ?"
	result, err := r.db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("failed to delete client: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("client with id %s not found", id)
	}

	return nil
}

func (r *ClientRepo) UpdateClient(id string, client ClientInfo) error {
	// Проверим, существует ли клиент
	var exists string
	err := r.db.QueryRow("SELECT id FROM clients WHERE id = ?", id).Scan(&exists)
	if err == sql.ErrNoRows {
		return fmt.Errorf("client with id %s not found", id)
	} else if err != nil {
		return fmt.Errorf("failed to check client existence: %w", err)
	}

	query := `
        UPDATE clients SET
            id = ?,
            id2 = ?,
            mark = ?,
            contractor = ?,
            full_name = ?,
            type = ?,
            email = ?,
            legal_address = ?,
            physical_address = ?,
            registration_date = ?,
            ad_channel = ?,
            reg_data_1 = ?,
            reg_data_2 = ?,
            note = ?,
            request_count = ?,
            birthday = ?,
            income = ?
        WHERE id = ?
    `

	contractorInt := 0
	if client.Contractor {
		contractorInt = 1
	}

	_, err = r.db.Exec(query,
		id,
		client.Id2,
		client.Mark,
		contractorInt,
		client.FullName,
		client.Type,
		client.Email,
		client.LegalAddress,
		client.PhysicalAddress,
		client.RegistrationDate,
		client.AdChannel,
		client.RegData1,
		client.RegData2,
		client.Note,
		client.RequestCount,
		client.Birthday,
		client.Income,
		id,
	)
	if err != nil {
		return fmt.Errorf("failed to update client: %w", err)
	}

	return nil
}