-- Create documents table
CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    title TEXT NOT NULL,
    filename TEXT NOT NULL,
    uri TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 999,
    uploaded_at DATETIME NOT NULL,
    UNIQUE(type)
);

-- Insert default document types with titles and URIs
INSERT OR IGNORE INTO documents (type, title, filename, uri, sort_order, uploaded_at)
VALUES 
    ('schoolreglement', 'Schoolreglement', '', 'https://klavertje.4.gvbs.be/sites/default/files/Schoolreglement%202024-2025.pdf', 1, datetime('now')),
    ('vrije_dagen', 'Vrije dagen en vakanties', '', 'https://klavertje.4.gvbs.be/sites/default/files/Vrije%20dagen%20en%20vakanties%202024-2025.pdf', 2, datetime('now')),
    ('medicatie_attest', 'Attest: Medicatie zonder voorschrift', '', 'https://klavertje.4.gvbs.be/sites/default/files/Attest%20ouders%20medicatie%20zonder%20voorschrift.pdf', 3, datetime('now'));
