# SƠ ĐỒ CƠ SỞ DỮ LIỆU (DATABASE SCHEMA SPECIFICATION)
# Hệ quản trị: MariaDB 10.6+ / MySQL 8.0+
# Bảng mã mặc định: utf8mb4_unicode_ci / utf8mb4_0900_ai_ci

```sql
-- ==============================================================================
-- 1. BẢNG NGƯỜI DÙNG & XÁC THỰC (USERS, PASSKEYS, SESSIONS)
-- ==============================================================================

-- Bảng người dùng: Thiết kế sẵn trường 'role' để mở rộng nhiều tác giả ở tương lai
CREATE TABLE `users` (
    `id` CHAR(36) NOT NULL COMMENT 'UUID v4 định danh người dùng',
    `username` VARCHAR(50) NOT NULL COMMENT 'Tên đăng nhập duy nhất',
    `email` VARCHAR(191) NOT NULL COMMENT 'Email nhận cảnh báo và khôi phục',
    `password_hash` VARCHAR(255) NOT NULL COMMENT 'Mật khẩu băm bằng Argon2id hoặc Bcrypt',
    `totp_secret` VARCHAR(255) DEFAULT NULL COMMENT 'Khóa bí mật TOTP mã hóa bằng Libsodium',
    `totp_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Trạng thái bật 2FA TOTP',
    `recovery_codes` TEXT DEFAULT NULL COMMENT 'Mảng JSON chứa 10 mã khôi phục đã băm SHA-256',
    `role` ENUM('owner', 'admin', 'editor', 'author') NOT NULL DEFAULT 'owner' COMMENT 'Vai trò hệ thống',
    `status` ENUM('active', 'suspended', 'locked') NOT NULL DEFAULT 'active' COMMENT 'Trạng thái tài khoản',
    `failed_login_count` INT NOT NULL DEFAULT 0 COMMENT 'Số lần đăng nhập sai liên tiếp',
    `locked_until` DATETIME DEFAULT NULL COMMENT 'Thời điểm khóa tạm tài khoản nếu bị brute-force',
    `last_login_at` DATETIME DEFAULT NULL COMMENT 'Thời điểm đăng nhập gần nhất',
    `last_login_ip_hash` CHAR(64) DEFAULT NULL COMMENT 'SHA-256 của IP kèm muối hệ thống',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_users_username` (`username`),
    UNIQUE KEY `uk_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng lưu trữ Passkeys (FIDO2 / WebAuthn)
CREATE TABLE `passkeys` (
    `id` CHAR(36) NOT NULL COMMENT 'UUID v4 của khóa',
    `user_id` CHAR(36) NOT NULL COMMENT 'Khóa ngoại tới users',
    `credential_id` VARCHAR(255) NOT NULL COMMENT 'Credential ID dạng base64url từ thiết bị bảo mật',
    `public_key` TEXT NOT NULL COMMENT 'Public key dạng COSE/PEM',
    `name` VARCHAR(100) NOT NULL COMMENT 'Tên thiết bị (ví dụ: YubiKey 5C, iPhone TouchID)',
    `counter` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Bộ đếm chống clone chữ ký',
    `aaguid` VARCHAR(64) DEFAULT NULL COMMENT 'Định danh nhà sản xuất thiết bị xác thực',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_used_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_passkeys_cred_id` (`credential_id`),
    KEY `idx_passkeys_user` (`user_id`),
    CONSTRAINT `fk_passkeys_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng quản lý phiên đăng nhập trong CSDL (Không lưu phiên ở thư mục /tmp dùng chung của host)
CREATE TABLE `sessions` (
    `id` VARCHAR(128) NOT NULL COMMENT 'Mã băm an toàn của Session ID',
    `user_id` CHAR(36) NOT NULL COMMENT 'ID người dùng',
    `ip_hash` CHAR(64) NOT NULL COMMENT 'SHA-256 của IP kèm muối',
    `user_agent` VARCHAR(255) NOT NULL COMMENT 'Thông tin trình duyệt',
    `payload` MEDIUMTEXT NOT NULL COMMENT 'Dữ liệu phiên đã mã hóa',
    `last_activity` INT UNSIGNED NOT NULL COMMENT 'Thời điểm hoạt động cuối (epoch timestamp)',
    PRIMARY KEY (`id`),
    KEY `idx_sessions_user` (`user_id`),
    KEY `idx_sessions_activity` (`last_activity`),
    CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng theo dõi lịch sử và khóa IP khi đăng nhập sai (Anti-Brute Force)
CREATE TABLE `login_attempts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_hash` CHAR(64) NOT NULL,
    `username_attempted` VARCHAR(100) NOT NULL,
    `attempted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `is_successful` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_attempts_ip_time` (`ip_hash`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ==============================================================================
-- 2. BẢNG PHÂN LOẠI & NỘI DUNG (CATEGORIES, SERIES, TAGS, POSTS)
-- ==============================================================================

-- Bảng chuyên mục 2 cấp (Chuyên mục cha -> Mục con)
CREATE TABLE `categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_id` INT UNSIGNED DEFAULT NULL COMMENT 'NULL nếu là chuyên mục gốc (Cấp 1)',
    `name` VARCHAR(100) NOT NULL COMMENT 'Tên chuyên mục (Tiếng Việt)',
    `slug` VARCHAR(120) NOT NULL COMMENT 'Đường dẫn tĩnh SEO friendly',
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'Mô tả ngắn hiển thị ở chuyên mục',
    `icon_svg` TEXT DEFAULT NULL COMMENT 'Mã SVG nội tuyến đại diện',
    `brand_color` VARCHAR(20) DEFAULT '#2563eb' COMMENT 'Mã màu nhận diện chuyên mục',
    `cover_image` VARCHAR(255) DEFAULT NULL COMMENT 'Đường dẫn ảnh bìa',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Thứ tự sắp xếp hiển thị',
    `seo_title` VARCHAR(150) DEFAULT NULL,
    `seo_description` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_categories_slug` (`slug`),
    KEY `idx_categories_parent` (`parent_id`),
    CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng quản lý chuỗi bài viết (Series)
CREATE TABLE `series` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(220) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `cover_image` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_series_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng thẻ (Tags)
CREATE TABLE `tags` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(60) NOT NULL,
    `slug` VARCHAR(80) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tags_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng bài viết (Posts)
CREATE TABLE `posts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` CHAR(36) NOT NULL COMMENT 'Định danh UUID v4 chống lộ ID tuần tự',
    `author_id` CHAR(36) NOT NULL COMMENT 'Người viết bài',
    `category_id` INT UNSIGNED NOT NULL COMMENT 'Chuyên mục hoặc mục con',
    `series_id` INT UNSIGNED DEFAULT NULL COMMENT 'Chuỗi bài (nếu có)',
    `series_order` INT UNSIGNED DEFAULT NULL COMMENT 'Thứ tự trong series (1, 2, 3...)',
    `title` VARCHAR(255) NOT NULL COMMENT 'Tiêu đề bài viết',
    `slug` VARCHAR(255) NOT NULL COMMENT 'Slug duy nhất trên URL',
    `summary` VARCHAR(500) DEFAULT NULL COMMENT 'Mô tả tóm tắt ngắn cho bài viết',
    `content_markdown` MEDIUMTEXT NOT NULL COMMENT 'Mã nguồn Markdown gốc kèm cú pháp Callout',
    `content_html` MEDIUMTEXT NOT NULL COMMENT 'HTML đã qua parse và làm sạch triệt để',
    `toc_json` TEXT DEFAULT NULL COMMENT 'Mục lục H2/H3 tự động trích xuất dạng JSON',
    `featured_image` VARCHAR(255) DEFAULT NULL COMMENT 'Ảnh đại diện bài viết',
    `reading_time_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Thời gian đọc ước tính',
    `status` ENUM('draft', 'scheduled', 'published', 'trash') NOT NULL DEFAULT 'draft',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Đánh dấu bài ghim/nổi bật',
    `published_at` DATETIME DEFAULT NULL COMMENT 'Thời điểm xuất bản (dùng cho đăng theo lịch)',
    `seo_title` VARCHAR(150) DEFAULT NULL,
    `seo_description` VARCHAR(255) DEFAULT NULL,
    `view_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Số lượt xem lưu tạm',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_posts_uuid` (`uuid`),
    UNIQUE KEY `uk_posts_slug` (`slug`),
    KEY `idx_posts_status_published` (`status`, `published_at`),
    KEY `idx_posts_category` (`category_id`),
    KEY `idx_posts_series` (`series_id`, `series_order`),
    CONSTRAINT `fk_posts_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`),
    CONSTRAINT `fk_posts_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
    CONSTRAINT `fk_posts_series` FOREIGN KEY (`series_id`) REFERENCES `series` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng liên kết nhiều-nhiều giữa bài viết và thẻ
CREATE TABLE `post_tags` (
    `post_id` BIGINT UNSIGNED NOT NULL,
    `tag_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`post_id`, `tag_id`),
    KEY `idx_pt_tag` (`tag_id`),
    CONSTRAINT `fk_pt_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_pt_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng lịch sử phiên bản bài viết (Post Revisions - khôi phục nội dung cũ)
CREATE TABLE `post_revisions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id` BIGINT UNSIGNED NOT NULL,
    `user_id` CHAR(36) NOT NULL COMMENT 'Người thực hiện chỉnh sửa',
    `title` VARCHAR(255) NOT NULL,
    `content_markdown` MEDIUMTEXT NOT NULL,
    `summary` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_revisions_post` (`post_id`, `created_at`),
    CONSTRAINT `fk_revisions_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ==============================================================================
-- 3. BẢNG ĐIỀU HƯỚNG, THIẾT LẬP, MEDIA & HỆ THỐNG
-- ==============================================================================

-- Bảng quản lý chuyển hướng 301 tự động khi đổi slug (Chống gãy liên kết)
CREATE TABLE `redirects` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `source_path` VARCHAR(255) NOT NULL COMMENT 'Đường dẫn cũ cần chuyển',
    `target_path` VARCHAR(255) NOT NULL COMMENT 'Đường dẫn mới',
    `status_code` SMALLINT NOT NULL DEFAULT 301,
    `hits` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_redirects_source` (`source_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng quản lý thư viện hình ảnh và tài nguyên đã re-encode
CREATE TABLE `media` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` CHAR(36) NOT NULL,
    `uploaded_by` CHAR(36) NOT NULL,
    `filename_disk` VARCHAR(150) NOT NULL COMMENT 'Tên file ngẫu nhiên đã băm trên đĩa',
    `original_name` VARCHAR(255) NOT NULL COMMENT 'Tên gốc để tham khảo',
    `mime_type` VARCHAR(50) NOT NULL COMMENT 'image/jpeg, image/png, image/webp',
    `file_size_bytes` INT UNSIGNED NOT NULL,
    `width` INT UNSIGNED NOT NULL,
    `height` INT UNSIGNED NOT NULL,
    `alt_text` VARCHAR(255) NOT NULL COMMENT 'Bắt buộc nhập alt text',
    `variants_json` TEXT DEFAULT NULL COMMENT 'Mảng JSON chứa các kích thước thumbnails (srcset)',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_media_uuid` (`uuid`),
    KEY `idx_media_disk` (`filename_disk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng cấu hình hệ thống (Bao gồm nút nổi mạng xã hội, thông tin site)
CREATE TABLE `settings` (
    `key_name` VARCHAR(80) NOT NULL COMMENT 'Khóa cấu hình',
    `value_text` LONGTEXT DEFAULT NULL COMMENT 'Giá trị cấu hình (chuỗi hoặc JSON)',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng nhật ký kiểm toán bảo mật (Audit Log)
CREATE TABLE `audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` CHAR(36) DEFAULT NULL COMMENT 'NULL nếu là hành động ngoài hệ thống hoặc thất bại',
    `action` VARCHAR(100) NOT NULL COMMENT 'post_create, login_success, passkey_remove...',
    `entity_type` VARCHAR(50) DEFAULT NULL,
    `entity_id` VARCHAR(64) DEFAULT NULL,
    `details_json` TEXT DEFAULT NULL COMMENT 'Chi tiết thay đổi dạng JSON',
    `ip_hash` CHAR(64) NOT NULL COMMENT 'SHA-256 IP kèm muối',
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_action_time` (`action`, `created_at`),
    KEY `idx_audit_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bảng Rate Limiting tầng ứng dụng (Fallback nếu host không có APCu)
CREATE TABLE `rate_limits` (
    `bucket_key` VARCHAR(128) NOT NULL COMMENT 'Mã nhận diện (IP băm + hành động)',
    `tokens` DOUBLE NOT NULL COMMENT 'Số lượng token còn lại',
    `last_refilled_at` INT UNSIGNED NOT NULL COMMENT 'Epoch timestamp lần nạp gần nhất',
    PRIMARY KEY (`bucket_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```
