Introduction to MariaDB & Database Setup

docker --version
# Docker version 24.x.x, build xxxxx

# Pull the official MariaDB image and start a container
docker run --name blogdb \
  -e MARIADB_ROOT_PASSWORD=rootpass \
  -e MARIADB_DATABASE=blogdb \
  -e MARIADB_USER=bloguser \
  -e MARIADB_PASSWORD=blogpass \
  -p 3306:3306 \
  -d mariadb:11

# Check that the container is running
docker ps

# You should see something like:
# CONTAINER ID   IMAGE        STATUS         PORTS                    NAMES
# a1b2c3d4e5f6   mariadb:11   Up 2 minutes   0.0.0.0:3306->3306/tcp   blogdb

# Open a MariaDB shell inside the container
docker exec -it blogdb mariadb -u bloguser -pblogpass blogdb

Welcome to the MariaDB monitor.
MariaDB [blogdb]>

# Stop the container
docker stop blogdb

# Start it again (data is preserved)
docker start blogdb

# View container logs (useful for debugging)
docker logs blogdb

# Remove the container completely (WARNING: data is lost!)
docker rm -f blogdb

# Restart from scratch
docker rm -f blogdb
docker run --name blogdb ... (same command as above)

# docker-compose.yml
version: '3.8'

services:
  mariadb:
    image: mariadb:11
    container_name: blogdb
    environment:
      MARIADB_ROOT_PASSWORD: rootpass
      MARIADB_DATABASE: blogdb
      MARIADB_USER: bloguser
      MARIADB_PASSWORD: blogpass
    ports:
      - "3306:3306"
    volumes:
      # This line persists data even if the container is removed
      - mariadb_data:/var/lib/mysql

volumes:
  mariadb_data:

docker compose up -d

brew install mariadb
brew services start mariadb

# Secure the installation (set root password, remove test database)
mariadb-secure-installation

sudo apt update
sudo apt install mariadb-server

# Start the service
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Secure the installation
sudo mariadb-secure-installation

mariadb -u root -p
# Enter your root password when prompted

# Docker:
docker exec -it blogdb mariadb -u root -prootpass

# Native:
mariadb -u root -p

-- Show all databases on this server
SHOW DATABASES;

-- Output:
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | blogdb             |
-- | information_schema |
-- | mysql              |
-- | performance_schema |
-- | sys                |
-- +--------------------+

-- The ones besides 'blogdb' are system databases — do not touch them.

-- Create a new database (if you did not use the Docker environment variable)
CREATE DATABASE blogdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Switch to the blogdb database (all subsequent commands will run in this database)
USE blogdb;

-- Drop (delete) a database — WARNING: this permanently deletes everything inside!
-- DROP DATABASE blogdb;

-- Create a user (if you did not use Docker environment variables)
CREATE USER 'bloguser'@'%' IDENTIFIED BY 'blogpass';

-- Grant permissions on the blogdb database
GRANT ALL PRIVILEGES ON blogdb.* TO 'bloguser'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

-- Production example: only allow SELECT, INSERT, UPDATE, DELETE (no DROP, no ALTER)
GRANT SELECT, INSERT, UPDATE, DELETE ON blogdb.* TO 'bloguser'@'%';
FLUSH PRIVILEGES;

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show permissions for a specific user
SHOW GRANTS FOR 'bloguser'@'%';

-- Change a user's password
ALTER USER 'bloguser'@'%' IDENTIFIED BY 'newpassword';

-- Delete a user
DROP USER 'bloguser'@'%';

docker exec -it blogdb mariadb -u bloguser -pblogpass blogdb

-- Create a simple table to practice with
CREATE TABLE authors (
    id          BIGINT       AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    bio         TEXT,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single row
INSERT INTO authors (name, email, bio)
VALUES ('Alice Johnson', 'alice@example.com', 'Software engineer and tech blogger');

-- Insert multiple rows at once
INSERT INTO authors (name, email, bio) VALUES
    ('Bob Smith', 'bob@example.com', 'Full-stack developer'),
    ('Carol Davis', 'carol@example.com', 'Database specialist'),
    ('David Wilson', 'david@example.com', NULL);

-- Select all columns from all rows
SELECT * FROM authors;

-- Select specific columns
SELECT name, email FROM authors;

-- Filter with WHERE
SELECT * FROM authors WHERE name = 'Alice Johnson';

-- Pattern matching with LIKE
-- % means "any characters" (similar to * in file systems)
SELECT * FROM authors WHERE email LIKE '%example.com';

-- Multiple conditions
SELECT * FROM authors WHERE bio IS NOT NULL AND name LIKE 'A%';

-- Ordering results
SELECT * FROM authors ORDER BY name ASC;    -- Alphabetical (A → Z)
SELECT * FROM authors ORDER BY created_at DESC;  -- Newest first

-- Limiting results (pagination)
SELECT * FROM authors ORDER BY id LIMIT 10;         -- First 10 rows
SELECT * FROM authors ORDER BY id LIMIT 10 OFFSET 20; -- Skip 20, get next 10

-- Counting rows
SELECT COUNT(*) AS total_authors FROM authors;

-- Aggregate functions
SELECT COUNT(*) AS total, MIN(created_at) AS earliest, MAX(created_at) AS latest
FROM authors;

-- Update a specific row
UPDATE authors
SET bio = 'Senior software engineer and tech blogger',
    email = 'alice.johnson@example.com'
WHERE id = 1;

-- CRITICAL: Always use WHERE with UPDATE!
-- Without WHERE, ALL rows are updated:
-- UPDATE authors SET bio = 'oops';  ← This changes EVERY author's bio!

-- Delete a specific row
DELETE FROM authors WHERE id = 4;

-- CRITICAL: Always use WHERE with DELETE!
-- Without WHERE, ALL rows are deleted:
-- DELETE FROM authors;  ← This deletes EVERY author!

-- Delete with a condition
DELETE FROM authors WHERE bio IS NULL;

CREATE TABLE articles (
    id          BIGINT       AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    content     TEXT         NOT NULL,
    author_id   BIGINT       NOT NULL,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,

    -- Foreign key: author_id must reference a valid id in the authors table
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Insert some articles
INSERT INTO articles (title, content, author_id) VALUES
    ('Getting Started with Spring Boot', 'Spring Boot simplifies...', 1),
    ('Advanced SQL Techniques', 'In this article we explore...', 1),
    ('Introduction to Docker', 'Docker containers provide...', 2),
    ('REST API Best Practices', 'When designing REST APIs...', 3);

-- INNER JOIN: Get articles with their author information.
-- Only returns rows where there is a match in BOTH tables.
SELECT
    a.title       AS article_title,
    a.created_at  AS published_date,
    au.name       AS author_name,
    au.email      AS author_email
FROM articles a
INNER JOIN authors au ON a.author_id = au.id;

+-------------------------------+---------------------+---------------+---------------------------+
| article_title                 | published_date      | author_name   | author_email              |
+-------------------------------+---------------------+---------------+---------------------------+
| Getting Started with Spring   | 2024-01-15 10:30:00 | Alice Johnson | alice.johnson@example.com |
| Advanced SQL Techniques       | 2024-01-15 10:31:00 | Alice Johnson | alice.johnson@example.com |
| Introduction to Docker        | 2024-01-15 10:32:00 | Bob Smith     | bob@example.com           |
| REST API Best Practices       | 2024-01-15 10:33:00 | Carol Davis   | carol@example.com         |
+-------------------------------+---------------------+---------------+---------------------------+

-- LEFT JOIN: Get ALL authors, even those with no articles.
-- Authors without articles will have NULL for article columns.
SELECT au.name, a.title
FROM authors au
LEFT JOIN articles a ON au.id = a.author_id;

-- Output includes authors with no articles:
-- | Alice Johnson | Getting Started with Spring Boot |
-- | Alice Johnson | Advanced SQL Techniques          |
-- | Bob Smith     | Introduction to Docker           |
-- | Carol Davis   | REST API Best Practices          |
-- | David Wilson  | NULL                             |  ← No articles

-- GROUP BY with COUNT: How many articles has each author written?
SELECT au.name, COUNT(a.id) AS article_count
FROM authors au
LEFT JOIN articles a ON au.id = a.author_id
GROUP BY au.id, au.name
ORDER BY article_count DESC;

-- Output:
-- | Alice Johnson | 2 |
-- | Bob Smith     | 1 |
-- | Carol Davis   | 1 |
-- | David Wilson  | 0 |

-- Examples:
name        VARCHAR(100)   -- Human names are rarely longer than 100 chars
email       VARCHAR(255)   -- Email standard allows up to 254 chars
title       VARCHAR(500)   -- Blog post titles
content     TEXT           -- Blog post body
status      VARCHAR(20)    -- DRAFT, PUBLISHED, ARCHIVED
slug        VARCHAR(300)   -- URL-friendly version of the title

-- These are equivalent:
is_active BOOLEAN DEFAULT TRUE
is_active TINYINT(1) DEFAULT 1

-- In SQL, TRUE = 1 and FALSE = 0
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active = 1;  -- Same thing

-- DECIMAL(10, 2) means: up to 10 digits total, with 2 decimal places
-- Maximum value: 99999999.99
price DECIMAL(10, 2)  -- $99,999,999.99

-- NEVER use FLOAT or DOUBLE for money — they cause rounding errors!
-- FLOAT: 0.1 + 0.2 might equal 0.30000000000000004
-- DECIMAL: 0.1 + 0.2 = 0.3 (exact)

metadata JSON

-- Insert JSON data
INSERT INTO posts (title, metadata) VALUES (
    'My Post',
    '{"tags": ["java", "spring"], "readTime": 5}'
);

-- Query JSON fields
SELECT title, JSON_EXTRACT(metadata, '$.tags') AS tags
FROM posts;

CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- Primary key with auto-increment
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

-- No two users can have the same email
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    UNIQUE INDEX idx_users_email (email)
);

-- Alternatively, you can add the UNIQUE constraint inline:
-- email VARCHAR(255) NOT NULL UNIQUE

-- We often query posts by status, so add an index on status
CREATE INDEX idx_posts_status ON posts(status);

-- We often query posts by author_id, so add an index on author_id
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- If we often filter by both status AND category, a composite index helps
CREATE INDEX idx_posts_status_category ON posts(status, category);

-- Show all indexes on a table
SHOW INDEX FROM posts;

-- See how MariaDB executes a query (shows if indexes are used)
EXPLAIN SELECT * FROM posts WHERE status = 'PUBLISHED';

-- ❌ VIOLATES 1NF — tags stored as a comma-separated string
CREATE TABLE posts_bad (
    id BIGINT PRIMARY KEY,
    title VARCHAR(255),
    tags VARCHAR(500)    -- "java,spring,tutorial" — NOT atomic!
);

-- ✅ FOLLOWS 1NF — tags stored in a separate table, one per row
CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255)
);

CREATE TABLE post_tags (
    post_id BIGINT,
    tag     VARCHAR(50),
    PRIMARY KEY (post_id, tag),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- Each tag is its own row:
-- | post_id | tag      |
-- |---------|----------|
-- | 1       | java     |
-- | 1       | spring   |
-- | 1       | tutorial |

-- ❌ VIOLATES 2NF — author_name depends only on author_id, not on the full key
CREATE TABLE articles_bad (
    article_id  BIGINT,
    author_id   BIGINT,
    title       VARCHAR(255),
    author_name VARCHAR(100),   -- Depends only on author_id, not on (article_id, author_id)
    PRIMARY KEY (article_id, author_id)
);

-- ✅ FOLLOWS 2NF — separate the author information into its own table
CREATE TABLE authors (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE articles (
    id        BIGINT AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(255),
    author_id BIGINT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- ❌ VIOLATES 3NF — city and country have a transitive dependency
-- (country depends on city, which depends on the primary key)
CREATE TABLE users_bad (
    id      BIGINT PRIMARY KEY,
    name    VARCHAR(100),
    city    VARCHAR(100),
    country VARCHAR(100)    -- country depends on city, not directly on id
);
-- If "Hanoi" is always in "Vietnam", storing both is redundant.
-- Changing Hanoi's country in one row but not another creates inconsistency.

-- ✅ FOLLOWS 3NF — separate location information
CREATE TABLE cities (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE users (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100),
    city_id BIGINT,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

# Docker
docker exec -it blogdb mariadb -u bloguser -pblogpass blogdb

# Native installation
mariadb -u bloguser -p blogdb

-- Show all tables in the current database
SHOW TABLES;

-- Show the structure of a table
DESCRIBE posts;
-- or
SHOW CREATE TABLE posts;

-- Show the current database
SELECT DATABASE();

-- Show server version
SELECT VERSION();

-- Clear the screen
\! clear

-- Switch to the blogdb database
USE blogdb;

-- ============================================
-- Drop existing tables (if recreating from scratch)
-- Order matters: drop tables with foreign keys first
-- ============================================
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS users;

-- ============================================
-- 1. USERS TABLE
-- ============================================
CREATE TABLE users (
    -- Primary key: auto-incrementing BIGINT
    id              BIGINT          AUTO_INCREMENT PRIMARY KEY,

    -- User's display name, required
    username        VARCHAR(50)     NOT NULL,

    -- Login email, required and unique (no two users can share an email)
    email           VARCHAR(255)    NOT NULL,

    -- Hashed password (we will NEVER store plain text passwords)
    -- BCrypt hashes are 60 characters long
    password_hash   VARCHAR(255)    NOT NULL,

    -- User's full name, optional
    full_name       VARCHAR(100),

    -- Short biography, optional
    bio             TEXT,

    -- User role for authorization: ROLE_USER, ROLE_AUTHOR, ROLE_ADMIN
    role            VARCHAR(20)     NOT NULL DEFAULT 'ROLE_USER',

    -- Soft delete: instead of removing rows, we mark them as inactive
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,

    -- Timestamps for auditing
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,

    -- Unique constraints (these also create indexes automatically)
    UNIQUE INDEX idx_users_username (username),
    UNIQUE INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. CATEGORIES TABLE
-- ============================================
CREATE TABLE categories (
    id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL,
    description TEXT,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE INDEX idx_categories_name (name),
    UNIQUE INDEX idx_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. TAGS TABLE
-- ============================================
CREATE TABLE tags (
    id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50)     NOT NULL,
    slug        VARCHAR(50)     NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE INDEX idx_tags_name (name),
    UNIQUE INDEX idx_tags_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. POSTS TABLE
-- ============================================
CREATE TABLE posts (
    id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(500)    NOT NULL,
    slug            VARCHAR(500)    NOT NULL,
    content         TEXT            NOT NULL,
    excerpt         VARCHAR(1000),
    status          VARCHAR(20)     NOT NULL DEFAULT 'DRAFT',

    -- Foreign keys: link posts to users and categories
    author_id       BIGINT          NOT NULL,
    category_id     BIGINT,

    -- SEO fields
    meta_title      VARCHAR(200),
    meta_description VARCHAR(500),

    -- Timestamps
    published_at    TIMESTAMP       NULL,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,

    -- Unique constraint on slug (for URL routing)
    UNIQUE INDEX idx_posts_slug (slug),

    -- Index on status (frequently filtered)
    INDEX idx_posts_status (status),

    -- Index on author_id (frequently joined and filtered)
    INDEX idx_posts_author_id (author_id),

    -- Index on category_id (frequently joined and filtered)
    INDEX idx_posts_category_id (category_id),

    -- Foreign key constraints
    -- RESTRICT means: do not allow deleting a user who has posts
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,

    -- SET NULL means: if a category is deleted, set category_id to NULL
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. COMMENTS TABLE
-- ============================================
CREATE TABLE comments (
    id          BIGINT      AUTO_INCREMENT PRIMARY KEY,
    content     TEXT        NOT NULL,

    -- Foreign keys
    post_id     BIGINT      NOT NULL,
    author_id   BIGINT      NOT NULL,

    -- Self-referencing foreign key for nested replies
    -- A comment can be a reply to another comment
    parent_id   BIGINT      NULL,

    -- Timestamps
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
                            ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_comments_post_id (post_id),
    INDEX idx_comments_author_id (author_id),
    INDEX idx_comments_parent_id (parent_id),

    -- CASCADE: if a post is deleted, all its comments are deleted too
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. POST_TAGS TABLE (Many-to-Many junction table)
-- ============================================
-- This table exists solely to represent the many-to-many relationship
-- between posts and tags. It has no columns of its own except the
-- two foreign keys, which together form the primary key.
CREATE TABLE post_tags (
    post_id     BIGINT NOT NULL,
    tag_id      BIGINT NOT NULL,

    -- Composite primary key: the combination of post_id + tag_id must be unique
    -- This prevents duplicate tag assignments
    PRIMARY KEY (post_id, tag_id),

    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert users
INSERT INTO users (username, email, password_hash, full_name, bio, role) VALUES
    ('alice', 'alice@example.com', '$2a$10$dummyhash1', 'Alice Johnson', 'Senior developer and blogger', 'ROLE_ADMIN'),
    ('bob', 'bob@example.com', '$2a$10$dummyhash2', 'Bob Smith', 'Full-stack developer', 'ROLE_AUTHOR'),
    ('carol', 'carol@example.com', '$2a$10$dummyhash3', 'Carol Davis', 'Tech enthusiast', 'ROLE_USER');

-- Insert categories
INSERT INTO categories (name, slug, description) VALUES
    ('Tutorial', 'tutorial', 'Step-by-step programming tutorials'),
    ('News', 'news', 'Latest tech news and announcements'),
    ('Opinion', 'opinion', 'Personal thoughts and perspectives');

-- Insert tags
INSERT INTO tags (name, slug) VALUES
    ('Java', 'java'),
    ('Spring Boot', 'spring-boot'),
    ('Database', 'database'),
    ('Docker', 'docker'),
    ('REST API', 'rest-api');

-- Insert posts
INSERT INTO posts (title, slug, content, excerpt, status, author_id, category_id, published_at) VALUES
    ('Getting Started with Spring Boot',
     'getting-started-with-spring-boot',
     'Spring Boot makes it easy to create stand-alone, production-grade Spring based applications...',
     'A beginner-friendly guide to Spring Boot',
     'PUBLISHED', 1, 1, NOW()),
    ('Understanding REST APIs',
     'understanding-rest-apis',
     'REST (Representational State Transfer) is an architectural style for designing networked applications...',
     'Learn the fundamentals of RESTful API design',
     'PUBLISHED', 1, 1, NOW()),
    ('Docker for Developers',
     'docker-for-developers',
     'Docker containers provide a consistent environment for development and deployment...',
     'A practical guide to Docker',
     'DRAFT', 2, 1, NULL),
    ('The Future of Java',
     'the-future-of-java',
     'Java continues to evolve with regular releases every six months...',
     'Where is Java heading?',
     'PUBLISHED', 2, 2, NOW());

-- Assign tags to posts (many-to-many)
INSERT INTO post_tags (post_id, tag_id) VALUES
    (1, 1),    -- "Getting Started" tagged with "Java"
    (1, 2),    -- "Getting Started" tagged with "Spring Boot"
    (2, 5),    -- "Understanding REST" tagged with "REST API"
    (2, 2),    -- "Understanding REST" tagged with "Spring Boot"
    (3, 4),    -- "Docker for Developers" tagged with "Docker"
    (4, 1);    -- "The Future of Java" tagged with "Java"

-- Insert comments
INSERT INTO comments (content, post_id, author_id, parent_id) VALUES
    ('Great article! Very helpful for beginners.', 1, 3, NULL),
    ('Thanks Carol! Glad you found it useful.', 1, 1, 1),
    ('Could you cover Spring Security next?', 1, 2, NULL),
    ('Excellent overview of REST principles.', 2, 3, NULL);

-- Show all tables
SHOW TABLES;

-- Verify table structures
DESCRIBE users;
DESCRIBE posts;
DESCRIBE comments;
DESCRIBE post_tags;

-- Test the relationships with joins

-- Get all posts with their author name and category
SELECT p.title, u.full_name AS author, c.name AS category, p.status
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY p.created_at DESC;

-- Get all tags for each post
SELECT p.title, GROUP_CONCAT(t.name SEPARATOR ', ') AS tags
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

-- Get comments with their authors and reply structure
SELECT
    c.content,
    u.username AS commenter,
    c.parent_id AS reply_to,
    c.created_at
FROM comments c
JOIN users u ON c.author_id = u.id
WHERE c.post_id = 1
ORDER BY c.created_at;

-- Count posts per category
SELECT c.name AS category, COUNT(p.id) AS post_count
FROM categories c
LEFT JOIN posts p ON c.id = p.category_id
GROUP BY c.id, c.name;

-- Count posts per author
SELECT u.full_name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.full_name
ORDER BY post_count DESC;