-- Database Setup for Finance App
CREATE DATABASE IF NOT EXISTS finance_app;
USE finance_app;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transactions Table
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, date),
    INDEX idx_type (type)
);

-- Budgets Table
CREATE TABLE IF NOT EXISTS budgets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    planned_amount DECIMAL(15,2) NOT NULL,
    spent_amount DECIMAL(15,2) DEFAULT 0,
    month VARCHAR(7) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_category_month (user_id, category, month),
    INDEX idx_user_month (user_id, month)
);

-- Create trigger to update budget spent amount
DELIMITER $$

CREATE TRIGGER update_budget_after_transaction_insert
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    IF NEW.type = 'expense' THEN
        UPDATE budgets 
        SET spent_amount = (
            SELECT COALESCE(SUM(amount), 0)
            FROM transactions
            WHERE user_id = NEW.user_id
            AND category = NEW.category
            AND type = 'expense'
            AND DATE_FORMAT(date, '%Y-%m') = month
        )
        WHERE user_id = NEW.user_id
        AND category = NEW.category
        AND month = DATE_FORMAT(NEW.date, '%Y-%m');
    END IF;
END$$

CREATE TRIGGER update_budget_after_transaction_delete
AFTER DELETE ON transactions
FOR EACH ROW
BEGIN
    IF OLD.type = 'expense' THEN
        UPDATE budgets 
        SET spent_amount = (
            SELECT COALESCE(SUM(amount), 0)
            FROM transactions
            WHERE user_id = OLD.user_id
            AND category = OLD.category
            AND type = 'expense'
            AND DATE_FORMAT(date, '%Y-%m') = month
        )
        WHERE user_id = OLD.user_id
        AND category = OLD.category
        AND month = DATE_FORMAT(OLD.date, '%Y-%m');
    END IF;
END$$

DELIMITER ;

-- Insert sample user (password: password123)
INSERT INTO users (name, email, password) VALUES 
('Demo User', 'demo@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
