02_payments.sql

-- ==========================================================================
-- PaymentsDB - Fintech double-entry ledger
-- Every money movement (Transactions) produces balanced debit/credit
-- LedgerEntries (Direction = +1 debit / -1 credit). Append-heavy facts.
-- All tables have an IDENTITY PK + Change Tracking. Data loaded by loader/.
-- ==========================================================================

IF DB_ID('PaymentsDB') IS NOT NULL
BEGIN
    ALTER DATABASE PaymentsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PaymentsDB;
END
GO

CREATE DATABASE PaymentsDB;
GO

USE PaymentsDB;
GO

-- SIMPLE recovery keeps the transaction log small during large bulk loads.
ALTER DATABASE PaymentsDB SET RECOVERY SIMPLE;
GO

ALTER DATABASE PaymentsDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

-- --------------------------------------------------------------------------
-- Dimensions
-- --------------------------------------------------------------------------

CREATE TABLE Customers (
    CustomerID     INT IDENTITY(1,1) PRIMARY KEY,
    CustomerNumber NVARCHAR(20)  NOT NULL CONSTRAINT UQ_PayCustomers_Number UNIQUE,
    FullName       NVARCHAR(200),
    Email          NVARCHAR(256) NOT NULL CONSTRAINT UQ_PayCustomers_Email UNIQUE,
    KycStatus      NVARCHAR(20)  NOT NULL DEFAULT 'pending',
    CreatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    IsDeleted      BIT           NOT NULL DEFAULT 0
);
GO

CREATE TABLE Merchants (
    MerchantID   INT IDENTITY(1,1) PRIMARY KEY,
    MerchantCode NVARCHAR(20)  NOT NULL CONSTRAINT UQ_Merchants_Code UNIQUE,
    Name         NVARCHAR(200),
    Mcc          CHAR(4),
    CountryCode  CHAR(2),
    CreatedAt    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TABLE Accounts (
    AccountID     INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID    INT           NOT NULL,
    AccountNumber NVARCHAR(34)  NOT NULL CONSTRAINT UQ_Accounts_Number UNIQUE,
    AccountType   NVARCHAR(20)  NOT NULL DEFAULT 'checking',
    CurrencyCode  CHAR(3)       NOT NULL DEFAULT 'USD',
    Balance       DECIMAL(18,4) NOT NULL DEFAULT 0,
    Status        NVARCHAR(20)  NOT NULL DEFAULT 'active',
    OpenedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt     DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Accounts_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
GO

CREATE TABLE Cards (
    CardID      INT IDENTITY(1,1) PRIMARY KEY,
    AccountID   INT          NOT NULL,
    CardToken   NVARCHAR(40) NOT NULL CONSTRAINT UQ_Cards_Token UNIQUE,
    LastFour    CHAR(4),
    Brand       NVARCHAR(20),
    Status      NVARCHAR(20) NOT NULL DEFAULT 'active',
    ExpiryMonth TINYINT,
    ExpiryYear  SMALLINT,
    CreatedAt   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Cards_Account FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);
GO

-- --------------------------------------------------------------------------
-- Facts
-- --------------------------------------------------------------------------

CREATE TABLE Transactions (
    TransactionID BIGINT IDENTITY(1,1) PRIMARY KEY,
    TransactionRef NVARCHAR(40) NOT NULL CONSTRAINT UQ_Transactions_Ref UNIQUE,
    AccountID     INT           NOT NULL,
    MerchantID    INT NULL,
    CardID        INT NULL,
    Type          NVARCHAR(20)  NOT NULL,
    Status        NVARCHAR(20)  NOT NULL,
    Amount        DECIMAL(18,4) NOT NULL,
    CurrencyCode  CHAR(3)       NOT NULL DEFAULT 'USD',
    OccurredAt    DATETIME2(3)  NOT NULL,
    CreatedAt     DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Transactions_Account  FOREIGN KEY (AccountID)  REFERENCES Accounts(AccountID),
    CONSTRAINT FK_Transactions_Merchant FOREIGN KEY (MerchantID) REFERENCES Merchants(MerchantID),
    CONSTRAINT FK_Transactions_Card     FOREIGN KEY (CardID)     REFERENCES Cards(CardID)
);
GO

CREATE TABLE LedgerEntries (
    EntryID       BIGINT IDENTITY(1,1) PRIMARY KEY,
    TransactionID BIGINT        NOT NULL,
    AccountID     INT           NOT NULL,
    Direction     SMALLINT      NOT NULL,  -- +1 debit, -1 credit
    Amount        DECIMAL(18,4) NOT NULL,
    CurrencyCode  CHAR(3)       NOT NULL DEFAULT 'USD',
    PostedAt      DATETIME2(3)  NOT NULL,
    CreatedAt     DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Ledger_Transaction FOREIGN KEY (TransactionID) REFERENCES Transactions(TransactionID),
    CONSTRAINT FK_Ledger_Account     FOREIGN KEY (AccountID)     REFERENCES Accounts(AccountID)
);
GO

-- --------------------------------------------------------------------------
-- Secondary indexes
-- --------------------------------------------------------------------------
CREATE INDEX IX_Accounts_Customer      ON Accounts(CustomerID);
CREATE INDEX IX_Cards_Account          ON Cards(AccountID);
CREATE INDEX IX_Transactions_Account   ON Transactions(AccountID);
CREATE INDEX IX_Transactions_Merchant  ON Transactions(MerchantID);
CREATE INDEX IX_Transactions_Occurred  ON Transactions(OccurredAt);
CREATE INDEX IX_Ledger_Transaction     ON LedgerEntries(TransactionID);
CREATE INDEX IX_Ledger_AccountPosted   ON LedgerEntries(AccountID, PostedAt);
GO

-- --------------------------------------------------------------------------
-- Enable Change Tracking on every table.
-- --------------------------------------------------------------------------
ALTER TABLE Customers     ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Merchants     ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Accounts      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Cards         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Transactions  ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE LedgerEntries ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
GO

PRINT 'PaymentsDB schema created.';
GO