migrate.sql

-- BlueCMS Migration Script
-- Run this in phpMyAdmin or MySQL if you upgraded from a previous version
-- Safe to run multiple times (uses IF NOT EXISTS / IGNORE)

USE bluecms;

-- Add missing columns to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS cover VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_primary_color VARCHAR(7) DEFAULT '#3e729a';
ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_layout JSON DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_theme VARCHAR(20) DEFAULT 'default';
ALTER TABLE users ADD COLUMN IF NOT EXISTS dark_mode TINYINT(1) DEFAULT 0;

-- Add missing columns to parked_domains
ALTER TABLE parked_domains ADD COLUMN IF NOT EXISTS recaptcha_site_key VARCHAR(255) DEFAULT NULL;
ALTER TABLE parked_domains ADD COLUMN IF NOT EXISTS recaptcha_secret_key VARCHAR(255) DEFAULT NULL;

-- Blog team members
CREATE TABLE IF NOT EXISTS blog_team (
    id INT AUTO_INCREMENT PRIMARY KEY,
    blog_owner_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('admin','editor') DEFAULT 'editor',
    invited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_team (blog_owner_id, user_id),
    FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Email subscriptions
CREATE TABLE IF NOT EXISTS email_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    blog_owner_id INT NOT NULL,
    email VARCHAR(150) NOT NULL,
    name VARCHAR(100) DEFAULT NULL,
    confirmed TINYINT(1) DEFAULT 0,
    confirm_token VARCHAR(64) DEFAULT NULL,
    unsubscribe_token VARCHAR(64) DEFAULT NULL,
    subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sub (blog_owner_id, email),
    FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Email campaigns
CREATE TABLE IF NOT EXISTS email_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    blog_owner_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    status ENUM('draft','sent') DEFAULT 'draft',
    sent_count INT DEFAULT 0,
    sent_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 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 DEFAULT CHARSET=utf8mb4;

-- Comments
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 DEFAULT CHARSET=utf8mb4;

-- Post view logs
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 DEFAULT CHARSET=utf8mb4;

-- Blog view logs
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 DEFAULT CHARSET=utf8mb4;

-- Payments
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 DEFAULT CHARSET=utf8mb4;

-- User 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 DEFAULT CHARSET=utf8mb4;

-- Site settings
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 DEFAULT CHARSET=utf8mb4;

-- 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'),('recaptcha_site_key',''),('recaptcha_secret_key',''),
('recaptcha_enabled','0'),('comments_auto_approve','0'),('ns1','ns1.bluecms.org'),
('ns2','ns2.bluecms.org'),('smtp_host',''),('smtp_port','587'),('smtp_user',''),
('smtp_pass',''),('smtp_from_name',''),('smtp_from_email',''),('smtp_enabled','0');

-- Default language if missing
INSERT IGNORE INTO languages (name, code, is_default, is_active) VALUES ('English', 'en', 1, 1);

SELECT 'Migration complete!' AS status;

-- Plugins system
CREATE TABLE IF NOT EXISTS plugins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    version VARCHAR(20) DEFAULT '1.0.0',
    author VARCHAR(100) DEFAULT NULL,
    author_url VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 0,
    installed_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plugin_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plugin_slug VARCHAR(100) NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT DEFAULT NULL,
    UNIQUE KEY uq_plugin_setting (plugin_slug, setting_key),
    FOREIGN KEY (plugin_slug) REFERENCES plugins(slug) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Reserved usernames (admin can block these from registration)
CREATE TABLE IF NOT EXISTS reserved_usernames (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    reason VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default reserved usernames (system paths + common ones)
INSERT IGNORE INTO reserved_usernames (username, reason) VALUES
('admin','System path'),('dashboard','System path'),('api','System path'),
('assets','System path'),('includes','System path'),('plugins','System path'),
('uploads','System path'),('blog','System path'),('sitemap','System path'),
('login','System path'),('logout','System path'),('register','System path'),
('setup','System path'),('install','System path'),('upgrade','System path'),
('subscribe','System path'),('unsubscribe','System path'),('stripe','System path'),
('blue-admin','System path'),('www','Reserved'),('mail','Reserved'),
('ftp','Reserved'),('smtp','Reserved'),('pop','Reserved'),('imap','Reserved'),
('support','Reserved'),('help','Reserved'),('info','Reserved'),('contact','Reserved'),
('about','Reserved'),('home','Reserved'),('index','Reserved');

-- Post translations (multi-language posts)
CREATE TABLE IF NOT EXISTS post_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    language_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    slug VARCHAR(270) NOT NULL,
    UNIQUE KEY uq_post_lang (post_id, language_id),
    UNIQUE KEY uq_post_slug_lang (language_id, slug, post_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add admin_path setting for custom admin URL
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('admin_path', 'blue-admin'),
('default_language', 'en'),
('site_languages', 'en');