-- 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;