package persistence

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

	"github.com/nexuscrm/shared/pkg/models"
)

type BulkRepository struct {
	db *sql.DB
}

func NewBulkRepository(db *sql.DB) *BulkRepository {
	return &BulkRepository{db: db}
}

func (r *BulkRepository) CreateJob(ctx context.Context, job *models.BulkJob) error {
	query := `
		INSERT INTO _System_BulkJob (
			Id, Object, Operation, State, ConcurrencyMode, 
			CreatedById, CreatedDate, LastModifiedDate,
			JobType, ContentUrl
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`
	_, err := r.db.ExecContext(ctx, query,
		job.ID, job.Object, job.Operation, job.State, job.ConcurrencyMode,
		job.CreatedById, job.CreatedDate, job.SystemModstamp,
		job.JobType, job.ContentUrl,
	)
	return err
}

func (r *BulkRepository) GetJob(ctx context.Context, jobId string) (*models.BulkJob, error) {
	query := `
		SELECT 
			Id, Object, Operation, State, ConcurrencyMode, 
			CreatedById, CreatedDate, LastModifiedDate,
			ErrorMessage, TotalProcessingTime, ApiActiveProcessingTime, ApexProcessingTime,
			JobType, ContentUrl
		FROM _System_BulkJob 
		WHERE Id = ?
	`
	row := r.db.QueryRowContext(ctx, query, jobId)

	var job models.BulkJob
	var errorMessage, jobType, contentUrl sql.NullString

	err := row.Scan(
		&job.ID, &job.Object, &job.Operation, &job.State, &job.ConcurrencyMode,
		&job.CreatedById, &job.CreatedDate, &job.SystemModstamp,
		&errorMessage, &job.TotalProcessingTime, &job.ApiActiveProcessingTime, &job.ApexProcessingTime,
		&jobType, &contentUrl,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}

	if errorMessage.Valid {
		job.ErrorMessage = errorMessage.String
	}
	if jobType.Valid {
		job.JobType = jobType.String
	}
	if contentUrl.Valid {
		job.ContentUrl = contentUrl.String
	}

	return &job, nil
}

func (r *BulkRepository) UpdateJobState(ctx context.Context, jobId string, state models.BulkJobState) error {
	query := `
		UPDATE _System_BulkJob 
		SET State = ?, LastModifiedDate = ? 
		WHERE Id = ?
	`
	_, err := r.db.ExecContext(ctx, query, state, time.Now(), jobId)
	return err
}

func (r *BulkRepository) UpdateJob(ctx context.Context, job *models.BulkJob) error {
	query := `
		UPDATE _System_BulkJob 
		SET State = ?, LastModifiedDate = ?, ErrorMessage = ?,
		    JobType = ?, ContentUrl = ?, Object = ?
		WHERE Id = ?
	`
	_, err := r.db.ExecContext(ctx, query,
		job.State, time.Now(), job.ErrorMessage,
		job.JobType, job.ContentUrl, job.Object, job.ID,
	)
	return err
}

func (r *BulkRepository) CreateBatch(ctx context.Context, batch *models.BulkBatch) error {
	query := `
		INSERT INTO _System_BulkBatch (
			Id, JobId, State, 
			CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?)
	`
	_, err := r.db.ExecContext(ctx, query,
		batch.ID, batch.JobID, batch.State,
		batch.CreatedDate, batch.SystemModstamp,
	)
	return err
}

func (r *BulkRepository) GetBatches(ctx context.Context, jobId string) ([]*models.BulkBatch, error) {
	query := `
        SELECT 
            Id, JobId, State, RecordsProcessed, RecordsFailed, TotalRecords,
            CreatedDate, LastModifiedDate
        FROM _System_BulkBatch
        WHERE JobId = ?
    `
	rows, err := r.db.QueryContext(ctx, query, jobId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var batches []*models.BulkBatch
	for rows.Next() {
		var b models.BulkBatch
		if err := rows.Scan(
			&b.ID, &b.JobID, &b.State, &b.RecordsProcessed, &b.RecordsFailed, &b.TotalRecords,
			&b.CreatedDate, &b.SystemModstamp,
		); err != nil {
			return nil, err
		}
		batches = append(batches, &b)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return batches, nil
}