-- Blog Database Schema
-- 
-- This creates the complete database structure for the blog system
-- including users, posts, and categories tables with all necessary
-- relationships and constraints.

CREATE DATABASE IF NOT EXISTS yujbjplf_blog;
USE yujbjplf_blog;

-- Users table for admin authentication
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'author') DEFAULT 'author',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories table for organizing posts
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table for blog content
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content LONGTEXT NOT NULL,
    excerpt TEXT,
    category_id INT,
    author_id INT,
    status ENUM('draft', 'published') DEFAULT 'draft',
    featured_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert default admin user (password: admin123)
INSERT INTO users (username, email, password, role) VALUES 
('admin', 'admin@blog.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- Insert default categories
INSERT INTO categories (name, slug, description) VALUES 
('News', 'news', 'Latest news and updates'),
('Articles', 'articles', 'In-depth articles and analysis'),
('Journals', 'journals', 'Personal journals and experiences');

-- Insert sample posts
INSERT INTO posts (title, slug, content, excerpt, category_id, author_id, status) VALUES 
('Welcome to Our Blog', 'welcome-to-our-blog', '<p>Welcome to our new blog system! This is where we\'ll share news, articles, and journal entries.</p><p>Stay tuned for more exciting content.</p>', 'Welcome to our new blog system where we share news, articles, and journals.', 1, 1, 'published'),
('Getting Started with PHP', 'getting-started-with-php', '<p>PHP is a powerful server-side scripting language that\'s perfect for web development.</p><p>In this article, we\'ll explore the basics of PHP programming.</p>', 'Learn the fundamentals of PHP programming in this comprehensive guide.', 2, 1, 'published');