CREATE TABLE beneficiaries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    name text NOT NULL,
    iban text NOT NULL CHECK (iban ~ '^[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}$'),
    bic text CHECK (bic IS NULL OR bic ~ '^[A-Z]{4}[A-Z]{2}[A-Z0-9]{2}([A-Z0-9]{3})?$'),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'deleted')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, iban)
);

CREATE INDEX beneficiaries_user_id_status_created_at_idx
    ON beneficiaries (user_id, status, created_at DESC);

ALTER TABLE transfers
    ALTER COLUMN to_account_id DROP NOT NULL,
    ADD COLUMN beneficiary_id uuid REFERENCES beneficiaries(id) ON DELETE RESTRICT,
    ADD COLUMN beneficiary_name text,
    ADD COLUMN beneficiary_iban text,
    ADD COLUMN beneficiary_bic text,
    ADD COLUMN payment_reference text,
    ADD COLUMN transfer_type text NOT NULL DEFAULT 'internal'
        CHECK (transfer_type IN ('internal', 'sepa'));

ALTER TABLE transfers
    ADD CONSTRAINT transfers_destination_check
    CHECK (to_account_id IS NOT NULL OR beneficiary_iban IS NOT NULL);

CREATE INDEX transfers_beneficiary_id_idx ON transfers (beneficiary_id);
CREATE INDEX transfers_beneficiary_iban_idx ON transfers (beneficiary_iban);

CREATE TRIGGER beneficiaries_set_updated_at
BEFORE UPDATE ON beneficiaries
FOR EACH ROW EXECUTE FUNCTION set_updated_at();