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

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(30) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 status ENUM('pending','active','blocked') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE bank_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, account_no VARCHAR(24) NOT NULL UNIQUE,
 account_type ENUM('savings','current') DEFAULT 'savings',
 balance DECIMAL(18,2) NOT NULL DEFAULT 0, status ENUM('pending','active','blocked','closed') DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE kyc_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, document_type VARCHAR(50) NOT NULL,
 document_number VARCHAR(100) NULL, file_path VARCHAR(255) NULL,
 status ENUM('pending','approved','rejected') DEFAULT 'pending',
 remarks VARCHAR(500) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL,
 reference_no VARCHAR(40) NOT NULL UNIQUE,
 type ENUM('deposit','withdrawal','transfer_in','transfer_out','loan','fd') NOT NULL,
 amount DECIMAL(18,2) NOT NULL, balance_after DECIMAL(18,2) NOT NULL,
 description VARCHAR(255), status ENUM('pending','completed','rejected') DEFAULT 'completed',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE
);

CREATE TABLE deposits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(18,2) NOT NULL, method VARCHAR(50) DEFAULT 'cash',
 status ENUM('pending','approved','rejected') DEFAULT 'pending',
 remarks VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE withdrawals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(18,2) NOT NULL, method VARCHAR(50) DEFAULT 'cash',
 status ENUM('pending','approved','rejected') DEFAULT 'pending',
 remarks VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE virtual_cards (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 card_last4 CHAR(4) NOT NULL, card_token VARCHAR(64) NOT NULL UNIQUE,
 status ENUM('active','frozen','closed') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE loans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(18,2) NOT NULL, interest_rate DECIMAL(6,2) NOT NULL,
 tenure_months INT NOT NULL, status ENUM('pending','approved','rejected','closed') DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE fixed_deposits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 principal DECIMAL(18,2) NOT NULL, interest_rate DECIMAL(6,2) NOT NULL,
 tenure_months INT NOT NULL, maturity_amount DECIMAL(18,2) NOT NULL,
 status ENUM('active','matured','closed') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(150) NOT NULL, message TEXT NOT NULL, is_read TINYINT(1) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE support_tickets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 subject VARCHAR(200) NOT NULL, message TEXT NOT NULL,
 status ENUM('open','closed') DEFAULT 'open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT UNSIGNED NULL,
 action VARCHAR(150) NOT NULL, details TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE SET NULL
);

CREATE TABLE settings (
 id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(100) UNIQUE NOT NULL, setting_value VARCHAR(500) NOT NULL
);

INSERT INTO settings(setting_key,setting_value) VALUES
('site_name','Digital Banking Platform'),
('fd_interest_rate','7.00'),
('loan_interest_rate','12.00')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

-- Demo admin: change password immediately after first login.
INSERT INTO admins(name,email,password_hash)
VALUES ('Administrator','admin@example.com',
'$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llCkT2vQ1y1Qm6YwYyT7e');