BEGIN;

-- Keep device calls in their native tables; a story has exactly one origin.
ALTER TABLE call_session ADD CONSTRAINT call_session_story_scope UNIQUE(id,tenant_id,subject_id);
ALTER TABLE story_draft ALTER COLUMN conversation_id DROP NOT NULL;
ALTER TABLE story_draft ADD COLUMN call_session_id uuid;
ALTER TABLE story_draft ADD COLUMN call_privacy_epoch bigint;
ALTER TABLE story_draft ADD CONSTRAINT story_call_scope_fk
 FOREIGN KEY(call_session_id,tenant_id,subject_id) REFERENCES call_session(id,tenant_id,subject_id);
ALTER TABLE story_draft ADD CONSTRAINT story_origin_check CHECK (
 (conversation_id IS NOT NULL AND call_session_id IS NULL AND call_privacy_epoch IS NULL) OR
 (conversation_id IS NULL AND call_session_id IS NOT NULL AND call_privacy_epoch IS NOT NULL AND call_privacy_epoch>=0));
ALTER TABLE story_draft ADD CONSTRAINT story_call_generation_key UNIQUE(call_session_id,generation_version);

ALTER TABLE story_source ALTER COLUMN conversation_turn_id DROP NOT NULL;
ALTER TABLE story_source ADD COLUMN call_session_id uuid;
ALTER TABLE story_source ADD COLUMN call_utterance_id text;
ALTER TABLE story_source ADD COLUMN call_source_revision bigint;
ALTER TABLE story_source ADD CONSTRAINT story_call_utterance_fk
 FOREIGN KEY(call_session_id,call_utterance_id) REFERENCES call_utterance(session_id,utterance_id);
ALTER TABLE story_source ADD CONSTRAINT story_source_origin_check CHECK (
 (conversation_turn_id IS NOT NULL AND call_session_id IS NULL AND call_utterance_id IS NULL AND call_source_revision IS NULL) OR
 (conversation_turn_id IS NULL AND call_session_id IS NOT NULL AND call_utterance_id IS NOT NULL AND call_source_revision IS NOT NULL AND call_source_revision>=0 AND source_role='subject'));
ALTER TABLE story_source ADD CONSTRAINT story_call_source_key
 UNIQUE(story_revision_id,call_session_id,call_utterance_id,source_start_offset,source_end_offset);
CREATE INDEX story_source_call_idx ON story_source(call_session_id,call_utterance_id) WHERE call_session_id IS NOT NULL;

CREATE TABLE call_story_extraction (
 session_id uuid NOT NULL REFERENCES call_session(id),
 transcript_revision bigint NOT NULL CHECK(transcript_revision>0),
 privacy_epoch bigint NOT NULL CHECK(privacy_epoch>=0),
 outcome text NOT NULL CHECK(outcome IN ('STORY','NO_STORY','EMPTY','REVIEWED')),
 model_ref text NOT NULL DEFAULT '', prompt_version text NOT NULL DEFAULT '',
 created_at timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(session_id,transcript_revision,privacy_epoch)
);

-- Authorization is frozen at call start and must still be valid at processing
-- and review time. A later grant must not authorize old calls retroactively.
CREATE FUNCTION call_story_authorized(sid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
 SELECT EXISTS(SELECT 1 FROM call_session c JOIN subject_profile sp ON sp.id=c.subject_id AND sp.tenant_id=c.tenant_id
 JOIN memory_user_state us ON us.subject_id=c.subject_id
 WHERE c.id=sid AND sp.status='active' AND c.privacy_epoch=us.privacy_epoch
 AND c.budget_snapshot->>'protocol'='DIRECT-CHAT-1'
 AND c.budget_snapshot->>'story_extraction_allowed'='true'
 AND NOT EXISTS(SELECT 1 FROM privacy_request pr WHERE pr.subject_id=c.subject_id AND pr.request_type='DELETE')
 AND NOT EXISTS(SELECT 1 FROM (VALUES ('transcript_storage'),('story_extraction')) required(kind)
  WHERE NOT EXISTS(SELECT 1 FROM consent_record cr WHERE cr.subject_id=c.subject_id AND cr.tenant_id=c.tenant_id
   AND cr.consent_type=required.kind AND cr.granted_at<=c.created_at AND cr.withdrawn_at IS NULL
   AND (cr.expires_at IS NULL OR cr.expires_at>now()))))
$$;

CREATE FUNCTION call_story_available(story uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
 SELECT EXISTS(SELECT 1 FROM story_draft sd WHERE sd.id=story AND (
 sd.call_session_id IS NULL OR (
 call_story_authorized(sd.call_session_id)
 AND sd.call_privacy_epoch=(SELECT privacy_epoch FROM call_session WHERE id=sd.call_session_id)
 AND EXISTS(SELECT 1 FROM story_source ss WHERE ss.story_revision_id=sd.current_revision_id)
 AND NOT EXISTS(SELECT 1 FROM story_source ss LEFT JOIN call_utterance u
   ON u.session_id=ss.call_session_id AND u.utterance_id=ss.call_utterance_id
   WHERE ss.story_revision_id=sd.current_revision_id AND (
    ss.call_session_id IS DISTINCT FROM sd.call_session_id OR u.session_id IS NULL
    OR u.source_revision IS DISTINCT FROM ss.call_source_revision OR u.speaker<>'USER'
    OR u.text_content='[deleted]' OR length(trim(u.text_content))=0
    OR EXISTS(SELECT 1 FROM memory_source_suppression ms CROSS JOIN LATERAL jsonb_array_elements(ms.source_refs) r
      WHERE ms.subject_id=sd.subject_id AND (r->>'story_id'=sd.public_id::text OR
       (r->>'session_id'=ss.call_session_id::text AND (r->>'utterance_id' IS NULL OR r->>'utterance_id'=ss.call_utterance_id)))))))))
$$;

CREATE FUNCTION call_story_consent_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF NEW.consent_type='story_extraction' AND (NEW.withdrawn_at IS NOT NULL OR NEW.expires_at<=now())
 AND (OLD.withdrawn_at IS DISTINCT FROM NEW.withdrawn_at OR OLD.expires_at IS DISTINCT FROM NEW.expires_at) THEN
  PERFORM call_privacy_fence(NEW.subject_id,'STORY_CONSENT_REVOKED');
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER call_story_consent_changed AFTER UPDATE ON consent_record
 FOR EACH ROW EXECUTE FUNCTION call_story_consent_changed();

COMMIT;