package storage

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"github.com/thepeterstone/claudomator/internal/story"
)

// StoryFilter specifies criteria for listing stories. Zero-value fields are
// not applied as filters.
type StoryFilter struct {
	Status string
	EpicID string
}

// CreateStory inserts a new story.
func (s *DB) CreateStory(st *story.Story) error {
	now := time.Now().UTC()
	st.CreatedAt = now
	st.UpdatedAt = now

	ac := st.AcceptanceCriteria
	if ac == nil {
		ac = []string{}
	}
	acJSON, err := json.Marshal(ac)
	if err != nil {
		return fmt.Errorf("marshaling acceptance_criteria: %w", err)
	}

	_, err = s.db.Exec(`
		INSERT INTO stories (id, epic_id, project_id, name, branch_name, status, discovery_source, spec, acceptance_criteria_json, validation_json, deploy_config, priority, root_task_id, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		st.ID, st.EpicID, st.ProjectID, st.Name, st.BranchName, st.Status, st.DiscoverySource, st.Spec,
		string(acJSON), rawJSONOrNull(st.Validation), rawJSONOrNull(st.DeployConfig), st.Priority, st.RootTaskID, now, now,
	)
	return err
}

// GetStory retrieves a story by ID.
func (s *DB) GetStory(id string) (*story.Story, error) {
	row := s.db.QueryRow(storySelectColumns()+` FROM stories WHERE id = ?`, id)
	return scanStory(row)
}

// ListStories returns stories matching the given filter.
func (s *DB) ListStories(filter StoryFilter) ([]*story.Story, error) {
	query := storySelectColumns() + ` FROM stories WHERE 1=1`
	var args []interface{}
	if filter.Status != "" {
		query += " AND status = ?"
		args = append(args, filter.Status)
	}
	if filter.EpicID != "" {
		query += " AND epic_id = ?"
		args = append(args, filter.EpicID)
	}
	query += " ORDER BY created_at DESC"

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stories []*story.Story
	for rows.Next() {
		st, err := scanStoryRows(rows)
		if err != nil {
			return nil, err
		}
		stories = append(stories, st)
	}
	return stories, rows.Err()
}

// UpdateStory replaces all editable fields (everything except id/created_at).
// This phase enforces no state-machine validation on Status transitions —
// it writes whatever status string it is given, the same trust level as
// UpdateProject/UpdateTask's field replacement (not UpdateTaskState).
func (s *DB) UpdateStory(st *story.Story) error {
	now := time.Now().UTC()

	ac := st.AcceptanceCriteria
	if ac == nil {
		ac = []string{}
	}
	acJSON, err := json.Marshal(ac)
	if err != nil {
		return fmt.Errorf("marshaling acceptance_criteria: %w", err)
	}

	_, err = s.db.Exec(`
		UPDATE stories SET epic_id = ?, project_id = ?, name = ?, branch_name = ?, status = ?, discovery_source = ?,
		spec = ?, acceptance_criteria_json = ?, validation_json = ?, deploy_config = ?, priority = ?, root_task_id = ?, updated_at = ?
		WHERE id = ?`,
		st.EpicID, st.ProjectID, st.Name, st.BranchName, st.Status, st.DiscoverySource,
		st.Spec, string(acJSON), rawJSONOrNull(st.Validation), rawJSONOrNull(st.DeployConfig), st.Priority, st.RootTaskID, now,
		st.ID,
	)
	if err != nil {
		return err
	}
	st.UpdatedAt = now
	return nil
}

func storySelectColumns() string {
	return `SELECT id, epic_id, project_id, name, branch_name, status, discovery_source, spec, acceptance_criteria_json, validation_json, deploy_config, priority, root_task_id, created_at, updated_at`
}

func scanStory(row scanner) (*story.Story, error) {
	var st story.Story
	var epicID, projectID, branchName, discoverySource, spec, validationJSON, deployConfig, priority, rootTaskID sql.NullString
	var acJSON string
	if err := row.Scan(
		&st.ID, &epicID, &projectID, &st.Name, &branchName, &st.Status, &discoverySource, &spec,
		&acJSON, &validationJSON, &deployConfig, &priority, &rootTaskID, &st.CreatedAt, &st.UpdatedAt,
	); err != nil {
		return nil, err
	}
	st.EpicID = epicID.String
	st.ProjectID = projectID.String
	st.BranchName = branchName.String
	st.DiscoverySource = discoverySource.String
	st.Spec = spec.String
	st.Priority = priority.String
	st.RootTaskID = rootTaskID.String

	if acJSON == "" {
		acJSON = "[]"
	}
	if err := json.Unmarshal([]byte(acJSON), &st.AcceptanceCriteria); err != nil {
		return nil, fmt.Errorf("unmarshaling acceptance_criteria: %w", err)
	}
	if validationJSON.Valid && validationJSON.String != "" {
		st.Validation = json.RawMessage(validationJSON.String)
	}
	if deployConfig.Valid && deployConfig.String != "" {
		st.DeployConfig = json.RawMessage(deployConfig.String)
	}
	return &st, nil
}

func scanStoryRows(rows *sql.Rows) (*story.Story, error) {
	return scanStory(rows)
}

// rawJSONOrNull returns nil (SQL NULL) for an empty json.RawMessage, or the
// raw JSON text otherwise. Used for the nullable validation_json/deploy_config
// columns, which carry freeform JSON blobs, not a fixed schema.
func rawJSONOrNull(raw json.RawMessage) interface{} {
	if len(raw) == 0 {
		return nil
	}
	return string(raw)
}