CREATE TABLE Role
(
    role_id   SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Currency
(
    currency_id SERIAL PRIMARY KEY,
    code        VARCHAR(10) NOT NULL UNIQUE,
    name        VARCHAR(50) NOT NULL

);

CREATE TABLE Employee
(
    employee_id SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    last_name   VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    position    VARCHAR(50),
    hire_date   DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Branch
(
    branch_id   SERIAL PRIMARY KEY,
    branch_name VARCHAR(100) NOT NULL,
    address     VARCHAR(255),
    city        VARCHAR(50)
);

CREATE TABLE Client
(
    client_id       SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    last_name       VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    embg            VARCHAR(130) UNIQUE,
    phone           VARCHAR(20),
    email           VARCHAR(100) UNIQUE,
    address         VARCHAR(255),
    status          VARCHAR(20) DEFAULT 'ACTIVE',
    date_registered DATE DEFAULT CURRENT_DATE,
    branch_id       INT,
    CONSTRAINT chk_client_status
        CHECK (status IN ('ACTIVE', 'INACTIVE', 'BLOCKED')),
    CONSTRAINT chk_client_email
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_client_phone
        CHECK (phone ~ '^\+?[0-9]{7,15}$'),
    FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Client_user
(
    client_user_id SERIAL PRIMARY KEY,
    username       VARCHAR(50)  NOT NULL UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    last_login     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status         VARCHAR(20) DEFAULT 'ACTIVE',
    client_id      INT          NOT NULL,
    CONSTRAINT chk_client_user_status
        CHECK (status IN ('ACTIVE', 'LOCKED', 'DISABLED')),
    CONSTRAINT chk_password_hash_length
        CHECK (length(password_hash) >= 60),
    CONSTRAINT chk_password
        CHECK (
            password_hash LIKE '$2a$%' OR
            password_hash LIKE '$2b$%' OR
            password_hash LIKE '$2y$%'
            ),
    FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Loan
(
    loan_id       SERIAL PRIMARY KEY,
    loan_type     VARCHAR(20)    NOT NULL,
    amount        DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
    interest_rate DECIMAL(5, 2)  NOT NULL CHECK (interest_rate >= 0),
    start_date    DATE           NOT NULL,
    end_date      DATE,
    status        VARCHAR(20) DEFAULT 'PENDING',
    approved_date DATE,
    client_id     INT            NOT NULL,
    employee_id   INT,
    CONSTRAINT chk_loan_status
        CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'CLOSED')),
    CONSTRAINT chk_loan_dates
        CHECK (end_date IS NULL OR end_date > start_date),
    CONSTRAINT chk_interest_range
        CHECK (interest_rate BETWEEN 0 AND 100),
    CONSTRAINT chek_status
        CHECK ((status = 'APPROVED' AND approved_date IS NOT NULL) OR (status <> 'APPROVED')),
    FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Account
(
    account_id     SERIAL PRIMARY KEY,
    account_number VARCHAR(30) NOT NULL UNIQUE,
    account_type   VARCHAR(20) NOT NULL,
    balance        DECIMAL(20, 2) DEFAULT 0,
    currency_id    INT,
    open_date      DATE DEFAULT CURRENT_DATE,
    status         VARCHAR(20) DEFAULT 'ACTIVE',
    client_id      INT         NOT NULL,
    client_user_id INT,
    loan_id        INT,
    branch_id      INT         NOT NULL,
    CONSTRAINT chk_account_status
        CHECK (status IN ('ACTIVE', 'CLOSED', 'BLOCKED')),
    CONSTRAINT chk_balance_positive
        CHECK (balance >= 0),
    CONSTRAINT chk_account_type
        CHECK (account_type IN ('SAVINGS', 'CHECKING', 'CREDIT', 'LOAN', 'INVESTMENT')),
    FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (client_user_id) REFERENCES Client_user (client_user_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE SavingsAccount
(
    account_id          INT PRIMARY KEY REFERENCES Account (account_id) ON DELETE CASCADE ON UPDATE CASCADE,
    interest_rate       DECIMAL(5, 2) NOT NULL CHECK (interest_rate BETWEEN 0 AND 100),
    interest_period     VARCHAR(20)   NOT NULL CHECK (interest_period IN ('DAILY', 'MONTHLY', 'YEARLY')),
    minimum_balance     DECIMAL(20, 2) DEFAULT 0 CHECK (minimum_balance >= 0),
    capitalization_type VARCHAR(20)   NOT NULL CHECK (capitalization_type IN ('SIMPLE', 'COMPOUND'))
);

CREATE TABLE Loan_installment
(
    installment_id     SERIAL PRIMARY KEY,
    installment_number INT            NOT NULL,
    due_date           DATE           NOT NULL,
    amount             DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
    status             VARCHAR(20),
    paid_date          DATE,
    loan_id            INT            NOT NULL,
    CONSTRAINT chk_installment_status
        CHECK (status IN ('PENDING', 'PAID', 'LATE')),
    CONSTRAINT uq_installment_per_loan
        UNIQUE (installment_number, loan_id),
    FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Collateral
(
    collateral_id SERIAL PRIMARY KEY,
    type          VARCHAR(30)    NOT NULL,
    description   VARCHAR(255),
    value         DECIMAL(15, 2) NOT NULL,
    status        VARCHAR(20),
    date_added    DATE DEFAULT CURRENT_DATE,
    loan_id       INT            NOT NULL,
    client_id     INT            NOT NULL,

    CONSTRAINT chk_collateral_value
        CHECK (value >= 0),

    CONSTRAINT chk_collateral_status
        CHECK (status IN ('ACTIVE', 'RELEASED', 'PLEDGED', 'DEFAULTED')),

    FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (client_id) REFERENCES Client (client_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Transaction_type
(
    transaction_type_id SERIAL PRIMARY KEY,
    type_name           VARCHAR(30) NOT NULL,
    description         VARCHAR(225),
    CHECK (type_name IN ('DEPOSIT', 'WITHDRAWAL', 'TRANSFER', 'PAYMENT', 'FEE', 'INTEREST', 'REFUND', 'LOAN_PAYMENT',
                         'CURRENCY_EXCHANGE'))
);

CREATE TABLE Payment_method
(
    method_id   SERIAL PRIMARY KEY,
    method_name VARCHAR(30) NOT NULL,
    description VARCHAR(225),
    CONSTRAINT chk_payment_method
        CHECK (method_name IN ('CASH', 'DEBIT_CARD', 'CREDIT_CARD', 'BANK_TRANSFER', 'MOBILE_PAYMENT', 'CHECK'))
);

CREATE TABLE Counter
(
    counter_id     SERIAL PRIMARY KEY,
    counter_number INT NOT NULL,
    status         VARCHAR(20),
    branch_id      INT NOT NULL,
    CONSTRAINT chk_counter_status
        CHECK (status IN ('OPEN', 'CLOSED', 'MAINTENANCE')),
    CONSTRAINT uq_counter_per_branch
        UNIQUE (counter_number, branch_id),
    FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Branch_employee
(
    emp_branch_id SERIAL PRIMARY KEY,
    branch_id     INT NOT NULL,
    employee_id   INT NOT NULL,
    start_date    DATE        DEFAULT CURRENT_DATE,
    end_date      DATE,
    status        VARCHAR(20) DEFAULT 'ACTIVE',
    CONSTRAINT chk_branch_employee_dates
        CHECK (end_date IS NULL OR end_date > start_date),
    CONSTRAINT chk_branch_employee_status
        CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'TEMPORARY_ASSIGNMENT')),
    FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Employee_counter
(
    emp_counter_id SERIAL PRIMARY KEY,
    start_shift    TIMESTAMP NOT NULL,
    end_shift      TIMESTAMP,
    employee_id    INT       NOT NULL,
    counter_id     INT       NOT NULL,
    CONSTRAINT chk_shift_dates
        CHECK (end_shift IS NULL OR end_shift > start_shift),
    FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (counter_id) REFERENCES Counter (counter_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Transaction
(
    transaction_id      SERIAL PRIMARY KEY,
    amount              DECIMAL(20, 2) NOT NULL CHECK (amount > 0),
    transaction_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status              VARCHAR(20) DEFAULT 'PENDING',
    description         VARCHAR(225),
    reference_number    VARCHAR(30) UNIQUE,
    account_id          INT            NOT NULL,
    account_id2         INT,
    transaction_type_id INT,
    payment_method_id   INT,
    currency_id         INT,
    currency_id2        INT,
    installment_id      INT,
    emp_counter_id      INT,
    loan_id             INT,
    CONSTRAINT chk_transaction_status
        CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED')),
    CONSTRAINT chk_transfer_logic
        CHECK (account_id2 IS NULL OR account_id <> account_id2),
    FOREIGN KEY (account_id) REFERENCES Account (account_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (account_id2) REFERENCES Account (account_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (transaction_type_id) REFERENCES Transaction_type (transaction_type_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES Payment_method (method_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (currency_id2) REFERENCES Currency (currency_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (installment_id) REFERENCES Loan_installment (installment_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (emp_counter_id) REFERENCES Employee_counter (emp_counter_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (loan_id) REFERENCES Loan (loan_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Receipt
(
    receipt_id     SERIAL PRIMARY KEY,
    print_time     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    receipt_number VARCHAR(30) NOT NULL UNIQUE,
    transaction_id INT         NOT NULL UNIQUE,
    FOREIGN KEY (transaction_id) REFERENCES Transaction (transaction_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Daily_report
(
    report_id          SERIAL PRIMARY KEY,
    report_date        DATE           NOT NULL,
    total_transactions INT            NOT NULL DEFAULT 0,
    total_amount       DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
    branch_id          INT            NOT NULL,
    FOREIGN KEY (branch_id) REFERENCES Branch (branch_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Exchange_rate
(
    rate_id      SERIAL PRIMARY KEY,
    rate         DECIMAL(15, 6) NOT NULL CHECK (rate > 0),
    date_updated DATE DEFAULT CURRENT_DATE,
    currency_id  INT            NOT NULL,
    CONSTRAINT uq_currency_date
        UNIQUE (currency_id, date_updated),
    FOREIGN KEY (currency_id) REFERENCES Currency (currency_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Bank_user
(
    user_id       SERIAL PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id       INT          NOT NULL,
    employee_id   INT          NOT NULL,
    CONSTRAINT chk_password_hash_length
        CHECK (length(password_hash) >= 60),
    CONSTRAINT chk_password
        CHECK (
            password_hash LIKE '$2a$%' OR
            password_hash LIKE '$2b$%' OR
            password_hash LIKE '$2y$%'
            ),
    FOREIGN KEY (role_id) REFERENCES Role (role_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES Employee (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE InterestPayment
(
    interest_payment_id SERIAL PRIMARY KEY,
    amount              DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
    payment_date        DATE           NOT NULL DEFAULT CURRENT_DATE,
    period_start        DATE           NOT NULL,
    period_end          DATE           NOT NULL,
    account_id          INT            NOT NULL,
    transaction_id      INT            NOT NULL,
    FOREIGN KEY (transaction_id) REFERENCES Transaction (transaction_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (account_id) REFERENCES Account (account_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_period_dates CHECK (period_end >= period_start)
);