To recreate the schema and tables you have in your MySQL database, you can
use the following SQL CREATE TABLE statements. These statements reflect the
structure and columns you've provided.
1. Create bank_statement Table
sql
Copy code
CREATE TABLE bank_statement (
id INT NOT NULL AUTO_INCREMENT,
cash_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
bank_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
2. Create categories Table
sql
Copy code
CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
type ENUM('expense', 'income') NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
3. Create expenses Table
sql
Copy code
CREATE TABLE expenses (
id INT NOT NULL AUTO_INCREMENT,
description VARCHAR(255) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
by_cash DECIMAL(10,2) NOT NULL,
by_bank DECIMAL(10,2) NOT NULL,
date DATE NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
category_id INT DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_expense_category (category_id),
CONSTRAINT fk_expense_category FOREIGN KEY (category_id)
REFERENCES categories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
4. Create income_categories Table
sql
Copy code
CREATE TABLE income_categories (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
type ENUM('income') DEFAULT 'income',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
5. Create incomes Table
sql
Copy code
CREATE TABLE incomes (
id INT NOT NULL AUTO_INCREMENT,
description VARCHAR(255) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
by_cash DECIMAL(10,2) NOT NULL,
by_bank DECIMAL(10,2) NOT NULL,
date DATE NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
category_id INT DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_income_category (category_id),
CONSTRAINT fk_income_category FOREIGN KEY (category_id)
REFERENCES income_categories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
6. Create summaries Table
sql
Copy code
CREATE TABLE summaries (
id INT NOT NULL AUTO_INCREMENT,
total_expenses DECIMAL(10,2),
total_income DECIMAL(10,2),
total_cash DECIMAL(10,2),
total_bank DECIMAL(10,2),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
7. Create totals Table
sql
Copy code
CREATE TABLE totals (
id INT NOT NULL AUTO_INCREMENT,
cash_amount DECIMAL(10,2),
bank_amount DECIMAL(10,2),
total_amount DECIMAL(10,2),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;