database.sql

-- BlueCMS Database Schema
-- Run this file to set up the database

CREATE DATABASE IF NOT EXISTS bluecms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bluecms;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','user') DEFAULT 'user',
    version ENUM('basic','pro') DEFAULT 'basic',
    blog_title VARCHAR(150) DEFAULT NULL,
    blog_slug VARCHAR(100) DEFAULT NULL UNIQUE,
    avatar VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    blog_suspended TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Languages table
CREATE TABLE IF NOT EXISTS languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL UNIQUE,
    flag VARCHAR(255) DEFAULT NULL,
    is_default TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    slug VARCHAR(150) NOT NULL,
    thumbnail VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Category translations
CREATE TABLE IF NOT EXISTS category_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    language_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    UNIQUE KEY uq_cat_lang (category_id, language_id),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tags table
CREATE TABLE IF NOT EXISTS tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(110) NOT NULL,
    UNIQUE KEY uq_user_tag (user_id, slug),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Posts table
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category_id INT DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(270) NOT NULL,
    content LONGTEXT,
    thumbnail VARCHAR(255) DEFAULT NULL,
    status ENUM('draft','published') DEFAULT 'published',
    views INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_slug (user_id, slug),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Post tags pivot
CREATE TABLE IF NOT EXISTS post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    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;

-- Advertisements table
CREATE TABLE IF NOT EXISTS advertisements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('banner','text') NOT NULL,
    title VARCHAR(200) DEFAULT NULL,
    content TEXT DEFAULT NULL,
    image VARCHAR(255) DEFAULT NULL,
    link_url VARCHAR(500) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    position ENUM('header','sidebar','footer','in-content') DEFAULT 'sidebar',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default admin user (password: admin123!!)
INSERT INTO users (username, email, password, role, version, blog_title, blog_slug, is_active)
VALUES ('admin', 'admin@admin.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'pro', 'Admin Blog', 'admin', 1)
ON DUPLICATE KEY UPDATE id=id;

-- Insert default language
INSERT INTO languages (name, code, is_default, is_active)
VALUES ('English', 'en', 1, 1)
ON DUPLICATE KEY UPDATE id=id;

-- Sample ads
INSERT INTO advertisements (type, title, content, link_url, is_active, position) VALUES
('text', 'Upgrade to Pro', 'Remove ads and get more features! <a href="/upgrade">Upgrade Now</a>', '/upgrade', 1, 'sidebar'),
('banner', 'BlueCMS Pro', NULL, 'https://example.com', 1, 'header');

-- ============================================================
-- NEW TABLES: Views tracking, Domain parking, Stripe settings
-- ============================================================

-- Post view logs (daily aggregated)
CREATE TABLE IF NOT EXISTS post_views_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    view_date DATE NOT NULL,
    count INT DEFAULT 1,
    UNIQUE KEY uq_post_date (post_id, view_date),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Blog view logs (daily aggregated per user/blog)
CREATE TABLE IF NOT EXISTS blog_views_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    view_date DATE NOT NULL,
    count INT DEFAULT 1,
    UNIQUE KEY uq_blog_date (user_id, view_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Parked domains
CREATE TABLE IF NOT EXISTS parked_domains (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    domain VARCHAR(255) NOT NULL UNIQUE,
    status ENUM('pending','active','rejected') DEFAULT 'pending',
    verified TINYINT(1) DEFAULT 0,
    verify_token VARCHAR(64) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Site settings (key-value store for Stripe keys, etc.)
CREATE TABLE IF NOT EXISTS site_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default settings
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('stripe_publishable_key', ''),
('stripe_secret_key', ''),
('stripe_webhook_secret', ''),
('stripe_pro_price_id', ''),
('stripe_enabled', '0'),
('pro_price_monthly', '9.00'),
('pro_price_label', '$9/month');

-- Payments log
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stripe_session_id VARCHAR(255) DEFAULT NULL,
    stripe_payment_intent VARCHAR(255) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'usd',
    status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
    plan VARCHAR(50) DEFAULT 'pro',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- v4: Blog customization, user ads, profile fields
-- ============================================================

-- Blog customization per user
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS avatar VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS cover VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS bio TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS blog_primary_color VARCHAR(7) DEFAULT '#3e729a',
  ADD COLUMN IF NOT EXISTS blog_layout JSON DEFAULT NULL;

-- User's own ads
CREATE TABLE IF NOT EXISTS user_ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('banner','text') DEFAULT 'text',
    title VARCHAR(200) DEFAULT NULL,
    content TEXT DEFAULT NULL,
    image VARCHAR(255) DEFAULT NULL,
    link_url VARCHAR(500) DEFAULT NULL,
    position ENUM('header','sidebar','footer','in-content') DEFAULT 'sidebar',
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Admin setting for nameservers
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('ns1', 'ns1.bluecms.org'),
('ns2', 'ns2.bluecms.org');

-- ============================================================
-- v5: Security hardening + Reactions system
-- ============================================================

-- Post reactions
CREATE TABLE IF NOT EXISTS post_reactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    reactor_ip VARCHAR(64) NOT NULL,
    session_id VARCHAR(128) NOT NULL,
    reaction ENUM('like','dislike','love','fire','laugh','sad') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_reaction (post_id, session_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- v6: Comments, Archives, Related Posts, reCAPTCHA per domain
-- ============================================================

-- Comments table
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    parent_id INT DEFAULT NULL,
    author_name VARCHAR(100) NOT NULL,
    author_email VARCHAR(150) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('pending','approved','spam') DEFAULT 'pending',
    user_id INT DEFAULT NULL,
    ip VARCHAR(64) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Per-domain reCAPTCHA settings (for parked domains)
ALTER TABLE parked_domains
  ADD COLUMN IF NOT EXISTS recaptcha_site_key VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS recaptcha_secret_key VARCHAR(255) DEFAULT NULL;

-- Site-level reCAPTCHA (admin global)
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('recaptcha_site_key',   ''),
('recaptcha_secret_key', ''),
('recaptcha_enabled',    '0'),
('comments_auto_approve','0');