-- Drop tables if they exist (to reset)

DROP TABLE IF EXISTS api_keys;

DROP TABLE IF EXISTS comments;

DROP TABLE IF EXISTS post_tags;

DROP TABLE IF EXISTS posts;

DROP TABLE IF EXISTS tags;

DROP TABLE IF EXISTS categories;

DROP TABLE IF EXISTS users;

-- Users Table

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

email VARCHAR(100) NOT NULL UNIQUE,

password_hash VARCHAR(255) NOT NULL,

first_name VARCHAR(50),

last_name VARCHAR(50),

is_active BOOLEAN DEFAULT TRUE,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

-- Categories Table

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL UNIQUE,

description TEXT,

color CHAR(7), -- hex color like #3498db

created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);

-- Tags Table

CREATE TABLE tags (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL UNIQUE,

slug VARCHAR(100) NOT NULL UNIQUE,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);

-- Posts Table

CREATE TABLE posts (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

content TEXT NOT NULL,

excerpt TEXT,

author_id INT NOT NULL,

category_id INT NULL,

status ENUM('draft','published','archived') DEFAULT 'draft',

view_count INT DEFAULT 0,

is_featured BOOLEAN DEFAULT FALSE,

published_at DATETIME NULL,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,

FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL

);

-- Post_Tags (many-to-many relation)

CREATE TABLE post_tags (

post_id INT NOT NULL,

tag_id INT NOT NULL,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

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

);

-- Comments Table

CREATE TABLE comments (

id INT AUTO_INCREMENT PRIMARY KEY,

post_id INT NOT NULL,

user_id INT NOT NULL,

content TEXT NOT NULL,

parent_comment_id INT NULL,

is_approved BOOLEAN DEFAULT TRUE,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE

);

-- API Keys Table

CREATE TABLE api_keys (

id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT NOT NULL,

key_hash VARCHAR(255) NOT NULL,

name VARCHAR(100) NOT NULL,

permissions JSON NOT NULL,

rate_limit INT DEFAULT 1000,

is_active BOOLEAN DEFAULT TRUE,

expires_at DATETIME,

last_used_at DATETIME NULL,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

);