package store

import (
	"database/sql"
	"encoding/json"
	"errors"
)

// ExecEnvRequest 是一次扩展包申请（分层 Python 环境）。
type ExecEnvRequest struct {
	ID             string
	ConversationID string
	Owner          string
	Spec           string
	Reason         string
	Status         string
	LayerID        string
	Detail         string
	Approver       string
	CreatedAt      string
	UpdatedAt      string
}

// ExecEnvBinding 是会话挂载的层。Layers 是扩展层 ID；Isolated 非空时替换基础层。
type ExecEnvBinding struct {
	ConversationID string
	BaseID         string
	Layers         []string
	Isolated       string
	UpdatedAt      string
}

func (s *Store) InsertExecEnvRequest(r ExecEnvRequest) error {
	_, err := s.db.Exec(s.q(`INSERT INTO exec_env_requests(
		id,conversation_id,owner,spec,reason,status,layer_id,detail,approver,created_at,updated_at)
		VALUES(?,?,?,?,?,?,?,?,?,?,?)`),
		r.ID, r.ConversationID, r.Owner, r.Spec, r.Reason, r.Status, r.LayerID, r.Detail, r.Approver, r.CreatedAt, r.UpdatedAt)
	return err
}

func (s *Store) GetExecEnvRequest(id string) (ExecEnvRequest, error) {
	var r ExecEnvRequest
	err := s.db.QueryRow(s.q(`SELECT id,conversation_id,owner,spec,reason,status,layer_id,detail,approver,created_at,updated_at
		FROM exec_env_requests WHERE id=?`), id).Scan(
		&r.ID, &r.ConversationID, &r.Owner, &r.Spec, &r.Reason, &r.Status, &r.LayerID, &r.Detail, &r.Approver, &r.CreatedAt, &r.UpdatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return ExecEnvRequest{}, ErrNotFound
	}
	return r, err
}

func (s *Store) ListExecEnvRequests(conversationID string, limit int) ([]ExecEnvRequest, error) {
	if limit <= 0 {
		limit = 20
	}
	q := `SELECT id,conversation_id,owner,spec,reason,status,layer_id,detail,approver,created_at,updated_at
		FROM exec_env_requests`
	var args []any
	if conversationID != "" {
		q += ` WHERE conversation_id=?`
		args = append(args, conversationID)
	}
	q += ` ORDER BY created_at DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.Query(s.q(q), args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanExecEnvRequests(rows)
}

func scanExecEnvRequests(rows *sql.Rows) ([]ExecEnvRequest, error) {
	var out []ExecEnvRequest
	for rows.Next() {
		var r ExecEnvRequest
		if err := rows.Scan(&r.ID, &r.ConversationID, &r.Owner, &r.Spec, &r.Reason, &r.Status, &r.LayerID, &r.Detail, &r.Approver, &r.CreatedAt, &r.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func (s *Store) UpdateExecEnvRequest(id, status, layerID, detail, approver string) error {
	res, err := s.db.Exec(s.q(`UPDATE exec_env_requests SET status=?, layer_id=?, detail=?, approver=?, updated_at=? WHERE id=?`),
		status, layerID, detail, approver, nowISO(), id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s *Store) GetExecEnvBinding(conversationID string) (ExecEnvBinding, error) {
	var b ExecEnvBinding
	var layers string
	err := s.db.QueryRow(s.q(`SELECT conversation_id,base_id,layers,isolated,updated_at FROM exec_env_bindings WHERE conversation_id=?`), conversationID).
		Scan(&b.ConversationID, &b.BaseID, &layers, &b.Isolated, &b.UpdatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return ExecEnvBinding{}, nil
	}
	if err != nil {
		return ExecEnvBinding{}, err
	}
	_ = json.Unmarshal([]byte(layers), &b.Layers)
	return b, nil
}

func (s *Store) PutExecEnvBinding(b ExecEnvBinding) error {
	raw, err := json.Marshal(b.Layers)
	if err != nil {
		return err
	}
	_, err = s.db.Exec(s.q(`INSERT INTO exec_env_bindings(conversation_id,base_id,layers,isolated,updated_at)
		VALUES(?,?,?,?,?)
		ON CONFLICT(conversation_id) DO UPDATE SET base_id=excluded.base_id, layers=excluded.layers, isolated=excluded.isolated, updated_at=excluded.updated_at`),
		b.ConversationID, b.BaseID, string(raw), b.Isolated, b.UpdatedAt)
	return err
}

func (s *Store) ListExecEnvBindings() ([]ExecEnvBinding, error) {
	rows, err := s.db.Query(s.q(`SELECT conversation_id,base_id,layers,isolated,updated_at FROM exec_env_bindings`))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []ExecEnvBinding
	for rows.Next() {
		var b ExecEnvBinding
		var layers string
		if err := rows.Scan(&b.ConversationID, &b.BaseID, &layers, &b.Isolated, &b.UpdatedAt); err != nil {
			return nil, err
		}
		_ = json.Unmarshal([]byte(layers), &b.Layers)
		out = append(out, b)
	}
	return out, rows.Err()
}

func (s *Store) InsertExecEnvAudit(owner, action, actor, target, outcome, entry string) error {
	_, err := s.db.Exec(s.q(`INSERT INTO audit_events(time,owner,action,actor,target,evidence,trace_id,entry,instance_id,operation_id,outcome)
		VALUES(?,?,?,?,?,?,?,?,?,?,?)`),
		nowISO(), owner, action, actor, target, entry, "", "execenv", s.instanceID, "", outcome)
	return err
}