CREATE TABLE interaction.complaints (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  interaction_id uuid NOT NULL REFERENCES interaction.tracked_interactions(id) ON DELETE RESTRICT,
  customer_identity_id uuid NOT NULL REFERENCES account.identities(id) ON DELETE RESTRICT,
  provider_id uuid NOT NULL REFERENCES provider.organizations(id) ON DELETE RESTRICT,
  category_id uuid NOT NULL REFERENCES catalog.service_categories(id) ON DELETE RESTRICT,
  complaint_type text NOT NULL,
  summary text NOT NULL,
  status text NOT NULL DEFAULT 'submitted',
  revision integer NOT NULL DEFAULT 1,
  policy_version text NOT NULL,
  idempotency_key_hash character(64) NOT NULL,
  request_fingerprint character(64) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  terminal_at timestamptz,
  CONSTRAINT complaints_type_allowed CHECK (complaint_type IN ('service_quality', 'contact_privacy', 'provider_conduct', 'other')),
  CONSTRAINT complaints_summary_length CHECK (length(btrim(summary)) BETWEEN 20 AND 1000),
  CONSTRAINT complaints_status_allowed CHECK (status IN ('submitted', 'triaged', 'resolved', 'closed')),
  CONSTRAINT complaints_revision_positive CHECK (revision >= 1),
  CONSTRAINT complaints_policy_not_blank CHECK (length(btrim(policy_version)) BETWEEN 3 AND 80),
  CONSTRAINT complaints_key_hash_format CHECK (idempotency_key_hash ~ '^[0-9a-f]{64}$'),
  CONSTRAINT complaints_fingerprint_format CHECK (request_fingerprint ~ '^[0-9a-f]{64}$'),
  CONSTRAINT complaints_terminal_consistent CHECK (
    (status IN ('resolved', 'closed') AND terminal_at IS NOT NULL)
    OR (status IN ('submitted', 'triaged') AND terminal_at IS NULL)
  ),
  UNIQUE (customer_identity_id, idempotency_key_hash)
);

CREATE INDEX complaints_customer_idx ON interaction.complaints (customer_identity_id, created_at DESC);
CREATE INDEX complaints_operations_idx ON interaction.complaints (status, updated_at DESC);

CREATE TABLE interaction.complaint_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  complaint_id uuid NOT NULL REFERENCES interaction.complaints(id) ON DELETE RESTRICT,
  sequence integer NOT NULL,
  from_status text,
  to_status text NOT NULL,
  actor_kind text NOT NULL,
  actor_identity_id uuid NOT NULL REFERENCES account.identities(id) ON DELETE RESTRICT,
  reason text NOT NULL,
  policy_version text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT complaint_events_actor_allowed CHECK (actor_kind IN ('customer', 'operations')),
  CONSTRAINT complaint_events_reason_length CHECK (length(btrim(reason)) BETWEEN 12 AND 1000),
  UNIQUE (complaint_id, sequence)
);

CREATE TRIGGER complaint_events_immutable
BEFORE UPDATE OR DELETE ON interaction.complaint_events
FOR EACH ROW EXECUTE FUNCTION interaction.prevent_history_mutation();

CREATE FUNCTION interaction.validate_review_insert()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE tracked interaction.tracked_interactions;
BEGIN
  SELECT * INTO tracked FROM interaction.tracked_interactions WHERE id = NEW.interaction_id FOR UPDATE;
  IF tracked.id IS NULL OR tracked.customer_identity_id <> NEW.customer_identity_id
     OR tracked.provider_id <> NEW.provider_id OR tracked.publication_id <> NEW.publication_id
     OR tracked.category_id <> NEW.category_id OR tracked.status <> 'completed'
     OR tracked.review_eligible_from > now() OR tracked.review_eligible_until <= now() THEN
    RAISE EXCEPTION 'Review requires an owned qualifying tracked interaction';
  END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER reviews_validate_insert BEFORE INSERT ON interaction.reviews
FOR EACH ROW EXECUTE FUNCTION interaction.validate_review_insert();

CREATE FUNCTION interaction.record_review_created()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM interaction.append_interaction_event(
    NEW.interaction_id, 'review_submitted', 'customer', NEW.customer_identity_id,
    'Customer submitted one bounded review for the completed tracked interaction.',
    NEW.policy_version,
    jsonb_build_object('reviewId', NEW.id, 'rating', NEW.rating)
  );
  RETURN NEW;
END;
$$;
CREATE TRIGGER reviews_record_created
AFTER INSERT ON interaction.reviews
FOR EACH ROW EXECUTE FUNCTION interaction.record_review_created();

CREATE FUNCTION interaction.validate_provider_review_response()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE review interaction.reviews; authorized boolean;
BEGIN
  SELECT * INTO review FROM interaction.reviews WHERE id = NEW.review_id;
  IF review.id IS NULL OR review.provider_id <> NEW.provider_id THEN
    RAISE EXCEPTION 'Provider response must match the review provider scope';
  END IF;
  SELECT EXISTS (
    SELECT 1 FROM authz.role_assignments AS assignments
    JOIN authz.roles AS roles ON roles.id = assignments.role_id
    WHERE assignments.identity_id = NEW.responder_identity_id
      AND assignments.scope_type = 'provider'
      AND assignments.provider_id = NEW.provider_id
      AND assignments.revoked_at IS NULL
      AND assignments.starts_at <= now()
      AND (assignments.ends_at IS NULL OR assignments.ends_at > now())
      AND roles.role_key IN ('provider_owner', 'provider_member', 'provider_responder')
  ) INTO authorized;
  IF NOT authorized THEN RAISE EXCEPTION 'Provider response actor is not authorized'; END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER provider_review_responses_validate
BEFORE INSERT ON interaction.provider_review_responses
FOR EACH ROW EXECUTE FUNCTION interaction.validate_provider_review_response();

CREATE FUNCTION interaction.record_provider_review_response()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE interaction_uuid uuid;
BEGIN
  SELECT interaction_id INTO interaction_uuid FROM interaction.reviews WHERE id = NEW.review_id;
  PERFORM interaction.append_interaction_event(
    interaction_uuid, 'provider_response_submitted', 'provider', NEW.responder_identity_id,
    'Provider submitted one bounded response to the tracked-interaction review.',
    NEW.policy_version,
    jsonb_build_object('reviewId', NEW.review_id, 'responseId', NEW.id)
  );
  RETURN NEW;
END;
$$;
CREATE TRIGGER provider_review_responses_record
AFTER INSERT ON interaction.provider_review_responses
FOR EACH ROW EXECUTE FUNCTION interaction.record_provider_review_response();

CREATE FUNCTION interaction.validate_review_appeal_insert()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE review interaction.reviews; provider_authorized boolean;
BEGIN
  SELECT * INTO review FROM interaction.reviews WHERE id = NEW.review_id FOR UPDATE;
  IF review.id IS NULL OR review.moderation_status NOT IN ('withheld', 'removed') THEN
    RAISE EXCEPTION 'Only withheld or removed reviews can be appealed';
  END IF;
  IF NEW.appellant_scope = 'customer' THEN
    IF NEW.appellant_identity_id <> review.customer_identity_id OR NEW.provider_id IS NOT NULL THEN
      RAISE EXCEPTION 'Customer appeal scope does not match the review';
    END IF;
  ELSE
    IF NEW.provider_id IS DISTINCT FROM review.provider_id THEN
      RAISE EXCEPTION 'Provider appeal scope does not match the review';
    END IF;
    SELECT EXISTS (
      SELECT 1 FROM authz.role_assignments AS assignments
      JOIN authz.roles AS roles ON roles.id = assignments.role_id
      WHERE assignments.identity_id = NEW.appellant_identity_id
        AND assignments.provider_id = review.provider_id
        AND assignments.scope_type = 'provider'
        AND assignments.revoked_at IS NULL
        AND assignments.starts_at <= now()
        AND (assignments.ends_at IS NULL OR assignments.ends_at > now())
        AND roles.role_key IN ('provider_owner', 'provider_member', 'provider_responder')
    ) INTO provider_authorized;
    IF NOT provider_authorized THEN RAISE EXCEPTION 'Provider appeal actor is not authorized'; END IF;
  END IF;
  PERFORM set_config('direkt.review_moderation', 'on', true);
  UPDATE interaction.reviews
  SET moderation_status = 'appealed', revision = revision + 1, updated_at = now(),
      published_at = NULL, withheld_at = NULL, removed_at = NULL
  WHERE id = review.id;
  RETURN NEW;
END;
$$;
CREATE TRIGGER review_appeals_validate_insert
BEFORE INSERT ON interaction.review_appeals
FOR EACH ROW EXECUTE FUNCTION interaction.validate_review_appeal_insert();

CREATE FUNCTION interaction.record_review_appeal()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE interaction_uuid uuid;
BEGIN
  SELECT interaction_id INTO interaction_uuid FROM interaction.reviews WHERE id = NEW.review_id;
  PERFORM interaction.append_interaction_event(
    interaction_uuid, 'appeal_submitted', NEW.appellant_scope, NEW.appellant_identity_id,
    NEW.reason, NEW.policy_version,
    jsonb_build_object('appealId', NEW.id, 'reviewId', NEW.review_id, 'appellantScope', NEW.appellant_scope)
  );
  RETURN NEW;
END;
$$;
CREATE TRIGGER review_appeals_record
AFTER INSERT ON interaction.review_appeals
FOR EACH ROW EXECUTE FUNCTION interaction.record_review_appeal();

CREATE FUNCTION interaction.record_complaint_created()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO interaction.complaint_events (
    complaint_id, sequence, from_status, to_status, actor_kind, actor_identity_id, reason, policy_version
  ) VALUES (
    NEW.id, 1, NULL, NEW.status, 'customer', NEW.customer_identity_id,
    'Customer submitted a complaint linked to an owned tracked interaction.', NEW.policy_version
  );
  PERFORM interaction.append_interaction_event(
    NEW.interaction_id, 'complaint_submitted', 'customer', NEW.customer_identity_id,
    'Customer submitted a complaint linked to the tracked interaction.',
    NEW.policy_version,
    jsonb_build_object('complaintId', NEW.id, 'complaintType', NEW.complaint_type)
  );
  PERFORM interaction.append_interaction_event(
    NEW.interaction_id, 'complaint_linked', 'system', NULL,
    'System linked the customer complaint to the tracked interaction without exposing Phase 7 incident data.',
    NEW.policy_version,
    jsonb_build_object('complaintId', NEW.id, 'phase7IncidentLinked', false)
  );
  RETURN NEW;
END;
$$;
CREATE TRIGGER complaints_record_created
AFTER INSERT ON interaction.complaints
FOR EACH ROW EXECUTE FUNCTION interaction.record_complaint_created();

CREATE FUNCTION interaction.moderate_review(
  review_uuid uuid, operator_identity_uuid uuid, target_status text,
  moderation_reason_code text, moderation_reason text, moderation_policy_version text,
  expected_revision integer
)
RETURNS interaction.reviews LANGUAGE plpgsql AS $$
DECLARE current_review interaction.reviews; previous_status text; next_sequence integer;
BEGIN
  SELECT * INTO current_review FROM interaction.reviews WHERE id = review_uuid FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Review not found'; END IF;
  IF current_review.revision <> expected_revision THEN RAISE EXCEPTION 'Review revision conflict'; END IF;
  IF target_status NOT IN ('published', 'withheld', 'removed') THEN RAISE EXCEPTION 'Review moderation target is not allowed'; END IF;
  IF NOT (
    (current_review.moderation_status IN ('pending', 'appealed') AND target_status IN ('published', 'withheld', 'removed'))
    OR (current_review.moderation_status = 'published' AND target_status IN ('withheld', 'removed'))
    OR (current_review.moderation_status = 'withheld' AND target_status IN ('published', 'removed'))
  ) THEN RAISE EXCEPTION 'Review moderation transition is not allowed'; END IF;
  previous_status := current_review.moderation_status;
  SELECT COALESCE(max(sequence), 0) + 1 INTO next_sequence
  FROM interaction.review_moderation_events WHERE review_id = review_uuid;
  PERFORM set_config('direkt.review_moderation', 'on', true);
  UPDATE interaction.reviews
  SET moderation_status = target_status,
      revision = revision + 1,
      updated_at = now(),
      published_at = CASE WHEN target_status = 'published' THEN now() ELSE NULL END,
      withheld_at = CASE WHEN target_status = 'withheld' THEN now() ELSE NULL END,
      removed_at = CASE WHEN target_status = 'removed' THEN now() ELSE NULL END
  WHERE id = review_uuid RETURNING * INTO current_review;
  INSERT INTO interaction.review_moderation_events (
    review_id, sequence, from_status, to_status, operator_identity_id, reason_code, reason, policy_version
  ) VALUES (
    review_uuid, next_sequence, previous_status, target_status, operator_identity_uuid,
    moderation_reason_code, moderation_reason, moderation_policy_version
  );
  PERFORM interaction.append_interaction_event(
    current_review.interaction_id, 'review_moderated', 'operations', operator_identity_uuid,
    moderation_reason, moderation_policy_version,
    jsonb_build_object('fromStatus', previous_status, 'toStatus', target_status, 'reasonCode', moderation_reason_code)
  );
  RETURN current_review;
END;
$$;

CREATE FUNCTION interaction.decide_review_appeal(
  appeal_uuid uuid, operator_identity_uuid uuid, decision_status text,
  decision_code text, decision_reason_text text, decision_policy_version text
)
RETURNS interaction.review_appeals LANGUAGE plpgsql AS $$
DECLARE appeal interaction.review_appeals; review interaction.reviews;
BEGIN
  SELECT * INTO appeal FROM interaction.review_appeals WHERE id = appeal_uuid FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Appeal not found'; END IF;
  IF appeal.status <> 'submitted' THEN RAISE EXCEPTION 'Appeal is already terminal'; END IF;
  IF decision_status NOT IN ('upheld', 'denied') THEN RAISE EXCEPTION 'Appeal decision is not allowed'; END IF;
  SELECT * INTO review FROM interaction.reviews WHERE id = appeal.review_id FOR UPDATE;
  PERFORM set_config('direkt.appeal_decision', 'on', true);
  UPDATE interaction.review_appeals SET status = decision_status, decided_at = now(),
    decided_by_identity_id = operator_identity_uuid, decision_reason_code = decision_code,
    decision_reason = decision_reason_text
  WHERE id = appeal_uuid RETURNING * INTO appeal;
  IF decision_status = 'upheld' THEN
    PERFORM set_config('direkt.review_moderation', 'on', true);
    UPDATE interaction.reviews SET moderation_status = 'pending', revision = revision + 1,
      published_at = NULL, withheld_at = NULL, removed_at = NULL, updated_at = now()
    WHERE id = review.id;
  END IF;
  PERFORM interaction.append_interaction_event(
    review.interaction_id, 'appeal_decided', 'operations', operator_identity_uuid,
    decision_reason_text, decision_policy_version,
    jsonb_build_object('appealId', appeal.id, 'decision', decision_status, 'reasonCode', decision_code)
  );
  RETURN appeal;
END;
$$;

CREATE FUNCTION interaction.transition_complaint(
  complaint_uuid uuid, operator_identity_uuid uuid, target_status text,
  expected_revision integer, transition_reason text, transition_policy_version text
)
RETURNS interaction.complaints LANGUAGE plpgsql AS $$
DECLARE complaint interaction.complaints; previous_status text; next_sequence integer;
BEGIN
  SELECT * INTO complaint FROM interaction.complaints WHERE id = complaint_uuid FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Complaint not found'; END IF;
  IF complaint.revision <> expected_revision THEN RAISE EXCEPTION 'Complaint revision conflict'; END IF;
  IF NOT (
    (complaint.status = 'submitted' AND target_status IN ('triaged', 'resolved'))
    OR (complaint.status = 'triaged' AND target_status IN ('resolved', 'closed'))
    OR (complaint.status = 'resolved' AND target_status = 'closed')
  ) THEN RAISE EXCEPTION 'Complaint transition is not allowed'; END IF;
  previous_status := complaint.status;
  SELECT COALESCE(max(sequence), 0) + 1 INTO next_sequence FROM interaction.complaint_events WHERE complaint_id = complaint_uuid;
  PERFORM set_config('direkt.complaint_transition', 'on', true);
  UPDATE interaction.complaints SET status = target_status, revision = revision + 1, updated_at = now(),
    terminal_at = CASE WHEN target_status IN ('resolved', 'closed') THEN COALESCE(terminal_at, now()) ELSE NULL END
  WHERE id = complaint_uuid RETURNING * INTO complaint;
  INSERT INTO interaction.complaint_events (
    complaint_id, sequence, from_status, to_status, actor_kind, actor_identity_id, reason, policy_version
  ) VALUES (complaint_uuid, next_sequence, previous_status, target_status, 'operations', operator_identity_uuid,
    transition_reason, transition_policy_version);
  RETURN complaint;
END;
$$;
