package main

import (
	"database/sql"
	"time"

	"github.com/google/uuid"
)

type DodItem struct {
	ID        string `json:"id"`
	RunID     string `json:"run_id"`
	ItemKey   string `json:"item_key"`
	Kind      string `json:"kind"`
	Expected  string `json:"expected"`
	Required  bool   `json:"required"`
	Status    string `json:"status"`
	CreatedAt int64  `json:"created_at"`
}

type DodEvidence struct {
	ID          string  `json:"id"`
	DodItemID   string  `json:"dod_item_id"`
	Kind        string  `json:"kind"`
	PayloadPath *string `json:"payload_path,omitempty"`
	PayloadURL  *string `json:"payload_url,omitempty"`
	PayloadText *string `json:"payload_text,omitempty"`
	AttachedAt  int64   `json:"attached_at"`
	ValidatedAt *int64  `json:"validated_at,omitempty"`
	ValidatedBy *string `json:"validated_by,omitempty"`
}

func createDodItem(db *sql.DB, runID, key, kind, expected string, required bool) (DodItem, error) {
	id := "dod_" + uuid.New().String()[:12]
	now := time.Now().Unix()
	reqInt := 0
	if required {
		reqInt = 1
	}
	_, err := db.Exec(`INSERT INTO dod_items
		(id, run_id, item_key, kind, expected, required, status, created_at)
		VALUES (?, ?, ?, ?, ?, ?, 'pending', ?)`,
		id, runID, key, kind, expected, reqInt, now)
	if err != nil {
		return DodItem{}, err
	}
	return DodItem{
		ID: id, RunID: runID, ItemKey: key, Kind: kind, Expected: expected,
		Required: required, Status: "pending", CreatedAt: now,
	}, nil
}

func listDodItems(db *sql.DB, runID string) ([]DodItem, error) {
	rows, err := db.Query(`SELECT id, run_id, item_key, kind, expected, required, status, created_at
		FROM dod_items WHERE run_id = ? ORDER BY created_at`, runID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []DodItem{}
	for rows.Next() {
		var it DodItem
		var reqInt int
		if err := rows.Scan(&it.ID, &it.RunID, &it.ItemKey, &it.Kind, &it.Expected, &reqInt, &it.Status, &it.CreatedAt); err != nil {
			return nil, err
		}
		it.Required = reqInt != 0
		out = append(out, it)
	}
	return out, nil
}

func attachEvidence(db *sql.DB, itemID, kind string, path, url, text *string) (DodEvidence, error) {
	id := "ev_" + uuid.New().String()[:12]
	now := time.Now().Unix()
	_, err := db.Exec(`INSERT INTO dod_evidence
		(id, dod_item_id, kind, payload_path, payload_url, payload_text, attached_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		id, itemID, kind, nullStr(path), nullStr(url), nullStr(text), now)
	if err != nil {
		return DodEvidence{}, err
	}
	// Auto-bump item status to 'done' on first evidence
	_, _ = db.Exec(`UPDATE dod_items SET status = 'done' WHERE id = ? AND status = 'pending'`, itemID)
	return DodEvidence{
		ID: id, DodItemID: itemID, Kind: kind,
		PayloadPath: path, PayloadURL: url, PayloadText: text,
		AttachedAt: now,
	}, nil
}

func validateEvidence(db *sql.DB, evID, validatedBy string) error {
	now := time.Now().Unix()
	res, err := db.Exec(`UPDATE dod_evidence
		SET validated_at = ?, validated_by = ? WHERE id = ?`, now, validatedBy, evID)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	// Bump item status to validated
	_, _ = db.Exec(`UPDATE dod_items SET status = 'validated'
		WHERE id = (SELECT dod_item_id FROM dod_evidence WHERE id = ?)`, evID)
	return nil
}

func listEvidence(db *sql.DB, itemID string) ([]DodEvidence, error) {
	rows, err := db.Query(`SELECT id, dod_item_id, kind, payload_path, payload_url, payload_text,
		attached_at, validated_at, validated_by
		FROM dod_evidence WHERE dod_item_id = ? ORDER BY attached_at`, itemID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []DodEvidence{}
	for rows.Next() {
		var ev DodEvidence
		var path, url, text, valBy sql.NullString
		var valAt sql.NullInt64
		if err := rows.Scan(&ev.ID, &ev.DodItemID, &ev.Kind, &path, &url, &text,
			&ev.AttachedAt, &valAt, &valBy); err != nil {
			return nil, err
		}
		if path.Valid {
			s := path.String
			ev.PayloadPath = &s
		}
		if url.Valid {
			s := url.String
			ev.PayloadURL = &s
		}
		if text.Valid {
			s := text.String
			ev.PayloadText = &s
		}
		if valAt.Valid {
			v := valAt.Int64
			ev.ValidatedAt = &v
		}
		if valBy.Valid {
			s := valBy.String
			ev.ValidatedBy = &s
		}
		out = append(out, ev)
	}
	return out, nil
}

// dodGateOpen returns true when every required item has at least one validated evidence.
func dodGateOpen(db *sql.DB, runID string) (bool, error) {
	row := db.QueryRow(`
		SELECT COUNT(*) FROM dod_items
		WHERE run_id = ? AND required = 1
		  AND status != 'validated'`, runID)
	var n int
	if err := row.Scan(&n); err != nil {
		return false, err
	}
	return n == 0, nil
}

func nullStr(p *string) interface{} {
	if p == nil {
		return nil
	}
	return *p
}