package connector_syncstate

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

	"github.com/pingcap-inc/nexuscrm-connectors/internal/id"
)

type repository interface {
	Get(ctx context.Context, installID, object, direction string) (*SyncState, error)
	UpsertCursor(ctx context.Context, installID, namespace, object, direction, cursor string) error
	UpsertError(ctx context.Context, installID, namespace, object, direction, errorMsg string) error
	ResetErrors(ctx context.Context, installID, object, direction string) (int64, error) // returns rows affected
	ListByInstallID(ctx context.Context, installID string) ([]SyncState, error)
}

type sqlRepository struct {
	db *sql.DB
}

func NewRepository(db *sql.DB) repository {
	return &sqlRepository{db: db}
}

func (r *sqlRepository) Get(ctx context.Context, installID, object, direction string) (*SyncState, error) {
	row := r.db.QueryRowContext(ctx, `
SELECT Id, InstalledPackId, Namespace, Object, Direction,
       COALESCE(`+"`Cursor`"+`, ''),
       LastSyncAt,
       COALESCE(LastErrorMessage, ''),
       LastErrorAt,
       RunningErrorCount,
       IsActive,
       CreatedDate, LastModifiedDate
  FROM _System_SyncState
 WHERE InstalledPackId = ? AND Object = ? AND Direction = ?`,
		installID, object, direction)
	var s SyncState
	err := row.Scan(&s.ID, &s.InstalledPackId, &s.Namespace, &s.Object, &s.Direction,
		&s.Cursor, &s.LastSyncAt, &s.LastErrorMessage, &s.LastErrorAt,
		&s.RunningErrorCount, &s.IsActive,
		&s.CreatedDate, &s.LastModifiedDate)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &s, nil
}

func (r *sqlRepository) UpsertCursor(ctx context.Context, installID, namespace, object, direction, cursor string) error {
	now := time.Now().UTC()
	id := id.Generate()
	_, err := r.db.ExecContext(ctx, `
INSERT INTO _System_SyncState
       (Id, InstalledPackId, Namespace, Object, Direction, `+"`Cursor`"+`, LastSyncAt,
        RunningErrorCount, IsActive, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, ?, ?, 0, TRUE, ?, ?)
ON DUPLICATE KEY UPDATE
       `+"`Cursor`"+` = VALUES(`+"`Cursor`"+`),
       LastSyncAt = VALUES(LastSyncAt),
       LastModifiedDate = VALUES(LastModifiedDate)`,
		id, installID, namespace, object, direction, cursor, now, now, now)
	return err
}

func (r *sqlRepository) UpsertError(ctx context.Context, installID, namespace, object, direction, errorMsg string) error {
	now := time.Now().UTC()
	id := id.Generate()
	_, err := r.db.ExecContext(ctx, `
INSERT INTO _System_SyncState
       (Id, InstalledPackId, Namespace, Object, Direction,
        LastErrorMessage, LastErrorAt, RunningErrorCount,
        IsActive, CreatedDate, LastModifiedDate)
VALUES (?, ?, ?, ?, ?, ?, ?, 1, TRUE, ?, ?)
ON DUPLICATE KEY UPDATE
       LastErrorMessage = VALUES(LastErrorMessage),
       LastErrorAt = VALUES(LastErrorAt),
       RunningErrorCount = RunningErrorCount + 1,
       LastModifiedDate = VALUES(LastModifiedDate)`,
		id, installID, namespace, object, direction, errorMsg, now, now, now)
	return err
}

func (r *sqlRepository) ResetErrors(ctx context.Context, installID, object, direction string) (int64, error) {
	now := time.Now().UTC()
	res, err := r.db.ExecContext(ctx, `
UPDATE _System_SyncState
   SET LastErrorMessage = NULL,
       LastErrorAt = NULL,
       RunningErrorCount = 0,
       LastModifiedDate = ?
 WHERE InstalledPackId = ? AND Object = ? AND Direction = ?`,
		now, installID, object, direction)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func (r *sqlRepository) ListByInstallID(ctx context.Context, installID string) ([]SyncState, error) {
	rows, err := r.db.QueryContext(ctx, `
SELECT Id, InstalledPackId, Namespace, Object, Direction,
       COALESCE(`+"`Cursor`"+`, ''), LastSyncAt,
       COALESCE(LastErrorMessage, ''), LastErrorAt,
       RunningErrorCount, IsActive,
       CreatedDate, LastModifiedDate
  FROM _System_SyncState
 WHERE InstalledPackId = ?
 ORDER BY Object, Direction`, installID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SyncState
	for rows.Next() {
		var s SyncState
		if err := rows.Scan(&s.ID, &s.InstalledPackId, &s.Namespace, &s.Object, &s.Direction,
			&s.Cursor, &s.LastSyncAt, &s.LastErrorMessage, &s.LastErrorAt,
			&s.RunningErrorCount, &s.IsActive,
			&s.CreatedDate, &s.LastModifiedDate); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}