package bidoutcomes

// repo.go holds the parameterized SQL the service runs under RunInTenantTx (RLS scoped). The statements are
// raw and parameterized rather than sqlc generated so the M7 ledger is self contained and does not require
// regenerating the dbgen package for one new table; this mirrors the AI graph layer, which authors its
// reviewed read SQL as static constants. Every statement runs as the NOBYPASSRLS app role inside a tenant
// transaction, so the organization_id is the session GUC and a row in another org is never visible or
// writable (the RLS policy plus the composite FKs both hold). The app role has SELECT, INSERT, UPDATE on
// bid_outcomes and no DELETE, so the ledger is never destroyed.

// insertOrUpsertOutcomeSQL records a new bid outcome, or upserts an existing one keyed on its id. The
// organization_id is taken from the session GUC (never a caller argument), so RLS WITH CHECK confirms the
// row belongs to the caller; the ON CONFLICT update only ever touches a row already visible to this tenant.
const insertOrUpsertOutcomeSQL = `
	INSERT INTO bid_outcomes
	    (id, organization_id, opportunity_id, requirement_id, slate_id, submitted_at, result, award_date, awardee_uei, notes)
	VALUES
	    (COALESCE($1, gen_random_uuid()), NULLIF(current_setting('app.current_org_id', true), '')::uuid, $2, $3, $4, $5, $6, $7, $8, $9)
	ON CONFLICT (id) DO UPDATE SET
	    opportunity_id = EXCLUDED.opportunity_id,
	    requirement_id = EXCLUDED.requirement_id,
	    slate_id       = EXCLUDED.slate_id,
	    submitted_at   = EXCLUDED.submitted_at,
	    result         = EXCLUDED.result,
	    award_date     = EXCLUDED.award_date,
	    awardee_uei    = EXCLUDED.awardee_uei,
	    notes          = EXCLUDED.notes,
	    updated_at     = now()
	RETURNING id, opportunity_id, requirement_id, slate_id, submitted_at, result, award_date, awardee_uei, notes, created_at, updated_at`

// updateOutcomeSQL updates the result and award fields of an existing outcome. COALESCE keeps the existing
// submitted_at and award_date when the bind is null, so a partial update never wipes a known timestamp. The
// UPDATE is RLS scoped, so a row in another org is invisible and matches zero rows (the not found path).
const updateOutcomeSQL = `
	UPDATE bid_outcomes SET
	    result       = $2,
	    submitted_at = COALESCE($3, submitted_at),
	    award_date   = COALESCE($4, award_date),
	    awardee_uei  = $5,
	    notes        = $6,
	    updated_at   = now()
	WHERE id = $1
	RETURNING id, opportunity_id, requirement_id, slate_id, submitted_at, result, award_date, awardee_uei, notes, created_at, updated_at`