CREATE TABLE IF NOT EXISTS menu_items (
    id TEXT PRIMARY KEY,
    menu_id TEXT REFERENCES menu(id) ON DELETE CASCADE,
    reference_id TEXT, -- ID of the page/category/tag if applicable
    title VARCHAR(255), -- optional for custom_link
    type VARCHAR(50) CHECK (type IN ('page', 'category', 'service', 'product', 'tag', 'custom_link')),
    link VARCHAR(255), -- required only for custom_link
    parent_id TEXT REFERENCES menu_items(id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 0 NOT NULL,
    language VARCHAR(10) NOT NULL CHECK (language IN ('en', 'ar'))
);