ALTER TABLE transfers
    DROP CONSTRAINT IF EXISTS transfers_status_check;

ALTER TABLE transfers
    ADD CONSTRAINT transfers_status_check
    CHECK (status IN ('pending', 'processing', 'review_held', 'completed', 'failed', 'rejected', 'reversed'));

ALTER TABLE transfers
    ADD COLUMN payment_reason_code text,
    ADD COLUMN payment_reason_category text,
    ADD COLUMN payment_reason_description text,
    ADD COLUMN manual_review_required boolean NOT NULL DEFAULT false;

CREATE TABLE payment_status_transitions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_status text NOT NULL,
    to_status text NOT NULL,
    active boolean NOT NULL DEFAULT true,
    description text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (from_status, to_status)
);

INSERT INTO payment_status_transitions (from_status, to_status, description) VALUES
    ('pending', 'processing', 'Submit pending payment to the settlement rail'),
    ('processing', 'completed', 'Provider confirmed successful settlement'),
    ('processing', 'pending', 'Temporary provider failure, retry scheduled'),
    ('processing', 'failed', 'Provider failed settlement and refund was posted'),
    ('pending', 'review_held', 'Manual or risk review required before settlement'),
    ('review_held', 'pending', 'Manual review released payment for settlement'),
    ('review_held', 'rejected', 'Manual review rejected payment and refund was posted'),
    ('completed', 'reversed', 'Completed payment was reversed'),
    ('failed', 'reversed', 'Failed payment was reversed')
ON CONFLICT (from_status, to_status) DO NOTHING;

CREATE OR REPLACE FUNCTION enforce_transfer_status_transition()
RETURNS trigger AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN
        IF NOT EXISTS (
            SELECT 1
            FROM payment_status_transitions
            WHERE from_status = OLD.status
                AND to_status = NEW.status
                AND active = true
        ) THEN
            RAISE EXCEPTION 'invalid transfer status transition from % to %', OLD.status, NEW.status
                USING ERRCODE = '23514';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER transfers_status_transition_enforce
BEFORE UPDATE OF status ON transfers
FOR EACH ROW EXECUTE FUNCTION enforce_transfer_status_transition();

CREATE TABLE payment_reason_code_mappings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    provider_code text NOT NULL,
    internal_code text NOT NULL,
    category text NOT NULL CHECK (category IN ('success', 'temporary_failure', 'permanent_failure', 'risk', 'manual_review', 'system')),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    description text NOT NULL,
    active boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_code)
);

INSERT INTO payment_reason_code_mappings (
    provider, provider_code, internal_code, category, severity, description
) VALUES
    ('local_sepa', 'ACCEPTED', 'settlement_completed', 'success', 'low', 'Local SEPA simulator accepted the payment'),
    ('local_sepa', 'LOCAL_TEMPORARY_FAILURE', 'provider_temporary_failure', 'temporary_failure', 'medium', 'Local SEPA simulator returned a temporary failure'),
    ('local_sepa', 'LOCAL_PERMANENT_FAILURE', 'provider_permanent_failure', 'permanent_failure', 'high', 'Local SEPA simulator returned a permanent failure'),
    ('local_sepa', 'MAX_ATTEMPTS_EXCEEDED', 'max_attempts_exceeded', 'permanent_failure', 'high', 'Payment exceeded the retry policy'),
    ('risk', 'RISK_REVIEW', 'risk_review', 'risk', 'medium', 'Risk engine requires manual review before settlement'),
    ('admin', 'MANUAL_REVIEW_REQUIRED', 'manual_review_required', 'manual_review', 'medium', 'Admin placed payment on manual review hold'),
    ('admin', 'MANUAL_REVIEW_RELEASED', 'manual_review_released', 'manual_review', 'low', 'Admin released payment from manual review'),
    ('admin', 'MANUAL_REVIEW_REJECTED', 'manual_review_rejected', 'manual_review', 'high', 'Admin rejected payment during manual review')
ON CONFLICT (provider, provider_code) DO UPDATE
SET internal_code = EXCLUDED.internal_code,
    category = EXCLUDED.category,
    severity = EXCLUDED.severity,
    description = EXCLUDED.description,
    active = true,
    updated_at = now();

CREATE TABLE payment_status_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id uuid NOT NULL REFERENCES transfers(id) ON DELETE RESTRICT,
    from_status text,
    to_status text NOT NULL,
    provider text NOT NULL DEFAULT 'system',
    provider_code text,
    reason_code text NOT NULL,
    reason_category text NOT NULL,
    note text,
    actor_type text NOT NULL CHECK (actor_type IN ('system', 'admin', 'provider', 'risk')),
    actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payment_status_events_transfer_created_at_idx
    ON payment_status_events (transfer_id, created_at DESC);

CREATE INDEX payment_status_events_reason_created_at_idx
    ON payment_status_events (reason_code, created_at DESC);

CREATE TABLE payment_review_cases (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id uuid NOT NULL UNIQUE REFERENCES transfers(id) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'released', 'rejected', 'canceled')),
    reason_code text NOT NULL,
    reason text NOT NULL,
    opened_by_type text NOT NULL CHECK (opened_by_type IN ('system', 'risk', 'admin')),
    opened_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    assigned_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decision_note text,
    opened_at timestamptz NOT NULL DEFAULT now(),
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payment_review_cases_status_created_at_idx
    ON payment_review_cases (status, created_at DESC);

CREATE INDEX payment_review_cases_transfer_status_idx
    ON payment_review_cases (transfer_id, status);

CREATE INDEX transfers_payment_review_status_idx
    ON transfers (status, created_at DESC)
    WHERE transfer_type = 'sepa' AND status = 'review_held';

ALTER TABLE sepa_settlement_events
    DROP CONSTRAINT IF EXISTS sepa_settlement_events_event_type_check;

ALTER TABLE sepa_settlement_events
    ADD CONSTRAINT sepa_settlement_events_event_type_check
    CHECK (event_type IN ('queued', 'processing', 'completed', 'failed', 'retried', 'review_held', 'review_released', 'review_rejected'));

CREATE TRIGGER payment_reason_code_mappings_set_updated_at
BEFORE UPDATE ON payment_reason_code_mappings
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER payment_review_cases_set_updated_at
BEFORE UPDATE ON payment_review_cases
FOR EACH ROW EXECUTE FUNCTION set_updated_at();