-- ========================================================
-- MIGRATION: About Page CMS settings & timeline milestones
-- Tracks all editable sections of the public About page
-- ========================================================

CREATE TABLE IF NOT EXISTS about_page_settings (
    id VARCHAR(100) PRIMARY KEY DEFAULT 'about_cms_default',
    hero_title VARCHAR(255) NOT NULL DEFAULT 'Redefining Luxury',
    hero_subtitle VARCHAR(1000) NOT NULL DEFAULT 'We don\'t just sell properties; we curate lifestyles. Wild Dubai is the premier luxury real estate agency for discerning individuals.',
    hero_bg_image VARCHAR(255) NOT NULL DEFAULT 'https://images.unsplash.com/photo-1546412414-e1885259563a?auto=format&fit=crop&q=80&w=1920',
    mission_title VARCHAR(255) NOT NULL DEFAULT 'Our Mission',
    mission_desc TEXT NOT NULL,
    value1_title VARCHAR(255) NOT NULL DEFAULT 'Integrity First',
    value1_desc VARCHAR(1000) NOT NULL DEFAULT 'Transparency and honesty in every transaction, ensuring our clients\' interests are always protected.',
    value2_title VARCHAR(255) NOT NULL DEFAULT 'Precision Driven',
    value2_desc VARCHAR(1000) NOT NULL DEFAULT 'Data-backed insights and meticulous attention to detail to secure the best possible outcomes.',
    value3_title VARCHAR(255) NOT NULL DEFAULT 'Excellence Standard',
    value3_desc VARCHAR(1000) NOT NULL DEFAULT 'A relentless pursuit of perfection in our service delivery and property selection.',
    ceo_quote TEXT NOT NULL,
    ceo_name VARCHAR(255) NOT NULL DEFAULT 'Alexander Sterling',
    ceo_role VARCHAR(255) NOT NULL DEFAULT 'Founder & CEO, Wild Dubai',
    ceo_image VARCHAR(255) NOT NULL DEFAULT 'https://images.unsplash.com/photo-1560250097-0b93528c311a?auto=format&fit=crop&q=80&w=400',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS about_page_timeline (
    id VARCHAR(100) PRIMARY KEY,
    year VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    about_page_settings_id VARCHAR(100) NOT NULL DEFAULT 'about_cms_default',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (about_page_settings_id) REFERENCES about_page_settings(id) ON DELETE CASCADE
);

-- Seed defaults if settings do not exist
INSERT IGNORE INTO about_page_settings (
    id, hero_title, hero_subtitle, hero_bg_image,
    mission_title, mission_desc,
    value1_title, value1_desc,
    value2_title, value2_desc,
    value3_title, value3_desc,
    ceo_quote, ceo_name, ceo_role, ceo_image
) VALUES (
    'about_cms_default',
    'Redefining Luxury',
    'We don\'t just sell properties; we curate lifestyles. Wild Dubai is the premier luxury real estate agency for discerning individuals.',
    'https://images.unsplash.com/photo-1546412414-e1885259563a?auto=format&fit=crop&q=80&w=1920',
    'Our Mission',
    'To provide unparalleled real estate advisory and brokerage services, built on a foundation of integrity, market expertise, and an unwavering commitment to client success.',
    'Integrity First',
    'Transparency and honesty in every transaction, ensuring our clients\' interests are always protected.',
    'Precision Driven',
    'Data-backed insights and meticulous attention to detail to secure the best possible outcomes.',
    'Excellence Standard',
    'A relentless pursuit of perfection in our service delivery and property selection.',
    'In the world of luxury real estate, trust is the ultimate currency. We don\'t just facilitate transactions; we build enduring relationships based on discretion and profound market knowledge.',
    'Alexander Sterling',
    'Founder & CEO, Wild Dubai',
    'https://images.unsplash.com/photo-1560250097-0b93528c311a?auto=format&fit=crop&q=80&w=400'
);

-- Seed timeline milestones if empty
INSERT IGNORE INTO about_page_timeline (id, year, title, description, about_page_settings_id)
VALUES
(
    'seed-milestone-1',
    '2012',
    'Foundation',
    'Wild Dubai was established with a vision to redefine luxury real estate in the UAE.',
    'about_cms_default'
),
(
    'seed-milestone-2',
    '2015',
    'Expansion',
    'Opened our flagship office in Downtown Dubai and expanded our portfolio to include commercial properties.',
    'about_cms_default'
),
(
    'seed-milestone-3',
    '2018',
    'Market Leaders',
    'Achieved over AED 1 Billion in sales within a single fiscal year, cementing our position as market leaders.',
    'about_cms_default'
),
(
    'seed-milestone-4',
    '2023',
    'Global Reach',
    'Launched our international advisory division, catering to ultra-high-net-worth investors globally.',
    'about_cms_default'
);

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