-- ========================================================
-- MIGRATION: Real Estate Blog System
-- Tracks Blog Categories and Blog Posts with relations
-- ========================================================

CREATE TABLE IF NOT EXISTS blog_categories (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS blog_posts (
    id VARCHAR(100) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    excerpt TEXT NOT NULL,
    content TEXT NOT NULL,
    featured_img VARCHAR(1000) NOT NULL,
    read_time VARCHAR(50) NOT NULL DEFAULT '5 min read',
    is_featured BOOLEAN NOT NULL DEFAULT FALSE,
    is_published BOOLEAN NOT NULL DEFAULT TRUE,
    category_id VARCHAR(100) NOT NULL,
    author_name VARCHAR(255) NOT NULL DEFAULT 'Wilde Dubai Editors',
    author_img VARCHAR(255) NOT NULL DEFAULT '/images/default-avatar.png',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE CASCADE
);

-- Seed defaults categories
INSERT IGNORE INTO blog_categories (id, name, slug)
VALUES 
('cat-market-trends', 'Market Trends', 'market-trends'),
('cat-lifestyle', 'Dubai Lifestyle', 'dubai-lifestyle'),
('cat-investment', 'Investment Guide', 'investment-guide');

-- Seed sample blog posts
INSERT IGNORE INTO blog_posts (id, title, slug, excerpt, content, featured_img, read_time, is_featured, is_published, category_id, author_name)
VALUES
(
    'post-sample-1',
    'Dubai Real Estate Market Forecast 2026',
    'dubai-real-estate-market-forecast-2026',
    'An in-depth analysis of where the Dubai property market is heading in 2026, highlighting top areas for yield and capital appreciation.',
    '# Dubai Real Estate Market Forecast 2026\n\nDubai real estate continues to serve as a beacon for global wealth, combining tax advantages, robust yields, and premium developments. Here is our strategic outlook for 2026.\n\n## 1. Projected Capital Growth\n\nHistorically, prime areas like Palm Jumeirah, Downtown Dubai, and Dubai Hills Estate have outperformed regional benchmarks. We expect prime valuations to grow by an additional 8-12% in 2026, driven by high demand from ultra-high-net-worth individuals (UHNWIs).\n\n## 2. Rental Yield Influx\n\nInvestors looking for steady passive cash flows can expect rental yields averaging 6-8% in mid-market villa communities, and up to 9% in luxury apartment hubs like Dubai Marina and Business Bay. Short-term rentals continue to expand rapidly.\n\n## Summary\n\nWhether acquiring properties for relocation or investment portfolios, 2026 presents a mature buyer-friendly climate with long-term residency options like the Golden Visa backing your assets.',
    'https://images.unsplash.com/photo-1512453979798-5ea266f8880c?auto=format&fit=crop&q=80&w=800',
    '6 min read',
    TRUE,
    TRUE,
    'cat-market-trends',
    'Alexander Sterling'
),
(
    'post-sample-2',
    'A Guide to Golden Visa via Property Invesment',
    'guide-to-golden-visa-via-property-investment',
    'How to secure long-term UAE residency through real estate acquisitions. Learn requirements, budgets, and step-by-step processes.',
    '# A Guide to Golden Visa via Property Investment\n\nSecuring a 10-year Golden Visa in the UAE is now more accessible than ever through real estate investments.\n\n## Key Requirements\n\n- **Minimum Investment**: The total value of your property assets must equal or exceed **AED 2 Million** (approx. USD 545,000).\n- **Property Types**: Ready or off-plan properties are eligible. They can be financed via local mortgages under specific criteria.\n- **Joint Ownership**: Spouses can share ownership to reach the AED 2 Million threshold.\n\n## Benefits\n\n- 10-year renewable residency.\n- No requirement to reside in the UAE (visit at least once every 6 months to keep active).\n- Sponsor family members and domestic helpers easily.',
    'https://images.unsplash.com/photo-1582407947304-fd86f028f716?auto=format&fit=crop&q=80&w=800',
    '4 min read',
    FALSE,
    TRUE,
    'cat-investment',
    'Wilde Dubai Editors'
),
(
    'post-sample-3',
    'Inside Dubai\'s Most Exclusive Penthouses',
    'inside-dubais-most-exclusive-penthouses',
    'Step inside the architectural marvels floating above the Dubai skyline. Explore high-end finishes, layouts, and panoramic terrace views.',
    '# Inside Dubai\'s Most Exclusive Penthouses\n\nWhen it comes to ultra-luxury living, penthouses in Dubai set the absolute standard. Floating high above the city, these multi-million dollar homes offer unparalleled views and amenities.\n\n## Premium Amenities\n\nDiscerning residents enjoy private infinity pools, wellness rooms, dedicated butler elevators, and 360-degree glass terraces showcasing Palm Jumeirah or the Burj Khalifa skyline.\n\n## Top Locations\n\n- **Palm Jumeirah**: Beachfront tranquility and island estates.\n- **Downtown Dubai**: Urban luxury near Burj Khalifa.\n- **One Canal**: Brand new designs featuring bespoke pools.',
    'https://images.unsplash.com/photo-1600596542815-ffad4c1539a9?auto=format&fit=crop&q=80&w=800',
    '5 min read',
    FALSE,
    TRUE,
    'cat-lifestyle',
    'Elena Rostova'
);

-- Verification
SELECT 'blog_categories' AS tbl, COUNT(*) AS row_count FROM blog_categories
UNION ALL
SELECT 'blog_posts' AS tbl, COUNT(*) AS row_count FROM blog_posts;