database_render/config/schema.sql

-- Database Render Application - Sample Database Schema
-- This file contains sample database tables for testing the application

-- Posts table (blog posts/articles)
CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    category VARCHAR(50),
    tags VARCHAR(255),
    author_id INTEGER,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    password_hash VARCHAR(255),
    avatar_url VARCHAR(255),
    bio TEXT,
    status VARCHAR(20) DEFAULT 'active',
    role VARCHAR(20) DEFAULT 'user',
    last_login DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tags table
CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    color VARCHAR(7),
    description TEXT,
    usage_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Post-Tag relationship table
CREATE TABLE IF NOT EXISTS post_tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
    UNIQUE(post_id, tag_id)
);

-- Comments table
CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL,
    user_id INTEGER,
    author_name VARCHAR(100),
    author_email VARCHAR(255),
    content TEXT NOT NULL,
    parent_id INTEGER,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- Media/Attachments table
CREATE TABLE IF NOT EXISTS media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255),
    file_path VARCHAR(500),
    file_size INTEGER,
    mime_type VARCHAR(100),
    alt_text VARCHAR(255),
    title VARCHAR(255),
    description TEXT,
    uploaded_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Sample data insertion
-- Insert sample users
INSERT OR IGNORE INTO users (username, email, full_name, bio, role) VALUES
('admin', 'admin@example.com', 'System Administrator', 'System administrator account', 'admin'),
('john_doe', 'john@example.com', 'John Doe', 'Technology enthusiast and blogger', 'user'),
('jane_smith', 'jane@example.com', 'Jane Smith', 'Content creator and writer', 'user'),
('bob_wilson', 'bob@example.com', 'Bob Wilson', 'Software developer and tech reviewer', 'user');

-- Insert sample categories
INSERT OR IGNORE INTO categories (name, slug, description, color) VALUES
('Technology', 'technology', 'Latest technology trends and developments', '#3b82f6'),
('Programming', 'programming', 'Programming tutorials and best practices', '#8b5cf6'),
('Web Development', 'web-development', 'Web development articles and tutorials', '#10b981'),
('DevOps', 'devops', 'DevOps practices and tools', '#f59e0b'),
('Data Science', 'data-science', 'Data science and machine learning', '#ef4444'),
('Lifestyle', 'lifestyle', 'Personal development and lifestyle', '#ec4899');

-- Insert sample tags
INSERT OR IGNORE INTO tags (name, slug, color, description) VALUES
('Go', 'go', '#00add8', 'Go programming language'),
('Python', 'python', '#3776ab', 'Python programming language'),
('JavaScript', 'javascript', '#f7df1e', 'JavaScript programming language'),
('Docker', 'docker', '#2496ed', 'Docker containerization'),
('Kubernetes', 'kubernetes', '#326ce5', 'Kubernetes orchestration'),
('React', 'react', '#61dafb', 'React framework'),
('Vue.js', 'vuejs', '#4fc08d', 'Vue.js framework'),
('Database', 'database', '#4479a1', 'Database technologies'),
('API', 'api', '#68d391', 'API development'),
('Security', 'security', '#f56565', 'Security practices');

-- Insert sample posts
INSERT OR IGNORE INTO posts (title, content, category, tags, author_id, status) VALUES
('Getting Started with Go and Fiber Framework', '# Getting Started with Go and Fiber

This is a comprehensive guide to building web applications with Go and the Fiber framework.

## Introduction

Fiber is an Express.js inspired web framework written in Go. It provides a robust set of features for building web applications and APIs.

## Installation

```bash
go get github.com/gofiber/fiber/v2
```

## Basic Usage

```go
package main

import "github.com/gofiber/fiber/v2"

func main() {
    app := fiber.New()
    
    app.Get("/", func(c *fiber.Ctx) error {
        return c.SendString("Hello, World!")
    })
    
    app.Listen(":3000")
}
```

## Conclusion

Fiber provides an excellent foundation for building high-performance web applications in Go.', 'Programming', 'Go,Web Development,API', 2, 'published'),

('Modern Database Design Best Practices', '# Modern Database Design Best Practices

Learn the essential principles of designing scalable and maintainable databases.

## Key Principles

1. **Normalization**: Reduce data redundancy
2. **Indexing**: Improve query performance
3. **Data Types**: Choose appropriate data types
4. **Constraints**: Ensure data integrity

## Example Schema

Here''s an example of a well-designed user table:

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
```

## Performance Tips

- Use appropriate indexes
- Consider partitioning for large tables
- Regular database maintenance', 'Technology', 'Database,Design,Best Practices', 3, 'published'),

('Container Orchestration with Kubernetes', '# Container Orchestration with Kubernetes

Master the art of container orchestration with Kubernetes.

## What is Kubernetes?

Kubernetes is an open-source platform designed to automate deploying, scaling, and operating application containers.

## Key Features

- **Service Discovery**: Automatic container discovery
- **Load Balancing**: Distribute traffic across containers
- **Storage Orchestration**: Mount storage systems
- **Self-Healing**: Automatic container restarts
- **Secret Management**: Secure sensitive data

## Getting Started

```bash
# Start a local cluster
minikube start

# Deploy an application
kubectl create deployment hello-node --image=k8s.gcr.io/echoserver:1.4

# Expose the deployment
kubectl expose deployment hello-node --type=LoadBalancer --port=8080
```

## Best Practices

- Use namespaces for organization
- Implement health checks
- Monitor resource usage
- Use ConfigMaps for configuration', 'DevOps', 'Kubernetes,Docker,Containerization', 4, 'published'),

('Building RESTful APIs with Best Practices', '# Building RESTful APIs with Best Practices

Create robust and scalable RESTful APIs following industry standards.

## REST Principles

- **Stateless**: Each request contains all necessary information
- **Resource-Based**: URLs represent resources
- **HTTP Methods**: Use appropriate HTTP verbs (GET, POST, PUT, DELETE)
- **Status Codes**: Return appropriate HTTP status codes

## API Design Guidelines

### 1. Version Your API
```
/api/v1/users
/api/v2/users
```

### 2. Use Plural Nouns
```
GET /users
POST /users
GET /users/123
```

### 3. Filtering and Pagination
```
GET /users?page=1&limit=10&sort=name&order=asc
```

### 4. Error Handling
```json
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Email is required",
    "details": []
  }
}
```

## Security Considerations

- Use HTTPS
- Implement rate limiting
- Validate input data
- Use authentication and authorization', 'Programming', 'API,REST,Security', 2, 'published'),

('React State Management in 2024', '# React State Management in 2024

Explore modern state management solutions for React applications.

## State Management Options

### 1. React Context API
Built-in solution for sharing state across components.

### 2. Redux Toolkit
Predictable state container with great developer tools.

### 3. Zustand
Lightweight and simple state management.

### 4. Jotai
Atomic approach to state management.

## Choosing the Right Solution

| Solution | Best For | Bundle Size |
|----------|----------|-------------|
| Context | Simple apps | Small |
| Redux | Complex apps | Large |
| Zustand | Medium apps | Small |
| Jotai | Atomic state | Small |

## Code Example

```javascript
// Using Zustand
import { create } from ''zustand''

const useStore = create((set) => ({
  count: 0,
  increment: () => set((state) => ({ count: state.count + 1 })),
}))
```

## Performance Tips

- Use React.memo for expensive components
- Implement proper memoization
- Consider code splitting for large state', 'Web Development', 'React,JavaScript,State Management', 3, 'published');

-- Insert sample comments
INSERT OR IGNORE INTO comments (post_id, user_id, content, status) VALUES
(1, 3, 'Great tutorial! Very helpful for getting started with Fiber.', 'approved'),
(1, 4, 'Thanks for sharing this. The examples are really clear.', 'approved'),
(2, 2, 'Excellent overview of database design principles!', 'approved'),
(3, 1, 'Kubernetes can be overwhelming, but this guide makes it approachable.', 'approved'),
(4, 3, 'RESTful API best practices are so important. Great article!', 'approved');

-- Insert post-tag relationships
INSERT OR IGNORE INTO post_tags (post_id, tag_id) VALUES
(1, 1), (1, 9), (1, 10),
(2, 8), (2, 9),
(3, 4), (3, 5),
(4, 9), (4, 10),
(5, 6), (5, 7), (5, 10);