src/main/resources/db/migration/V1__init.sql - master - futureability/BonusService

src/main/resources/db/migration/V1__init.sql

32 строки2 KB

fee-lee

addMigration

04 фев 2026, 07:29

04 фев 2026, 07:291ccc7a3

CREATE TABLE bonus_account (
                                id BIGSERIAL PRIMARY KEY,
                                card_number VARCHAR(20) UNIQUE NOT NULL,
                                customer_id BIGINT NOT NULL,
                                balance DECIMAL(15,2) DEFAULT 0.00 CONSTRAINT positive_balance CHECK (balance >= 0),
                                version BIGINT DEFAULT 0,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

                                CONSTRAINT uq_card_number UNIQUE (card_number)
);

CREATE INDEX idx_bonus_accounts_card_number ON bonus_accounts(card_number);
CREATE INDEX idx_bonus_accounts_customer_id ON bonus_accounts(customer_id);

CREATE TABLE bonus_transaction (
                                    id BIGSERIAL PRIMARY KEY,
                                    account_id BIGINT NOT NULL,
                                    type VARCHAR(20) NOT NULL,
                                    amount DECIMAL(15,2) NOT NULL CONSTRAINT positive_amount CHECK (amount > 0),
                                    order_id VARCHAR(50),
                                    description TEXT,
                                    status VARCHAR(20) DEFAULT 'COMPLETED',
                                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                    expires_at TIMESTAMP NOT NULL,

                                    CONSTRAINT fk_account FOREIGN KEY (account_id)
                                        REFERENCES bonus_accounts(id) ON DELETE CASCADE,

                                    CONSTRAINT valid_status CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED', 'EXPIRED')),
                                    CONSTRAINT valid_type CHECK (type IN ('ACCRUAL', 'REDEMPTION', 'REFUND'))
);