CREATE TABLE page_sections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    page_key TEXT NOT NULL,
    section_type TEXT NOT NULL CHECK (section_type IN ('text', 'link_list')),
    heading TEXT,
    body TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_visible INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id)
);

CREATE TABLE page_section_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    section_id INTEGER NOT NULL REFERENCES page_sections(id) ON DELETE CASCADE,
    label TEXT NOT NULL,
    url TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_visible INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id)
);

CREATE INDEX idx_page_sections_page_key ON page_sections(page_key);
CREATE INDEX idx_page_sections_sort_order ON page_sections(page_key, sort_order);
CREATE INDEX idx_page_section_links_section_id ON page_section_links(section_id);
CREATE INDEX idx_page_section_links_sort_order ON page_section_links(section_id, sort_order);

INSERT INTO page_sections (page_key, section_type, heading, body, sort_order, is_visible)
VALUES (
    'ouderraad',
    'text',
    NULL,
    '<p>De oudderraad heeft 3 belangrijke doelstellingen: een spreekbuis tussen ouders en directie/schoolraad. Het bevorderen van het sociaal contact tussen de ouders en geld inzamelen voor projecten in de school via organisatie van activiteiten.</p><p><strong>E-mail:</strong> <a href="mailto:ouderraad.olmen@gmail.com">ouderraad.olmen@gmail.com</a></p>',
    10,
    1
);

INSERT INTO page_sections (page_key, section_type, heading, body, sort_order, is_visible)
VALUES (
    'ouderraad',
    'link_list',
    'Trooper',
    '<p>Wil je doorheen het jaar de ouderraad en de school steunen, dan kan je dit altijd doen via onze Trooper-actie. Wil je meer weten over over Trooper dan kan je hier meer ontdekken:</p>',
    20,
    1
);

INSERT INTO page_section_links (section_id, label, url, sort_order, is_visible)
SELECT id, 'Steun ons via Trooper', 'https://www.trooper.be/nl/trooperverenigingen/ouderraadklavertje4olmen', 10, 1
FROM page_sections
WHERE page_key = 'ouderraad' AND section_type = 'link_list' AND heading = 'Trooper';

INSERT INTO page_section_links (section_id, label, url, sort_order, is_visible)
SELECT id, 'Hoe werkt Trooper?', 'https://www.trooper.be/nl/hoe-troopen', 20, 1
FROM page_sections
WHERE page_key = 'ouderraad' AND section_type = 'link_list' AND heading = 'Trooper';

INSERT INTO page_section_links (section_id, label, url, sort_order, is_visible)
SELECT id, 'Trooper uitgelegd (video)', 'https://www.youtube.com/watch?v=4KqIlkKtC8A', 30, 1
FROM page_sections
WHERE page_key = 'ouderraad' AND section_type = 'link_list' AND heading = 'Trooper';

INSERT INTO page_section_links (section_id, label, url, sort_order, is_visible)
SELECT id, 'Gebruik de Trooper bot om je te waarschuwen', 'https://www.youtube.com/watch?v=tOxBigtgUFU', 40, 1
FROM page_sections
WHERE page_key = 'ouderraad' AND section_type = 'link_list' AND heading = 'Trooper';
