-- Blog System Schema for SEO Content Marketing

-- Blog posts table
CREATE TABLE IF NOT EXISTS blog_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    excerpt TEXT,
    content TEXT NOT NULL,
    featured_image TEXT,
    meta_title TEXT,
    meta_description TEXT,
    meta_keywords TEXT,
    author_id INTEGER,
    category TEXT,
    tags TEXT,
    status TEXT DEFAULT 'draft', -- draft, published, archived
    view_count INTEGER DEFAULT 0,
    published_at TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (author_id) REFERENCES admins(id) ON DELETE SET NULL
);

-- Blog categories table
CREATE TABLE IF NOT EXISTS blog_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Blog tags table
CREATE TABLE IF NOT EXISTS blog_tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Blog post tags junction table
CREATE TABLE IF NOT EXISTS blog_post_tags (
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES blog_tags(id) ON DELETE CASCADE
);

-- Indexes for SEO and performance
CREATE INDEX IF NOT EXISTS idx_blog_posts_slug ON blog_posts(slug);
CREATE INDEX IF NOT EXISTS idx_blog_posts_status ON blog_posts(status);
CREATE INDEX IF NOT EXISTS idx_blog_posts_published_at ON blog_posts(published_at);
CREATE INDEX IF NOT EXISTS idx_blog_posts_category ON blog_posts(category);
CREATE INDEX IF NOT EXISTS idx_blog_categories_slug ON blog_categories(slug);
CREATE INDEX IF NOT EXISTS idx_blog_tags_slug ON blog_tags(slug);

-- Insert default categories
INSERT OR IGNORE INTO blog_categories (name, slug, description) VALUES 
('Printing Guides', 'printing-guides', 'Comprehensive guides on printing services'),
('Design Tips', 'design-tips', 'Tips and tricks for design'),
('Product Reviews', 'product-reviews', 'Reviews of printing products and services'),
('Business Printing', 'business-printing', 'Business printing solutions and advice'),
('Custom Orders', 'custom-orders', 'Information about custom printing orders'),
('Pricing & Costs', 'pricing-costs', 'Pricing guides and cost breakdowns'),
('Industry News', 'industry-news', 'Latest news in the printing industry');