`database/blog-schema.sql`

134 lines
-- Blog System Schema for Omega Watches
-- Comprehensive content management with SEO optimization

-- Blog Articles Table
CREATE TABLE IF NOT EXISTS blog_articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    excerpt TEXT,
    content TEXT NOT NULL,
    author VARCHAR(100) DEFAULT 'Omega Watch Expert',
    category VARCHAR(50) NOT NULL,
    tags TEXT[], -- Array of tags
    featured_image VARCHAR(500),
    status VARCHAR(20) DEFAULT 'published', -- draft, published, archived
    meta_title VARCHAR(255),
    meta_description TEXT,
    meta_keywords TEXT[],
    read_time INTEGER, -- in minutes
    views INTEGER DEFAULT 0,
    likes INTEGER DEFAULT 0,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Glossary Terms Table
CREATE TABLE IF NOT EXISTS glossary_terms (
    id SERIAL PRIMARY KEY,
    term VARCHAR(100) UNIQUE NOT NULL,
    definition TEXT NOT NULL,
    category VARCHAR(50),
    related_terms TEXT[],
    slug VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Resource Library Table
CREATE TABLE IF NOT EXISTS resources (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    resource_type VARCHAR(50) NOT NULL, -- guide, video, infographic, tool
    url VARCHAR(500),
    file_path VARCHAR(500),
    thumbnail VARCHAR(500),
    category VARCHAR(50),
    tags TEXT[],
    downloads INTEGER DEFAULT 0,
    views INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Reviews Table
CREATE TABLE IF NOT EXISTS watch_reviews (
    id SERIAL PRIMARY KEY,
    watch_model VARCHAR(200) NOT NULL,
    watch_reference VARCHAR(50),
    user_name VARCHAR(100) NOT NULL,
    user_email VARCHAR(255),
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255) NOT NULL,
    review_text TEXT NOT NULL,
    pros TEXT[],
    cons TEXT[],
    verified_purchase BOOLEAN DEFAULT false,
    helpful_count INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP
);

-- Blog Comments Table
CREATE TABLE IF NOT EXISTS blog_comments (
    id SERIAL PRIMARY KEY,
    article_id INTEGER REFERENCES blog_articles(id) ON DELETE CASCADE,
    user_name VARCHAR(100) NOT NULL,
    user_email VARCHAR(255),
    comment_text TEXT NOT NULL,
    parent_id INTEGER REFERENCES blog_comments(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending', -- pending, approved, spam
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Article Analytics Table
CREATE TABLE IF NOT EXISTS article_analytics (
    id SERIAL PRIMARY KEY,
    article_id INTEGER REFERENCES blog_articles(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL, -- view, like, share, comment
    user_ip VARCHAR(45),
    user_agent TEXT,
    referrer VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_articles_slug ON blog_articles(slug);
CREATE INDEX IF NOT EXISTS idx_articles_category ON blog_articles(category);
CREATE INDEX IF NOT EXISTS idx_articles_status ON blog_articles(status);
CREATE INDEX IF NOT EXISTS idx_articles_published ON blog_articles(published_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_tags ON blog_articles USING GIN(tags);

CREATE INDEX IF NOT EXISTS idx_glossary_term ON glossary_terms(term);
CREATE INDEX IF NOT EXISTS idx_glossary_slug ON glossary_terms(slug);

CREATE INDEX IF NOT EXISTS idx_reviews_model ON watch_reviews(watch_model);
CREATE INDEX IF NOT EXISTS idx_reviews_status ON watch_reviews(status);
CREATE INDEX IF NOT EXISTS idx_reviews_rating ON watch_reviews(rating);

CREATE INDEX IF NOT EXISTS idx_comments_article ON blog_comments(article_id);
CREATE INDEX IF NOT EXISTS idx_comments_status ON blog_comments(status);

CREATE INDEX IF NOT EXISTS idx_analytics_article ON article_analytics(article_id);
CREATE INDEX IF NOT EXISTS idx_analytics_date ON article_analytics(created_at);

-- Insert initial glossary terms
INSERT INTO glossary_terms (term, definition, category, slug) VALUES
('Caliber', 'The movement or internal mechanism of a watch. Each caliber has a unique designation that identifies its technical specifications.', 'Movement', 'caliber'),
('Chronometer', 'A high-precision timepiece that has been tested and certified by an official watch institute (like COSC) for accuracy.', 'Certification', 'chronometer'),
('Co-Axial Escapement', 'Omega''s revolutionary escapement mechanism that reduces friction and improves long-term accuracy and reliability.', 'Movement', 'co-axial-escapement'),
('Master Chronometer', 'Omega''s highest certification standard, tested by METAS for precision, magnetic resistance, and water resistance.', 'Certification', 'master-chronometer'),
('Moonphase', 'A complication that displays the current phase of the moon on the watch dial.', 'Complication', 'moonphase'),
('Tourbillon', 'A complex mechanical complication that counters the effects of gravity on the watch movement.', 'Complication', 'tourbillon'),
('Case Diameter', 'The width of the watch case measured across the face, typically in millimeters.', 'Specifications', 'case-diameter'),
('Water Resistance', 'The depth to which a watch can be submerged without damage, measured in meters or atmospheres (ATM).', 'Specifications', 'water-resistance'),
('Bezel', 'The ring surrounding the watch crystal, which can be fixed or rotating depending on the watch type.', 'Components', 'bezel'),
('Crown', 'The knob on the side of the watch used to set the time and wind the movement.', 'Components', 'crown'),
('Sapphire Crystal', 'A scratch-resistant transparent cover protecting the watch dial, made from synthetic sapphire.', 'Components', 'sapphire-crystal'),
('Helium Escape Valve', 'A valve that allows helium to escape during decompression, essential for professional diving watches.', 'Components', 'helium-escape-valve'),
('Power Reserve', 'The amount of time a mechanical watch will run after being fully wound, typically 48-60 hours.', 'Movement', 'power-reserve'),
('Automatic Movement', 'A self-winding mechanical movement that winds itself through the motion of the wearer''s wrist.', 'Movement', 'automatic-movement'),
('Manual Wind', 'A mechanical movement that requires manual winding via the crown to maintain power.', 'Movement', 'manual-wind')
ON CONFLICT (term) DO NOTHING;