package timeoff

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

var ErrNotFound = fmt.Errorf("time off request not found")

type Request struct {
	ID          int64      `json:"id"`
	VolunteerID int64      `json:"volunteer_id"`
	StartsAt    time.Time  `json:"starts_at"`
	EndsAt      time.Time  `json:"ends_at"`
	Reason      string     `json:"reason,omitempty"`
	Status      string     `json:"status"`
	ReviewedBy  *int64     `json:"reviewed_by,omitempty"`
	ReviewedAt  *time.Time `json:"reviewed_at,omitempty"`
	CreatedAt   time.Time  `json:"created_at"`
	UpdatedAt   time.Time  `json:"updated_at"`
}

type CreateInput struct {
	StartsAt string `json:"starts_at"`
	EndsAt   string `json:"ends_at"`
	Reason   string `json:"reason"`
}

type ReviewInput struct {
	Status string `json:"status"` // "approved" | "rejected"
}

type Store struct {
	db *sql.DB
}

func NewStore(db *sql.DB) *Store {
	return &Store{db: db}
}

func (s *Store) Create(ctx context.Context, volunteerID int64, in CreateInput) (*Request, error) {
	res, err := s.db.ExecContext(ctx,
		`INSERT INTO time_off_requests (volunteer_id, starts_at, ends_at, reason) VALUES (?, ?, ?, ?)`,
		volunteerID, in.StartsAt, in.EndsAt, in.Reason,
	)
	if err != nil {
		return nil, fmt.Errorf("insert time off request: %w", err)
	}
	id, _ := res.LastInsertId()
	return s.GetByID(ctx, id)
}

func (s *Store) GetByID(ctx context.Context, id int64) (*Request, error) {
	req := &Request{}
	var startsAt, endsAt, createdAt, updatedAt string
	var reason sql.NullString
	var reviewedBy sql.NullInt64
	var reviewedAt sql.NullString

	err := s.db.QueryRowContext(ctx,
		`SELECT id, volunteer_id, starts_at, ends_at, reason, status, reviewed_by, reviewed_at, created_at, updated_at
		 FROM time_off_requests WHERE id = ?`, id,
	).Scan(&req.ID, &req.VolunteerID, &startsAt, &endsAt, &reason, &req.Status, &reviewedBy, &reviewedAt, &createdAt, &updatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get time off request: %w", err)
	}
	req.StartsAt, _ = time.Parse("2006-01-02 15:04:05", startsAt)
	req.EndsAt, _ = time.Parse("2006-01-02 15:04:05", endsAt)
	req.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
	req.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
	if reason.Valid {
		req.Reason = reason.String
	}
	if reviewedBy.Valid {
		req.ReviewedBy = &reviewedBy.Int64
	}
	if reviewedAt.Valid {
		t, _ := time.Parse("2006-01-02 15:04:05", reviewedAt.String)
		req.ReviewedAt = &t
	}
	return req, nil
}

func (s *Store) List(ctx context.Context, volunteerID int64) ([]Request, error) {
	query := `SELECT id, volunteer_id, starts_at, ends_at, reason, status, reviewed_by, reviewed_at, created_at, updated_at FROM time_off_requests`
	args := []any{}
	if volunteerID > 0 {
		query += ` WHERE volunteer_id = ?`
		args = append(args, volunteerID)
	}
	query += ` ORDER BY starts_at DESC`

	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list time off requests: %w", err)
	}
	defer rows.Close()

	var requests []Request
	for rows.Next() {
		var req Request
		var startsAt, endsAt, createdAt, updatedAt string
		var reason sql.NullString
		var reviewedBy sql.NullInt64
		var reviewedAt sql.NullString
		if err := rows.Scan(&req.ID, &req.VolunteerID, &startsAt, &endsAt, &reason, &req.Status, &reviewedBy, &reviewedAt, &createdAt, &updatedAt); err != nil {
			return nil, err
		}
		req.StartsAt, _ = time.Parse("2006-01-02 15:04:05", startsAt)
		req.EndsAt, _ = time.Parse("2006-01-02 15:04:05", endsAt)
		req.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
		req.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
		if reason.Valid {
			req.Reason = reason.String
		}
		if reviewedBy.Valid {
			req.ReviewedBy = &reviewedBy.Int64
		}
		if reviewedAt.Valid {
			t, _ := time.Parse("2006-01-02 15:04:05", reviewedAt.String)
			req.ReviewedAt = &t
		}
		requests = append(requests, req)
	}
	return requests, rows.Err()
}

func (s *Store) Review(ctx context.Context, id, reviewerID int64, status string) (*Request, error) {
	_, err := s.db.ExecContext(ctx,
		`UPDATE time_off_requests SET status=?, reviewed_by=?, reviewed_at=NOW(), updated_at=NOW() WHERE id=?`,
		status, reviewerID, id,
	)
	if err != nil {
		return nil, fmt.Errorf("review time off request: %w", err)
	}
	return s.GetByID(ctx, id)
}