-- Balance Sheet Module Database Setup
-- Description: Creates tables for Balance Sheet module (Categories, Banks, Transactions, Recurring)
-- Created: 2025-01-XX
-- Categories table for income and expense categories
CREATE TABLE IF NOT EXISTS bs_categories (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
type ENUM('income', 'expense') NOT NULL,
description TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_type (type),
UNIQUE KEY unique_user_category (user_id, name, type),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Banks table for managing bank accounts
CREATE TABLE IF NOT EXISTS bs_banks (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
owner VARCHAR(255) NOT NULL,
balance DECIMAL(15, 2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Transactions table for income and expenses
CREATE TABLE IF NOT EXISTS bs_transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
category_id INT NOT NULL,
bank_id INT NOT NULL,
type ENUM('income', 'expense') NOT NULL,
amount DECIMAL(15, 2) NOT NULL,
transaction_date DATE NOT NULL,
description TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_category_id (category_id),
INDEX idx_bank_id (bank_id),
INDEX idx_type (type),
INDEX idx_transaction_date (transaction_date),
INDEX idx_user_date (user_id, transaction_date),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES bs_categories(id) ON DELETE RESTRICT,
FOREIGN KEY (bank_id) REFERENCES bs_banks(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Recurring transactions table
CREATE TABLE IF NOT EXISTS bs_recurring (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
category_id INT NOT NULL,
bank_id INT NOT NULL,
type ENUM('income', 'expense') NOT NULL,
amount DECIMAL(15, 2) NOT NULL,
description TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_category_id (category_id),
INDEX idx_bank_id (bank_id),
INDEX idx_type (type),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES bs_categories(id) ON DELETE RESTRICT,
FOREIGN KEY (bank_id) REFERENCES bs_banks(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
