CREATE TABLE payment_beneficiary_screenings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id uuid REFERENCES transfers(id) ON DELETE SET NULL,
    beneficiary_id uuid REFERENCES beneficiaries(id) ON DELETE SET NULL,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    provider text NOT NULL,
    external_screening_id text NOT NULL DEFAULT '',
    status text NOT NULL CHECK (status IN ('clear', 'review', 'hit')),
    risk_score integer NOT NULL CHECK (risk_score BETWEEN 0 AND 100),
    matched boolean NOT NULL DEFAULT false,
    match_details jsonb NOT NULL DEFAULT '{}'::jsonb,
    screened_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payment_beneficiary_screenings_transfer_idx
    ON payment_beneficiary_screenings (transfer_id, created_at DESC);

CREATE INDEX payment_beneficiary_screenings_beneficiary_idx
    ON payment_beneficiary_screenings (beneficiary_id, created_at DESC);

CREATE TABLE sepa_provider_reports (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    report_reference text NOT NULL,
    report_type text NOT NULL DEFAULT 'settlement',
    status text NOT NULL DEFAULT 'processed' CHECK (status IN ('received', 'processed', 'failed')),
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    item_count integer NOT NULL DEFAULT 0 CHECK (item_count >= 0),
    matched_count integer NOT NULL DEFAULT 0 CHECK (matched_count >= 0),
    processed_count integer NOT NULL DEFAULT 0 CHECK (processed_count >= 0),
    error_count integer NOT NULL DEFAULT 0 CHECK (error_count >= 0),
    received_at timestamptz NOT NULL DEFAULT now(),
    processed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, report_reference)
);

CREATE TRIGGER sepa_provider_reports_set_updated_at
BEFORE UPDATE ON sepa_provider_reports
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX sepa_provider_reports_created_at_idx
    ON sepa_provider_reports (created_at DESC);

CREATE TABLE sepa_provider_report_items (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    report_id uuid NOT NULL REFERENCES sepa_provider_reports(id) ON DELETE CASCADE,
    transfer_id uuid REFERENCES transfers(id) ON DELETE SET NULL,
    provider_reference text NOT NULL DEFAULT '',
    provider_status text NOT NULL,
    provider_reason_code text NOT NULL DEFAULT '',
    provider_reason text NOT NULL DEFAULT '',
    amount_cents bigint NOT NULL DEFAULT 0 CHECK (amount_cents >= 0),
    currency char(3) REFERENCES currencies(code) ON DELETE RESTRICT,
    matched boolean NOT NULL DEFAULT false,
    processed boolean NOT NULL DEFAULT false,
    processing_error text NOT NULL DEFAULT '',
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX sepa_provider_report_items_report_idx
    ON sepa_provider_report_items (report_id, created_at);

CREATE INDEX sepa_provider_report_items_transfer_idx
    ON sepa_provider_report_items (transfer_id, created_at DESC);

CREATE TABLE inbound_payments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    provider_reference text NOT NULL,
    account_id uuid REFERENCES accounts(id) ON DELETE SET NULL,
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    debtor_name text NOT NULL DEFAULT '',
    debtor_iban text NOT NULL DEFAULT '',
    creditor_iban text NOT NULL,
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'completed' CHECK (status IN ('received', 'completed', 'failed')),
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    processed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_reference)
);

CREATE INDEX inbound_payments_account_created_at_idx
    ON inbound_payments (account_id, created_at DESC);

CREATE TABLE payment_notifications (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    transfer_id uuid REFERENCES transfers(id) ON DELETE SET NULL,
    inbound_payment_id uuid REFERENCES inbound_payments(id) ON DELETE SET NULL,
    notification_type text NOT NULL,
    channel text NOT NULL DEFAULT 'in_app',
    status text NOT NULL DEFAULT 'created' CHECK (status IN ('created', 'sent', 'failed')),
    title text NOT NULL,
    message text NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    sent_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payment_notifications_user_created_at_idx
    ON payment_notifications (user_id, created_at DESC);

CREATE INDEX payment_notifications_transfer_idx
    ON payment_notifications (transfer_id, created_at DESC);

INSERT INTO payment_reason_code_mappings (
    provider, provider_code, internal_code, category, severity, description
) VALUES
    ('local_sanctions', 'BENEFICIARY_SANCTIONS_HIT', 'beneficiary_sanctions_hit', 'compliance', 'critical', 'Beneficiary sanctions screening returned a hit'),
    ('local_sanctions', 'BENEFICIARY_SANCTIONS_REVIEW', 'beneficiary_sanctions_review', 'compliance', 'high', 'Beneficiary sanctions screening requires review'),
    ('local_sepa', 'PROVIDER_REPORT_COMPLETED', 'provider_report_completed', 'settlement', 'low', 'Provider settlement report confirmed completion'),
    ('local_sepa', 'PROVIDER_REPORT_FAILED', 'provider_report_failed', 'settlement', 'high', 'Provider settlement report confirmed failure')
ON CONFLICT (provider, provider_code) DO NOTHING;