package database

import (
	"context"
	"vegan-barcode/internal/models"
)

func (database *Database) FindClaimsByProductID(product_id int) (claims []models.Claim, err error) {
	ctx := context.Background()
	err = database.db.Query(ctx, claims, `SELECT
    cluster,
    id,
    worker_type,
    evidence_type,
    evidence,
    category,
    polarity,
    created_at,
    created_by
FROM (
    SELECT
        cluster,
        id,
        worker_type,
        evidence_type,
        evidence,
        category,
        polarity,
        created_at,
        created_by,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC) AS rn
    FROM (
        (
            SELECT "user" as cluster, id, product_id, null as worker_type, evidence_type, evidence, unnest(claim) as category, true as polarity, created_at, created_by FROM user_claims
            UNION ALL
            SELECT "automated" as cluster, id, product_id, worker_type, null as evidence_type, evidence, unnest(claim) as category, true as polarity, created_at, null as created_by FROM automated_claims
        )
        UNION ALL
        (
            SELECT "user" as cluster, id, product_id, null as worker_type, evidence_type, evidence, unnest(counter_claim) as category, false as polarity, created_at, created_by FROM user_claims
            UNION ALL
            SELECT "automated" as cluster, id, product_id, worker_type, null as evidence_type, evidence, unnest(counter_claim) as category, false as polarity, created_at, null as created_by FROM automated_claims
        )
    )
    WHERE product_id = ?
)
WHERE rn = 1
ORDER BY created_at;
`, product_id)
	if err != nil {
		return claims, err
	}
	return
}