package memory

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"regexp"
	"strings"
	"time"
)

// CalendarItem is a date-attached todo/event row.
type CalendarItem struct {
	ID        int64  `json:"id"`
	Title     string `json:"title"`
	Notes     string `json:"notes"`
	Status    string `json:"status"` // open, done, cancelled
	DueAt     string `json:"due_at,omitempty"`
	Source    string `json:"source"` // manual, agent
	CreatedAt string `json:"created_at"`
	UpdatedAt string `json:"updated_at"`
}

// CalendarPendingProposal is a proposed change awaiting user confirmation.
type CalendarPendingProposal struct {
	ID          int64           `json:"id"`
	Payload     json.RawMessage `json:"payload"`
	Status      string          `json:"status"`
	CreatedAt   string          `json:"created_at"`
	ExpiresAt   string          `json:"expires_at"`
	SummaryText string          `json:"summary,omitempty"`
}

// CalendarProposePayload is stored in calendar_pending.payload_json.
type CalendarProposePayload struct {
	Action string        `json:"action"` // create, update, delete, complete
	ID     int64         `json:"id,omitempty"`
	Item   CalendarInput `json:"item,omitempty"`
}

// CalendarPendingEditInput updates the item portion of a pending create/update proposal.
type CalendarPendingEditInput struct {
	Title  string `json:"title"`
	Notes  string `json:"notes"`
	Status string `json:"status"`
	DueAt  string `json:"due_at"`
}

// CalendarInput is used for create/update from API or proposals.
type CalendarInput struct {
	Title  string `json:"title"`
	Notes  string `json:"notes"`
	Status string `json:"status"`
	DueAt  string `json:"due_at"`
	Source string `json:"source"`
}

func (s *Store) bumpCalendarRevision() error {
	if s == nil || s.db == nil {
		return fmt.Errorf("store not available")
	}
	_, err := s.db.Exec(`UPDATE calendar_meta SET v = v + 1 WHERE k = 'revision'`)
	return err
}

// CalendarRevision returns the monotonic revision counter for realtime sync.
func (s *Store) CalendarRevision() (int64, error) {
	if s == nil || s.db == nil {
		return 0, fmt.Errorf("store not available")
	}
	var v int64
	err := s.db.QueryRow(`SELECT v FROM calendar_meta WHERE k = 'revision'`).Scan(&v)
	if err == sql.ErrNoRows {
		return 0, nil
	}
	return v, err
}

// ListCalendarRange returns items overlapping [from, to) (RFC3339 or date strings).
func (s *Store) ListCalendarRange(from, to string) ([]CalendarItem, error) {
	if s == nil || s.db == nil {
		return nil, fmt.Errorf("store not available")
	}
	from = strings.TrimSpace(from)
	to = strings.TrimSpace(to)
	if from == "" || to == "" {
		return nil, fmt.Errorf("from and to are required")
	}
	fromDate, toDateExclusive, err := normalizedDateWindow(from, to)
	if err != nil {
		return nil, err
	}
	// Items are date-only: due_at in [from, to) OR undated.
	// Use normalized date bounds so:
	// - to at midnight/date-only stays exclusive (existing behavior)
	// - to with non-midnight time includes that calendar day.
	rows, err := s.db.Query(`
		SELECT id, title, notes, status, due_at, source, created_at, updated_at
		FROM calendar_items
		WHERE status != 'cancelled' AND (
			(due_at IS NOT NULL AND due_at != '' AND date(due_at) >= date(?) AND date(due_at) < date(?))
			OR (due_at IS NULL OR due_at = '')
		)
		ORDER BY
			CASE
				WHEN (due_at IS NULL OR due_at = '') THEN 0
				ELSE 1
			END,
			COALESCE(due_at, created_at), id
	`, fromDate, toDateExclusive)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanCalendarItems(rows)
}

var datePrefixRE = regexp.MustCompile(`^\d{4}-\d{2}-\d{2}`)

func datePartString(v string) string {
	v = strings.TrimSpace(v)
	if len(v) < 10 {
		return ""
	}
	p := v[:10]
	if datePrefixRE.MatchString(p) {
		return p
	}
	return ""
}

func normalizedDateWindow(from, to string) (fromDate string, toDateExclusive string, err error) {
	fromDate = datePartString(from)
	toDate := datePartString(to)
	if fromDate == "" || toDate == "" {
		return "", "", fmt.Errorf("from/to must start with YYYY-MM-DD")
	}
	// If to includes a time and it's not midnight, include that day by bumping exclusive end +1 day.
	// Examples:
	// - to=2026-03-26T00:00:00 -> exclusive 2026-03-26
	// - to=2026-03-25T23:59:59 -> exclusive 2026-03-26
	toDateExclusive = toDate
	toTrim := strings.TrimSpace(to)
	if len(toTrim) >= 19 && strings.Contains(toTrim, "T") {
		tm := toTrim[11:19]
		if tm != "00:00:00" {
			d, parseErr := time.Parse("2006-01-02", toDate)
			if parseErr != nil {
				return "", "", parseErr
			}
			toDateExclusive = d.AddDate(0, 0, 1).Format("2006-01-02")
		}
	}
	return fromDate, toDateExclusive, nil
}

func scanCalendarItems(rows *sql.Rows) ([]CalendarItem, error) {
	var out []CalendarItem
	for rows.Next() {
		var it CalendarItem
		var dueAt sql.NullString
		if err := rows.Scan(
			&it.ID, &it.Title, &it.Notes, &it.Status,
			&dueAt, &it.Source, &it.CreatedAt, &it.UpdatedAt,
		); err != nil {
			return nil, err
		}
		if dueAt.Valid {
			it.DueAt = dueAt.String
		}
		out = append(out, it)
	}
	return out, rows.Err()
}

// GetCalendarItem returns one item by id.
func (s *Store) GetCalendarItem(id int64) (*CalendarItem, error) {
	if s == nil || s.db == nil {
		return nil, fmt.Errorf("store not available")
	}
	row := s.db.QueryRow(`
		SELECT id, title, notes, status, due_at, source, created_at, updated_at
		FROM calendar_items WHERE id = ?
	`, id)
	var it CalendarItem
	var dueAt sql.NullString
	if err := row.Scan(
		&it.ID, &it.Title, &it.Notes, &it.Status,
		&dueAt, &it.Source, &it.CreatedAt, &it.UpdatedAt,
	); err != nil {
		return nil, err
	}
	if dueAt.Valid {
		it.DueAt = dueAt.String
	}
	return &it, nil
}

func optionalDuePtr(dueAt string) (due interface{}) {
	dueAt = strings.TrimSpace(dueAt)
	if dueAt != "" {
		due = dueAt
	}
	return due
}

func normalizeCalendarInput(in *CalendarInput, defaultSource string) error {
	if in == nil {
		return fmt.Errorf("item required")
	}
	in.Title = strings.TrimSpace(in.Title)
	if in.Title == "" {
		return fmt.Errorf("title required")
	}
	in.Notes = strings.TrimSpace(in.Notes)
	if in.Status == "" {
		in.Status = "open"
	}
	switch in.Status {
	case "open", "done", "cancelled":
	default:
		return fmt.Errorf("invalid status %q", in.Status)
	}
	if in.Source == "" {
		in.Source = defaultSource
	}
	if in.Source != "manual" && in.Source != "agent" {
		return fmt.Errorf("invalid source")
	}
	return nil
}

// AddCalendarItem inserts a calendar row (manual or from applied proposal).
func (s *Store) AddCalendarItem(in CalendarInput) (int64, error) {
	if err := normalizeCalendarInput(&in, "manual"); err != nil {
		return 0, err
	}
	now := time.Now().UTC().Format(time.RFC3339)
	due := optionalDuePtr(in.DueAt)
	res, err := s.db.Exec(`
		INSERT INTO calendar_items (title, notes, status, due_at, source, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`, in.Title, in.Notes, in.Status, due, in.Source, now, now)
	if err != nil {
		return 0, err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	_ = s.bumpCalendarRevision()
	return id, nil
}

// UpdateCalendarItem updates fields (empty strings clear optional times if passed explicitly - we use pointers in API; here merge from existing).
func (s *Store) UpdateCalendarItem(id int64, in CalendarInput) error {
	if err := normalizeCalendarInput(&in, "manual"); err != nil {
		return err
	}
	now := time.Now().UTC().Format(time.RFC3339)
	due := optionalDuePtr(in.DueAt)
	res, err := s.db.Exec(`
		UPDATE calendar_items SET title=?, notes=?, status=?, due_at=?, updated_at=?
		WHERE id=?
	`, in.Title, in.Notes, in.Status, due, now, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no calendar item with id %d", id)
	}
	_ = s.bumpCalendarRevision()
	return nil
}

// DeleteCalendarItem removes a row.
func (s *Store) DeleteCalendarItem(id int64) error {
	res, err := s.db.Exec(`DELETE FROM calendar_items WHERE id = ?`, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no calendar item with id %d", id)
	}
	_ = s.bumpCalendarRevision()
	return nil
}

// AddCalendarPending inserts a proposal; returns id.
func (s *Store) AddCalendarPending(payload CalendarProposePayload, ttl time.Duration) (int64, error) {
	if s == nil || s.db == nil {
		return 0, fmt.Errorf("store not available")
	}
	if ttl <= 0 {
		ttl = 24 * time.Hour
	}
	b, err := json.Marshal(payload)
	if err != nil {
		return 0, err
	}
	exp := time.Now().UTC().Add(ttl).Format(time.RFC3339)
	res, err := s.db.Exec(`INSERT INTO calendar_pending (payload_json, status, expires_at) VALUES (?, 'pending', ?)`, string(b), exp)
	if err != nil {
		return 0, err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	_ = s.bumpCalendarRevision()
	return id, nil
}

// ListCalendarPending returns pending proposals not expired.
func (s *Store) ListCalendarPending() ([]CalendarPendingProposal, error) {
	if s == nil || s.db == nil {
		return nil, fmt.Errorf("store not available")
	}
	now := time.Now().UTC().Format(time.RFC3339)
	rows, err := s.db.Query(`
		SELECT id, payload_json, status, created_at, expires_at FROM calendar_pending
		WHERE status = 'pending' AND expires_at > ?
		ORDER BY id DESC
	`, now)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CalendarPendingProposal
	for rows.Next() {
		var p CalendarPendingProposal
		var raw string
		if err := rows.Scan(&p.ID, &raw, &p.Status, &p.CreatedAt, &p.ExpiresAt); err != nil {
			return nil, err
		}
		p.Payload = json.RawMessage(raw)
		p.SummaryText = summarizePendingPayload(raw)
		out = append(out, p)
	}
	return out, rows.Err()
}

func summarizePendingPayload(raw string) string {
	var pl CalendarProposePayload
	if err := json.Unmarshal([]byte(raw), &pl); err != nil {
		return raw
	}
	switch pl.Action {
	case "create":
		return fmt.Sprintf("create: %s", pl.Item.Title)
	case "update":
		return fmt.Sprintf("update #%d: %s", pl.ID, pl.Item.Title)
	case "delete":
		return fmt.Sprintf("delete #%d", pl.ID)
	case "complete":
		return fmt.Sprintf("complete #%d", pl.ID)
	default:
		return pl.Action
	}
}

// ApplyCalendarPending applies a pending proposal and marks it applied.
func (s *Store) ApplyCalendarPending(id int64) error {
	if s == nil || s.db == nil {
		return fmt.Errorf("store not available")
	}
	var raw string
	var status string
	err := s.db.QueryRow(`SELECT payload_json, status FROM calendar_pending WHERE id = ?`, id).Scan(&raw, &status)
	if err != nil {
		return err
	}
	if status != "pending" {
		return fmt.Errorf("proposal %d is not pending", id)
	}
	var pl CalendarProposePayload
	if err := json.Unmarshal([]byte(raw), &pl); err != nil {
		return err
	}
	if err := s.applyPayload(&pl); err != nil {
		return err
	}
	_, err = s.db.Exec(`UPDATE calendar_pending SET status = 'applied' WHERE id = ?`, id)
	if err != nil {
		return err
	}
	// Item mutations already bumped revision via applyPayload.
	return nil
}

func (s *Store) applyPayload(pl *CalendarProposePayload) error {
	if pl == nil {
		return fmt.Errorf("nil payload")
	}
	switch pl.Action {
	case "create":
		pl.Item.Source = "agent"
		_, err := s.AddCalendarItem(pl.Item)
		return err
	case "update":
		if pl.ID <= 0 {
			return fmt.Errorf("id required")
		}
		pl.Item.Source = "agent"
		return s.UpdateCalendarItem(pl.ID, pl.Item)
	case "delete":
		if pl.ID <= 0 {
			return fmt.Errorf("id required")
		}
		return s.DeleteCalendarItem(pl.ID)
	case "complete":
		if pl.ID <= 0 {
			return fmt.Errorf("id required")
		}
		ex, err := s.GetCalendarItem(pl.ID)
		if err != nil {
			return err
		}
		in := CalendarInput{
			Title:  ex.Title,
			Notes:  ex.Notes,
			Status: "done",
			DueAt:  ex.DueAt,
			Source: "agent",
		}
		return s.UpdateCalendarItem(pl.ID, in)
	default:
		return fmt.Errorf("unknown action %q", pl.Action)
	}
}

// RejectCalendarPending marks a proposal rejected.
func (s *Store) RejectCalendarPending(id int64) error {
	if s == nil || s.db == nil {
		return fmt.Errorf("store not available")
	}
	res, err := s.db.Exec(`UPDATE calendar_pending SET status = 'rejected' WHERE id = ? AND status = 'pending'`, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no pending proposal %d", id)
	}
	_ = s.bumpCalendarRevision()
	return nil
}

// UpdateCalendarPendingItem edits a pending create/update proposal.
func (s *Store) UpdateCalendarPendingItem(id int64, in CalendarPendingEditInput) error {
	if s == nil || s.db == nil {
		return fmt.Errorf("store not available")
	}
	var raw string
	var status string
	if err := s.db.QueryRow(`SELECT payload_json, status FROM calendar_pending WHERE id = ?`, id).Scan(&raw, &status); err != nil {
		return err
	}
	if status != "pending" {
		return fmt.Errorf("proposal %d is not pending", id)
	}
	var pl CalendarProposePayload
	if err := json.Unmarshal([]byte(raw), &pl); err != nil {
		return err
	}
	if pl.Action != "create" && pl.Action != "update" {
		return fmt.Errorf("proposal %d is %s; only create/update can be edited", id, pl.Action)
	}
	item := pl.Item
	item.Title = strings.TrimSpace(in.Title)
	item.Notes = strings.TrimSpace(in.Notes)
	if strings.TrimSpace(in.Status) != "" {
		item.Status = strings.TrimSpace(in.Status)
	}
	item.DueAt = strings.TrimSpace(in.DueAt)
	item.Source = "agent"
	if err := normalizeCalendarInput(&item, "agent"); err != nil {
		return err
	}
	pl.Item = item
	b, err := json.Marshal(pl)
	if err != nil {
		return err
	}
	_, err = s.db.Exec(`UPDATE calendar_pending SET payload_json = ? WHERE id = ? AND status = 'pending'`, string(b), id)
	if err != nil {
		return err
	}
	_ = s.bumpCalendarRevision()
	return nil
}

// ExpireOldCalendarPending marks expired rows.
func (s *Store) ExpireOldCalendarPending() error {
	if s == nil || s.db == nil {
		return nil
	}
	now := time.Now().UTC().Format(time.RFC3339)
	_, _ = s.db.Exec(`UPDATE calendar_pending SET status = 'expired' WHERE status = 'pending' AND expires_at <= ?`, now)
	return nil
}