-- Create the database
CREATE DATABASE IF NOT EXISTS finplanner;
USE finplanner;

CREATE TABLE blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    author_id INT,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE questionnaires (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(200),
    data JSON,
    status ENUM('draft', 'submitted', 'reviewed') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create sample blog posts
INSERT INTO blog_posts (title, content, author_id, image_url) VALUES 
('Understanding Retirement Planning', 'Retirement planning is the process of determining retirement income goals and the actions and decisions necessary to achieve those goals. Retirement planning includes identifying sources of income, estimating expenses, implementing a savings program, and managing assets and risk...', 1, '/static/images/retirement.jpg'),
('Investment Strategies for Beginners', 'When you''re new to investing, it can be overwhelming to know where to start. This guide breaks down the fundamentals of investing for beginners...', 1, '/static/images/investment.jpg'),
('The Importance of Emergency Funds', 'An emergency fund is a financial safety net that everybody should have. In this post, we discuss why emergency funds are crucial and how to build one step by step...', 1, '/static/images/emergency-fund.jpg');
