-- Landing page content tables (Boundary Commission)
-- Run: mysql -u USER -p DATABASE < scripts/sql/landing_content_tables.sql

-- Single row: hero section (title, subtitle, banner, background image)
CREATE TABLE IF NOT EXISTS landing_hero (
    id INT PRIMARY KEY DEFAULT 1,
    title VARCHAR(500) NOT NULL DEFAULT '',
    subtitle VARCHAR(500) NOT NULL DEFAULT '',
    banner_text VARCHAR(1000) DEFAULT NULL,
    background_image_url VARCHAR(1000) DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Multiple rows: stats (label, value, sort_order)
CREATE TABLE IF NOT EXISTS landing_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    value VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Single row: main services block (title, body, image, 3 buttons)
CREATE TABLE IF NOT EXISTS landing_services_block (
    id INT PRIMARY KEY DEFAULT 1,
    title VARCHAR(255) NOT NULL DEFAULT '',
    body_text TEXT,
    image_url VARCHAR(1000) DEFAULT NULL,
    button_1_label VARCHAR(255) DEFAULT NULL,
    button_1_url VARCHAR(500) DEFAULT NULL,
    button_2_label VARCHAR(255) DEFAULT NULL,
    button_2_url VARCHAR(500) DEFAULT NULL,
    button_3_label VARCHAR(255) DEFAULT NULL,
    button_3_url VARCHAR(500) DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Multiple rows: service cards (title, description, link, sort_order)
CREATE TABLE IF NOT EXISTS landing_service_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    link_url VARCHAR(500) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Single row: contact (phone, email, address)
CREATE TABLE IF NOT EXISTS landing_contact (
    id INT PRIMARY KEY DEFAULT 1,
    phone VARCHAR(100) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed single-row tables so the landing page renders immediately
INSERT INTO landing_hero (id, title, subtitle, banner_text, background_image_url)
VALUES (1, 'Boundary Commission Digital Archive', 'We are ready to serve. Managing boundaries and disputes with clarity.', 'Welcome to the Boundary Commission – your source for boundary and land dispute information.', NULL)
ON DUPLICATE KEY UPDATE id = id;

INSERT INTO landing_services_block (id, title, body_text, image_url, button_1_label, button_1_url, button_2_label, button_2_url, button_3_label, button_3_url)
VALUES (1, 'Boundary Commission Services', 'Access boundary records, dispute information, surveys, and treaties. Our services cover interstate and administrative boundaries.', NULL, 'Search Boundaries', '/dashboard?section=boundary', 'View Disputes', '/dashboard?section=dispute', 'Documentation', '/dashboard?section=overview')
ON DUPLICATE KEY UPDATE id = id;

INSERT INTO landing_contact (id, phone, email, address)
VALUES (1, NULL, NULL, NULL)
ON DUPLICATE KEY UPDATE id = id;