CREATE TABLE disputes (
 id text PRIMARY KEY, reference text NOT NULL UNIQUE, merchant_id text NOT NULL REFERENCES merchants(id), payment_id text NOT NULL REFERENCES payments(id), transaction_id text,
 external_reference text, source text NOT NULL CHECK(source IN ('SANDBOX','MERCHANT_REPORTED','PLATFORM_REPORTED','PROVIDER_REPORTED')),
 reason_category text NOT NULL CHECK(reason_category IN ('FRAUDULENT','DUPLICATE','PRODUCT_NOT_RECEIVED','SERVICE_NOT_PROVIDED','NOT_AS_DESCRIBED','CREDIT_NOT_PROCESSED','INCORRECT_AMOUNT','PROCESSING_ERROR','OTHER')),
 amount_minor bigint NOT NULL CHECK(amount_minor>0), currency char(3) NOT NULL CHECK(currency~'^[A-Z]{3}$'), summary text NOT NULL CHECK(length(trim(summary)) BETWEEN 3 AND 1000),
 status text NOT NULL DEFAULT 'OPEN' CHECK(status IN ('OPEN','NEEDS_MERCHANT_RESPONSE','MERCHANT_RESPONDED','UNDER_REVIEW','ACCEPTED','CONTESTED','WON','LOST','CLOSED')),
 response_deadline timestamptz, assigned_to text REFERENCES users(id), created_by text NOT NULL REFERENCES users(id), version integer NOT NULL DEFAULT 1 CHECK(version>0),
 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE(merchant_id,id), UNIQUE(external_reference)
);
CREATE INDEX disputes_merchant_page_idx ON disputes(merchant_id,created_at DESC,id DESC);
CREATE INDEX disputes_platform_page_idx ON disputes(status,assigned_to,created_at DESC,id DESC);
CREATE INDEX disputes_payment_idx ON disputes(payment_id);

CREATE TABLE dispute_public_responses(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),author_id text NOT NULL,author_domain text NOT NULL CHECK(author_domain IN ('MERCHANT','PLATFORM')),body text NOT NULL CHECK(length(trim(body)) BETWEEN 1 AND 10000),created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX dispute_responses_page_idx ON dispute_public_responses(dispute_id,created_at,id);
CREATE TABLE dispute_evidence(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),submitted_by text NOT NULL,submitter_domain text NOT NULL CHECK(submitter_domain IN ('MERCHANT','PLATFORM')),evidence_type text NOT NULL CHECK(length(trim(evidence_type)) BETWEEN 2 AND 80),description text NOT NULL CHECK(length(trim(description)) BETWEEN 1 AND 1000),original_filename text NOT NULL CHECK(length(original_filename) BETWEEN 1 AND 255 AND original_filename !~ '[\\/\x00-\x1f]'),mime_type text NOT NULL CHECK(mime_type IN ('application/pdf','image/png','image/jpeg','text/plain')),file_size bigint NOT NULL CHECK(file_size BETWEEN 1 AND 10485760),checksum_sha256 char(64) NOT NULL CHECK(checksum_sha256~'^[0-9a-f]{64}$'),storage_mode text NOT NULL DEFAULT 'SANDBOX_METADATA_ONLY' CHECK(storage_mode='SANDBOX_METADATA_ONLY'),created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX dispute_evidence_page_idx ON dispute_evidence(dispute_id,created_at,id);
CREATE TABLE dispute_internal_notes(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),author_id text NOT NULL REFERENCES users(id),body text NOT NULL CHECK(length(trim(body)) BETWEEN 1 AND 10000),created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE dispute_status_history(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),actor_id text NOT NULL REFERENCES users(id),from_status text,to_status text NOT NULL,reason text NOT NULL CHECK(length(trim(reason)) BETWEEN 3 AND 1000),created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE dispute_assignment_history(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),actor_id text NOT NULL REFERENCES users(id),from_staff_id text REFERENCES users(id),to_staff_id text NOT NULL REFERENCES users(id),reason text NOT NULL CHECK(length(trim(reason)) BETWEEN 3 AND 1000),created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE dispute_decisions(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),outcome text NOT NULL CHECK(outcome IN ('ACCEPTED','CONTESTED','WON','LOST')),state text NOT NULL CHECK(state IN ('PROPOSED','APPROVED','REJECTED')),reason text NOT NULL CHECK(length(trim(reason)) BETWEEN 3 AND 1000),evidence_ids text[] NOT NULL DEFAULT '{}',proposed_by text NOT NULL REFERENCES users(id),reviewed_by text REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),reviewed_at timestamptz,CHECK(reviewed_by IS NULL OR reviewed_by<>proposed_by));
CREATE UNIQUE INDEX dispute_one_live_decision_idx ON dispute_decisions(dispute_id) WHERE state IN ('PROPOSED','APPROVED');
CREATE TABLE dispute_decision_history(id text PRIMARY KEY,decision_id text NOT NULL REFERENCES dispute_decisions(id),dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),state text NOT NULL CHECK(state IN ('PROPOSED','APPROVED','REJECTED')),actor_id text NOT NULL REFERENCES users(id),reason text NOT NULL,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE dispute_financial_effects(id text PRIMARY KEY,dispute_id text NOT NULL REFERENCES disputes(id),merchant_id text NOT NULL REFERENCES merchants(id),effect_type text NOT NULL CHECK(effect_type IN ('HOLD','REVERSAL','RELEASE','FINALIZED_LOSS')),status text NOT NULL DEFAULT 'PENDING' CHECK(status='PENDING'),amount_minor bigint NOT NULL CHECK(amount_minor>0),currency char(3) NOT NULL,ledger_entry_id text REFERENCES journal_entries(id),sandbox_only boolean NOT NULL DEFAULT true,created_by text NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(dispute_id,effect_type),CHECK(ledger_entry_id IS NULL));
CREATE TABLE dispute_idempotency(authority_scope text NOT NULL,operation text NOT NULL,idempotency_key text NOT NULL,request_sha256 char(64) NOT NULL,response_status integer NOT NULL,response_body jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(authority_scope,operation,idempotency_key));

CREATE FUNCTION dispute_validate_tenant() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE owner text; BEGIN SELECT merchant_id INTO owner FROM disputes WHERE id=NEW.dispute_id; IF owner IS NULL OR owner<>NEW.merchant_id THEN RAISE EXCEPTION 'dispute tenant mismatch' USING ERRCODE='23514'; END IF; RETURN NEW; END $$;
CREATE TRIGGER dispute_response_tenant BEFORE INSERT ON dispute_public_responses FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_evidence_tenant BEFORE INSERT ON dispute_evidence FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_note_tenant BEFORE INSERT ON dispute_internal_notes FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_status_tenant BEFORE INSERT ON dispute_status_history FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_assignment_tenant BEFORE INSERT ON dispute_assignment_history FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_decision_tenant BEFORE INSERT ON dispute_decisions FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE TRIGGER dispute_effect_tenant BEFORE INSERT ON dispute_financial_effects FOR EACH ROW EXECUTE FUNCTION dispute_validate_tenant();
CREATE FUNCTION dispute_validate_payment() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE p payments%ROWTYPE; BEGIN SELECT * INTO p FROM payments WHERE id=NEW.payment_id; IF p.id IS NULL OR p.merchant_id<>NEW.merchant_id THEN RAISE EXCEPTION 'dispute payment mismatch' USING ERRCODE='23514'; END IF; IF p.currency<>NEW.currency THEN RAISE EXCEPTION 'dispute currency mismatch' USING ERRCODE='23514'; END IF; RETURN NEW; END $$;
CREATE TRIGGER dispute_payment_valid BEFORE INSERT OR UPDATE ON disputes FOR EACH ROW EXECUTE FUNCTION dispute_validate_payment();
CREATE FUNCTION dispute_validate_assignment() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.assigned_to IS NOT NULL AND NOT EXISTS(SELECT 1 FROM users WHERE id=NEW.assigned_to AND merchant_id IS NULL AND role='PLATFORM_ADMIN' AND status='ACTIVE' AND permissions@>ARRAY['platform.disputes.read']) THEN RAISE EXCEPTION 'ineligible dispute assignee' USING ERRCODE='23514'; END IF; RETURN NEW; END $$;
CREATE TRIGGER dispute_assignment_valid BEFORE INSERT OR UPDATE OF assigned_to ON disputes FOR EACH ROW EXECUTE FUNCTION dispute_validate_assignment();
CREATE FUNCTION dispute_validate_transition() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.status=OLD.status THEN RETURN NEW; END IF; IF (OLD.status='OPEN' AND NEW.status IN ('NEEDS_MERCHANT_RESPONSE','UNDER_REVIEW')) OR (OLD.status='NEEDS_MERCHANT_RESPONSE' AND NEW.status='MERCHANT_RESPONDED') OR (OLD.status='MERCHANT_RESPONDED' AND NEW.status IN ('NEEDS_MERCHANT_RESPONSE','UNDER_REVIEW')) OR (OLD.status='UNDER_REVIEW' AND NEW.status IN ('NEEDS_MERCHANT_RESPONSE','ACCEPTED','CONTESTED','WON','LOST')) OR (OLD.status IN ('ACCEPTED','CONTESTED','WON','LOST') AND NEW.status='CLOSED') OR (OLD.status='CLOSED' AND NEW.status='UNDER_REVIEW') THEN RETURN NEW; END IF; RAISE EXCEPTION 'invalid dispute transition' USING ERRCODE='23514'; END $$;
CREATE TRIGGER dispute_status_valid BEFORE UPDATE OF status ON disputes FOR EACH ROW EXECUTE FUNCTION dispute_validate_transition();
CREATE FUNCTION dispute_immutable_fields() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (OLD.merchant_id,OLD.payment_id,OLD.amount_minor,OLD.currency,OLD.source,OLD.external_reference) IS DISTINCT FROM (NEW.merchant_id,NEW.payment_id,NEW.amount_minor,NEW.currency,NEW.source,NEW.external_reference) THEN RAISE EXCEPTION 'dispute ownership and financial terms are immutable' USING ERRCODE='55000'; END IF; RETURN NEW; END $$;
CREATE TRIGGER dispute_terms_immutable BEFORE UPDATE ON disputes FOR EACH ROW EXECUTE FUNCTION dispute_immutable_fields();
CREATE FUNCTION reject_dispute_history_mutation() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'dispute history is append-only' USING ERRCODE='55000'; END $$;
CREATE TRIGGER dispute_responses_append_only BEFORE UPDATE OR DELETE ON dispute_public_responses FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_evidence_append_only BEFORE UPDATE OR DELETE ON dispute_evidence FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_notes_append_only BEFORE UPDATE OR DELETE ON dispute_internal_notes FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_status_append_only BEFORE UPDATE OR DELETE ON dispute_status_history FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_assignments_append_only BEFORE UPDATE OR DELETE ON dispute_assignment_history FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_decision_history_append_only BEFORE UPDATE OR DELETE ON dispute_decision_history FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();
CREATE TRIGGER dispute_effects_append_only BEFORE UPDATE OR DELETE ON dispute_financial_effects FOR EACH STATEMENT EXECUTE FUNCTION reject_dispute_history_mutation();

CREATE FUNCTION record_dispute_decision_history() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO dispute_decision_history(id,decision_id,dispute_id,merchant_id,state,actor_id,reason) VALUES('ddh_'||replace(gen_random_uuid()::text,'-',''),NEW.id,NEW.dispute_id,NEW.merchant_id,NEW.state,CASE WHEN NEW.state='PROPOSED' THEN NEW.proposed_by ELSE NEW.reviewed_by END,NEW.reason); RETURN NEW; END $$;
CREATE TRIGGER dispute_decision_history_recorded AFTER INSERT OR UPDATE OF state ON dispute_decisions FOR EACH ROW EXECUTE FUNCTION record_dispute_decision_history();
CREATE FUNCTION dispute_validate_checker() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE d disputes%ROWTYPE; BEGIN SELECT * INTO d FROM disputes WHERE id=NEW.dispute_id; IF NEW.reviewed_by IS NULL OR NEW.reviewed_by=NEW.proposed_by OR NEW.reviewed_by=d.created_by OR NEW.reviewed_by=d.assigned_to THEN RAISE EXCEPTION 'independent dispute checker required' USING ERRCODE='23514'; END IF; RETURN NEW; END $$;
CREATE TRIGGER dispute_checker_independent BEFORE UPDATE OF state ON dispute_decisions FOR EACH ROW WHEN(NEW.state IN ('APPROVED','REJECTED')) EXECUTE FUNCTION dispute_validate_checker();

UPDATE users SET permissions=(SELECT array_agg(DISTINCT p ORDER BY p) FROM unnest(permissions||ARRAY['platform.disputes.read','platform.disputes.investigate','platform.disputes.request_information','platform.disputes.decide','platform.disputes.assign','platform.disputes.reopen']) p) WHERE merchant_id IS NULL AND role='PLATFORM_ADMIN';
UPDATE merchant_roles SET permissions=(SELECT array_agg(DISTINCT p ORDER BY p) FROM unnest(permissions||ARRAY['disputes.read','disputes.respond','disputes.evidence.create']) p) WHERE normalized_name IN ('owner','administrator','support');
ALTER TABLE api_keys DROP CONSTRAINT api_keys_scopes_check;
ALTER TABLE api_keys ADD CONSTRAINT api_keys_scopes_check CHECK(scopes<@ARRAY['payments:read','disputes.read','disputes.respond','disputes.evidence.create']::text[]);