CREATE DATABASE IF NOT EXISTS bank_bkj CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE bank_bkj;

CREATE TABLE banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    api_endpoint VARCHAR(255) NULL,
    api_key VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_id INT NOT NULL,
    account_number VARCHAR(30) NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (bank_id) REFERENCES banks (id) ON DELETE CASCADE
);

CREATE TABLE transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'IDR',
    transaction_type ENUM('debit', 'credit') NOT NULL,
    description TEXT NULL,
    transaction_date DATETIME NOT NULL,
    external_ref VARCHAR(100) NULL,
    synced_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE
);

CREATE TABLE bank_api_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    bank_id INT NOT NULL,
    request_payload TEXT NULL,
    response_payload TEXT NULL,
    status_code INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bank_id) REFERENCES banks (id) ON DELETE CASCADE
);