package storage

import (
	"database/sql"
	"encoding/json"
	"gitea.deepblack.cloud/chorus/bubble/models"
	_ "github.com/mattn/go-sqlite3"
	"io/ioutil"
	"path/filepath"
)

// SQLiteStore is an implementation of the Storage interface using SQLite.
type SQLiteStore struct {
	DB *sql.DB
}

// NewSQLiteStore connects to the SQLite database and returns a new SQLiteStore.
func NewSQLiteStore(dbPath string) (*SQLiteStore, error) {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, err
	}
	if err = db.Ping(); err != nil {
		return nil, err
	}
	return &SQLiteStore{DB: db}, nil
}

// Setup reads the schema file and executes it to create the database tables.
func (s *SQLiteStore) Setup(schemaPath string) error {
	schema, err := ioutil.ReadFile(filepath.Clean(schemaPath))
	if err != nil {
		return err
	}
	_, err = s.DB.Exec(string(schema))
	return err
}

// GetDecisionMetadata retrieves a single decision record's metadata from the database.
func (s *SQLiteStore) GetDecisionMetadata(drID string) (*models.DecisionRecordSummary, error) {
	row := s.DB.QueryRow("SELECT id, statement, lifecycle_state, role_exposure, tags, timestamp FROM decisions WHERE id = ?", drID)

	var summary models.DecisionRecordSummary
	var roleExposureJSON, tagsJSON string

	err := row.Scan(
		&summary.ID,
		&summary.Statement,
		&summary.LifecycleState,
		&roleExposureJSON,
		&tagsJSON,
		&summary.Timestamp,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // Return nil, nil if not found
		}
		return nil, err
	}

	// Unmarshal JSON fields
	if err := json.Unmarshal([]byte(roleExposureJSON), &summary.RoleExposure); err != nil {
		return nil, err
	}
	if err := json.Unmarshal([]byte(tagsJSON), &summary.Tags); err != nil {
		return nil, err
	}

	return &summary, nil
}

// GetAncestors retrieves the IDs of all direct ancestors for a given decision record.
func (s *SQLiteStore) GetAncestors(drID string) ([]string, error) {
	rows, err := s.DB.Query("SELECT source_id FROM edges WHERE target_id = ?", drID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ancestorIDs []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ancestorIDs = append(ancestorIDs, id)
	}

	return ancestorIDs, nil
}
