schema.sql

-- =====================================================
-- NovaPay Banking System — MySQL Schema


CREATE DATABASE IF NOT EXISTS novapay_bank CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE novapay_bank;

-- ===== TABLE 1: USERS =====
CREATE TABLE IF NOT EXISTS users (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(100)  NOT NULL,
  email           VARCHAR(150)  UNIQUE NOT NULL,
  phone           VARCHAR(20),
  password_hash   VARCHAR(255)  NOT NULL,
  pin_hash        VARCHAR(255),
  role            ENUM('customer','staff','admin') DEFAULT 'customer',
  is_verified     TINYINT(1)    DEFAULT 0,
  is_locked       TINYINT(1)    DEFAULT 0,
  failed_attempts INT           DEFAULT 0,
  created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);

-- ===== TABLE 2: ACCOUNTS =====
CREATE TABLE IF NOT EXISTS accounts (
  id                   INT AUTO_INCREMENT PRIMARY KEY,
  user_id              INT NOT NULL,
  account_number       VARCHAR(20) UNIQUE NOT NULL,
  account_type         ENUM('Savings','Current','Fixed Deposit') DEFAULT 'Savings',
  balance              DECIMAL(15,2) DEFAULT 0.00,
  status               ENUM('pending','active','frozen','closed') DEFAULT 'pending',
  daily_transfer_limit DECIMAL(15,2) DEFAULT 100000.00,
  opened_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ===== TABLE 3: TRANSACTIONS =====
CREATE TABLE IF NOT EXISTS transactions (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  from_account_id INT,
  to_account_id   INT,
  type            ENUM('deposit','withdrawal','transfer','bill','emi') NOT NULL,
  amount          DECIMAL(15,2) NOT NULL,
  fee             DECIMAL(10,2) DEFAULT 0.00,
  status          ENUM('pending','success','failed','reversed') DEFAULT 'pending',
  reference_no    VARCHAR(50) UNIQUE NOT NULL,
  description     VARCHAR(255),
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_account_id) REFERENCES accounts(id),
  FOREIGN KEY (to_account_id)   REFERENCES accounts(id)
);

-- ===== TABLE 4: LEDGER ENTRIES (Double-Entry) =====
CREATE TABLE IF NOT EXISTS ledger_entries (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  transaction_id INT NOT NULL,
  account_id     INT NOT NULL,
  entry_type     ENUM('debit','credit') NOT NULL,
  amount         DECIMAL(15,2) NOT NULL,
  balance_after  DECIMAL(15,2) NOT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (transaction_id) REFERENCES transactions(id),
  FOREIGN KEY (account_id)     REFERENCES accounts(id)
);

-- ===== TABLE 5: OTPs =====
CREATE TABLE IF NOT EXISTS otps (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT NOT NULL,
  otp_code    VARCHAR(10) NOT NULL,
  purpose     VARCHAR(50) DEFAULT 'transfer',
  expires_at  DATETIME NOT NULL,
  is_verified TINYINT(1) DEFAULT 0,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- ===== TABLE 6: CARDS =====
CREATE TABLE IF NOT EXISTS cards (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  account_id  INT NOT NULL,
  card_number VARCHAR(20) NOT NULL,
  card_type   ENUM('debit','credit','virtual') DEFAULT 'debit',
  expiry_date DATE,
  cvv_hash    VARCHAR(255),
  is_locked   TINYINT(1)    DEFAULT 0,
  daily_limit DECIMAL(15,2) DEFAULT 50000.00,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

-- ===== TABLE 7: LOANS =====
CREATE TABLE IF NOT EXISTS loans (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  account_id    INT NOT NULL,
  loan_type     ENUM('personal','home','vehicle','education') DEFAULT 'personal',
  principal     DECIMAL(15,2) NOT NULL,
  interest_rate DECIMAL(5,2)  DEFAULT 8.50,
  tenure_months INT           DEFAULT 12,
  emi_amount    DECIMAL(15,2),
  status        ENUM('pending','approved','active','closed') DEFAULT 'pending',
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)    REFERENCES users(id),
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

-- ===== TABLE 8: AUDIT LOGS =====
CREATE TABLE IF NOT EXISTS audit_logs (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT,
  action     VARCHAR(255) NOT NULL,
  ip_address VARCHAR(50),
  status     ENUM('success','failed') DEFAULT 'success',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =====================================================
-- DEMO SEED DATA
-- Password for all accounts = "demo123"
-- Hash: $2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy
-- =====================================================

INSERT IGNORE INTO users (id, name, email, phone, password_hash, role, is_verified) VALUES
  (1, 'Arjun Sharma',  'arjun@email.com',       '9876543210', '$2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy', 'customer', 1),
  (2, 'Bank Admin',    'admin@novapay.com',      '9999999999', '$2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy', 'admin',    1),
  (3, 'Priya Staff',   'staff@novapay.com',      '9111111111', '$2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy', 'staff',    1),
  (4, 'Priya Patel',   'priya@email.com',        '9123456789', '$2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy', 'customer', 1),
  (5, 'Rahul Singh',   'rahul@email.com',        '9012345678', '$2a$12$LvKbizVm4a.RGGq3xDUCreIZ5HkMPrAEhHxcj2sRfTJXvORb3G5Sy', 'customer', 0);

INSERT IGNORE INTO accounts (id, user_id, account_number, account_type, balance, status) VALUES
  (1, 1, '4721589100', 'Savings',  98500.00,  'active'),
  (2, 1, '3344778200', 'Current',  26350.00,  'active'),
  (3, 4, '5512334400', 'Savings',  45200.00,  'active'),
  (4, 5, '6678912300', 'Savings',  12800.00,  'pending');

INSERT IGNORE INTO transactions (id, from_account_id, to_account_id, type, amount, status, reference_no, description) VALUES
  (1, NULL, 1, 'deposit',    45000.00, 'success', 'NOVA0001', 'Salary Credit'),
  (2, 1,    3, 'transfer',   12000.00, 'success', 'NOVA0002', 'Rent Transfer'),
  (3, 1, NULL, 'bill',        1450.00, 'success', 'NOVA0003', 'Electricity Bill'),
  (4, NULL, 1, 'deposit',     5000.00, 'success', 'NOVA0004', 'UPI Received'),
  (5, 1, NULL, 'bill',          299.00, 'success', 'NOVA0005', 'Mobile Recharge'),
  (6, 1, NULL, 'bill',          800.00, 'failed',  'NOVA0006', 'Internet Bill');

INSERT IGNORE INTO cards (id, account_id, card_number, card_type, expiry_date, is_locked) VALUES
  (1, 1, '4721XXXX5891', 'debit', '2028-08-31', 0);

INSERT IGNORE INTO audit_logs (user_id, action, status) VALUES
  (1, 'User login', 'success'),
  (1, 'Transfer ₹12000 to 5512334400', 'success'),
  (1, 'Bill payment ₹1450', 'success');

-- =====================================================
-- All 8 tables created! Demo password = "demo123"
-- Login: arjun@email.com / demo123
-- Admin: admin@novapay.com / demo123
-- =====================================================