42 lines
1.9 KiB
SQL
42 lines
1.9 KiB
SQL
-- Create categories table
|
|
CREATE TABLE IF NOT EXISTS `categories` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Create users table
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Create accounts table
|
|
CREATE TABLE IF NOT EXISTS `accounts` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`user_id` INT,
|
|
`initial_balance` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
|
|
`currency` ENUM('USD', 'EUR', 'COP') NOT NULL,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Step 1: Add new columns to 'expenses' table without dropping old ones yet.
|
|
ALTER TABLE `expenses`
|
|
ADD COLUMN `category_id` INT NULL AFTER `receipt_path`,
|
|
ADD COLUMN `user_id` INT NULL AFTER `category_id`,
|
|
ADD COLUMN `account_id` INT NULL AFTER `user_id`,
|
|
ADD COLUMN `expense_type` ENUM('expense', 'income', 'transfer') NOT NULL DEFAULT 'expense' AFTER `amount`,
|
|
ADD COLUMN `split_type` ENUM('equally', 'parts', 'amounts', 'none') NOT NULL DEFAULT 'none' AFTER `expense_type`;
|
|
|
|
-- Step 2: Add foreign key constraints
|
|
-- Note: We'll add constraints in a separate step after data migration to avoid issues with existing data.
|
|
-- ALTER TABLE `expenses`
|
|
-- ADD CONSTRAINT `fk_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
|
|
-- ADD CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
|
-- ADD CONSTRAINT `fk_account` FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL;
|
|
|
|
-- Step 3: Modify the currency column
|
|
ALTER TABLE `expenses`
|
|
MODIFY COLUMN `currency` ENUM('USD', 'EUR', 'COP') NOT NULL;
|