-- Sample MySQL Database Schema and Data
-- This file demonstrates both CREATE TABLE statements (schema) and INSERT statements (data)
-- You can use different parts of this file depending on the conversion mode you select

-- Schema Example: Users table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  role ENUM('admin', 'moderator', 'user') DEFAULT 'user'
);

-- Schema Example: Posts table
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
  published_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Data Example: Insert users
INSERT INTO users (username, email, password, first_name, last_name, is_active, role) VALUES 
('johndoe', 'john.doe@example.com', 'hashed_password_123', 'John', 'Doe', TRUE, 'admin'),
('janesmith', 'jane.smith@example.com', 'hashed_password_456', 'Jane', 'Smith', TRUE, 'user'),
('bobwilson', 'bob.wilson@example.com', 'hashed_password_789', 'Bob', 'Wilson', FALSE, 'moderator');

-- Data Example: Insert posts
INSERT INTO posts (user_id, title, content, status, published_at) VALUES 
(1, 'Welcome to Our Blog', 'This is the first post on our new blog platform.', 'published', '2023-01-15 10:00:00'),
(2, 'Getting Started Guide', 'Here is how to get started with our platform.', 'published', '2023-01-16 14:30:00'),
(1, 'Draft Article', 'This article is still being worked on.', 'draft', NULL); 