-- ========================================================
-- MIGRATION: Multiple Testimonials Table
-- Tracks multiple testimonials linked to the home page CMS
-- ========================================================

CREATE TABLE IF NOT EXISTS home_page_testimonials (
    id VARCHAR(100) PRIMARY KEY,
    quote TEXT NOT NULL,
    author VARCHAR(200) NOT NULL,
    role VARCHAR(200) NOT NULL DEFAULT 'Property Investor',
    rating INT NOT NULL DEFAULT 5,
    home_page_settings_id VARCHAR(100) NOT NULL DEFAULT 'home_cms_default',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (home_page_settings_id) REFERENCES home_page_settings(id) ON DELETE CASCADE
);

-- Seed with the original 3 default testimonials from the landing page design
INSERT IGNORE INTO home_page_testimonials (id, quote, author, role, rating, home_page_settings_id)
VALUES 
(
    'seed-testimonial-1',
    'The level of professionalism and market insight provided was unparalleled. They found the perfect off-plan investment that exceeded all expectations.',
    'Sarah Jenkins',
    'Property Investor',
    5,
    'home_cms_default'
),
(
    'seed-testimonial-2',
    'An incredibly smooth process from start to finish. The team handled everything with utmost care and secured a fantastic price for my villa.',
    'Ahmed Al Maktoum',
    'Homeowner',
    5,
    'home_cms_default'
),
(
    'seed-testimonial-3',
    'Moving to Dubai was daunting, but their property search service made it effortless. We found our dream apartment in just one week.',
    'Elena Rostova',
    'Expat Resident',
    5,
    'home_cms_default'
);

-- Verify
SELECT 'home_page_testimonials' AS tbl, COUNT(*) AS row_count FROM home_page_testimonials;