package postgres

import (
	"strconv"
	"time"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
	"github.com/ajaxray/geek-life/model"
)

// TaskRepository implements the task repository interface for PostgreSQL
type TaskRepository struct {
	db *sqlx.DB
}

// NewTaskRepository creates a new task repository
func NewTaskRepository(db *sqlx.DB) *TaskRepository {
	return &TaskRepository{db: db}
}

// GetAll retrieves all tasks for a user
func (r *TaskRepository) GetAll(ctx *model.UserContext) ([]model.Task, error) {
	var tasks []model.Task
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE tenant_id = $1 AND user_id = $2 ORDER BY created_at DESC`
	err := r.db.Select(&tasks, query, ctx.Tenant.ID, ctx.User.ID)
	return tasks, err
}

// GetAllByProject retrieves all tasks for a specific project
func (r *TaskRepository) GetAllByProject(ctx *model.UserContext, project model.Project) ([]model.Task, error) {
	var tasks []model.Task
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE tenant_id = $1 AND user_id = $2 AND project_id = $3 ORDER BY created_at DESC`
	err := r.db.Select(&tasks, query, ctx.Tenant.ID, ctx.User.ID, project.ID)
	return tasks, err
}

// GetAllByDate retrieves all tasks for a specific date
func (r *TaskRepository) GetAllByDate(ctx *model.UserContext, date time.Time) ([]model.Task, error) {
	var tasks []model.Task
	startOfDay := time.Date(date.Year(), date.Month(), date.Day(), 0, 0, 0, 0, date.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)
	
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE tenant_id = $1 AND user_id = $2 AND due_date >= $3 AND due_date < $4 ORDER BY due_date ASC`
	err := r.db.Select(&tasks, query, ctx.Tenant.ID, ctx.User.ID, startOfDay, endOfDay)
	return tasks, err
}

// GetAllByDateRange retrieves all tasks within a date range
func (r *TaskRepository) GetAllByDateRange(ctx *model.UserContext, from, to time.Time) ([]model.Task, error) {
	var tasks []model.Task
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE tenant_id = $1 AND user_id = $2 AND due_date >= $3 AND due_date <= $4 ORDER BY due_date ASC`
	err := r.db.Select(&tasks, query, ctx.Tenant.ID, ctx.User.ID, from, to)
	return tasks, err
}

// GetByID retrieves a task by ID (string format for compatibility)
func (r *TaskRepository) GetByID(ctx *model.UserContext, ID string) (model.Task, error) {
	var task model.Task
	id, err := strconv.ParseInt(ID, 10, 64)
	if err != nil {
		return task, err
	}
	
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE id = $1 AND tenant_id = $2 AND user_id = $3`
	err = r.db.Get(&task, query, id, ctx.Tenant.ID, ctx.User.ID)
	return task, err
}

// GetByUUID retrieves a task by UUID
func (r *TaskRepository) GetByUUID(ctx *model.UserContext, UUID string) (model.Task, error) {
	var task model.Task
	query := `SELECT id, tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at 
			  FROM tasks WHERE uuid = $1 AND tenant_id = $2 AND user_id = $3`
	err := r.db.Get(&task, query, UUID, ctx.Tenant.ID, ctx.User.ID)
	return task, err
}

// Create creates a new task
func (r *TaskRepository) Create(ctx *model.UserContext, project model.Project, title, details, UUID string, dueDate *int64) (model.Task, error) {
	if UUID == "" {
		UUID = uuid.New().String()
	}

	task := model.Task{
		TenantID:  ctx.Tenant.ID,
		UserID:    ctx.User.ID,
		ProjectID: project.ID,
		UUID:      UUID,
		Title:     title,
		Details:   details,
		Completed: false,
		DueDate:   dueDate,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	query := `INSERT INTO tasks (tenant_id, user_id, project_id, uuid, title, details, completed, due_date, created_at, updated_at) 
			  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING id`
	
	var dueDateValue interface{}
	if dueDate != nil {
		dueDateValue = time.Unix(*dueDate, 0)
	}
	
	err := r.db.QueryRow(query, task.TenantID, task.UserID, task.ProjectID, task.UUID, task.Title, task.Details, task.Completed, dueDateValue, task.CreatedAt, task.UpdatedAt).Scan(&task.ID)
	if err != nil {
		return model.Task{}, err
	}

	return task, nil
}

// Update updates an existing task
func (r *TaskRepository) Update(ctx *model.UserContext, t *model.Task) error {
	t.UpdatedAt = time.Now()
	
	var dueDateValue interface{}
	if t.DueDate != nil {
		dueDateValue = time.Unix(*t.DueDate, 0)
	}
	
	query := `UPDATE tasks SET title = $1, details = $2, completed = $3, due_date = $4, updated_at = $5 
			  WHERE id = $6 AND tenant_id = $7 AND user_id = $8`
	_, err := r.db.Exec(query, t.Title, t.Details, t.Completed, dueDateValue, t.UpdatedAt, t.ID, ctx.Tenant.ID, ctx.User.ID)
	return err
}

// UpdateField updates a specific field of a task
func (r *TaskRepository) UpdateField(ctx *model.UserContext, t *model.Task, field string, value interface{}) error {
	t.UpdatedAt = time.Now()
	query := `UPDATE tasks SET ` + field + ` = $1, updated_at = $2 
			  WHERE id = $3 AND tenant_id = $4 AND user_id = $5`
	_, err := r.db.Exec(query, value, t.UpdatedAt, t.ID, ctx.Tenant.ID, ctx.User.ID)
	return err
}

// Delete deletes a task
func (r *TaskRepository) Delete(ctx *model.UserContext, t *model.Task) error {
	query := `DELETE FROM tasks WHERE id = $1 AND tenant_id = $2 AND user_id = $3`
	_, err := r.db.Exec(query, t.ID, ctx.Tenant.ID, ctx.User.ID)
	return err
}