package callmemory

import (
	"context"

	"github.com/jackc/pgx/v5"
)

// Story references and direct-call references must resolve to the same source
// graph. A direct source deletion invalidates every story that quotes it, but
// must not erase unrelated utterances that happen to appear in those stories.
const storySourceCleanupCTE = `WITH refs AS (
 SELECT r FROM memory_source_suppression s JOIN curated_memory cm ON cm.id=s.memory_id
 CROSS JOIN LATERAL jsonb_array_elements(s.source_refs) r
 WHERE cm.subject_id=$1::uuid AND s.subject_id=cm.subject_id AND s.scope='SOURCE_AND_DERIVED'
 AND cm.public_id IN(SELECT value::uuid FROM jsonb_array_elements_text($2::jsonb))
), named_stories AS (
 SELECT DISTINCT sd.id FROM story_draft sd JOIN refs ON sd.public_id::text=r->>'story_id'
 WHERE sd.subject_id=$1::uuid
), call_sources AS (
 SELECT c.id AS session_id,r->>'utterance_id' AS utterance_id FROM call_session c
 JOIN refs ON c.id::text=r->>'session_id'
 WHERE c.subject_id=$1::uuid
 UNION
 SELECT ss.call_session_id,ss.call_utterance_id FROM story_source ss
 JOIN story_revision sr ON sr.id=ss.story_revision_id JOIN named_stories ns ON ns.id=sr.story_id
 WHERE ss.call_session_id IS NOT NULL
), import_sources AS (
 SELECT ss.import_turn_id AS turn_id FROM story_source ss
 JOIN story_revision sr ON sr.id=ss.story_revision_id JOIN named_stories ns ON ns.id=sr.story_id
 WHERE ss.import_turn_id IS NOT NULL
 UNION
 SELECT it.id FROM memoir_import_turn it JOIN memoir_chat_import ci ON ci.id=it.import_id
 JOIN refs ON ci.id::text=r->>'import_id' AND (r->>'turn_id' IS NULL OR it.id::text=r->>'turn_id')
 WHERE ci.subject_id=$1::uuid
), stories AS (
 SELECT id FROM named_stories
 UNION
 SELECT sr.story_id FROM story_source ss JOIN story_revision sr ON sr.id=ss.story_revision_id
 JOIN story_draft sd ON sd.id=sr.story_id
 JOIN call_sources cs ON cs.session_id=ss.call_session_id AND (cs.utterance_id IS NULL OR cs.utterance_id=ss.call_utterance_id)
 WHERE sd.subject_id=$1::uuid
 UNION
 SELECT sr.story_id FROM story_source ss JOIN story_revision sr ON sr.id=ss.story_revision_id
 JOIN story_draft sd ON sd.id=sr.story_id JOIN import_sources i ON i.turn_id=ss.import_turn_id
 WHERE sd.subject_id=$1::uuid
) `

func (s *Service) cleanStorySources(ctx context.Context, tx pgx.Tx, subject string, targets []byte) error {
	statements := []string{
		`UPDATE call_utterance u SET text_content='[deleted]' FROM call_sources cs WHERE u.session_id=cs.session_id AND (cs.utterance_id IS NULL OR u.utterance_id=cs.utterance_id)`,
		`UPDATE call_text_fragment f SET text_content='[deleted]' FROM call_sources cs WHERE f.session_id=cs.session_id AND (cs.utterance_id IS NULL OR f.utterance_id=cs.utterance_id)`,
		`UPDATE call_event ev SET payload='{"redacted":true}'::jsonb FROM call_sources cs WHERE ev.session_id=cs.session_id AND (cs.utterance_id IS NULL OR ev.payload->'payload'->>'utterance_id'=cs.utterance_id)`,
		`UPDATE conversation_turn ct SET final_text='[deleted]' WHERE ct.id IN(SELECT ss.conversation_turn_id FROM story_source ss JOIN story_revision sr ON sr.id=ss.story_revision_id JOIN named_stories ns ON ns.id=sr.story_id)`,
		`UPDATE memoir_import_turn SET content='[deleted]' WHERE id IN(SELECT turn_id FROM import_sources)`,
		`UPDATE story_revision SET content='[deleted]',edit_reason=NULL WHERE story_id IN(SELECT id FROM stories)`,
		`UPDATE fact_candidate_revision fcr SET content='[deleted]',edit_reason=NULL FROM fact_candidate fc WHERE fcr.fact_candidate_id=fc.id AND fc.story_id IN(SELECT id FROM stories)`,
		`UPDATE fact_candidate SET content='[deleted]',status='SUPERSEDED',review_reason=NULL WHERE story_id IN(SELECT id FROM stories)`,
		`UPDATE story_draft SET title='[deleted]',status='SUPERSEDED',review_reason='source deleted' WHERE id IN(SELECT id FROM stories)`,
	}
	for _, statement := range statements {
		if _, err := tx.Exec(ctx, storySourceCleanupCTE+statement, subject, targets); err != nil {
			return err
		}
	}
	return nil
}