-- Adds organised categories to the Members & Officers Document Library.
-- Run this once after upgrade-005-raffle-sales-unit.sql.

ALTER TABLE documents
  ADD COLUMN category ENUM(
    'summons',
    'minutes',
    'bylaws',
    'constitutions',
    'forms_guidance',
    'other'
  ) NOT NULL DEFAULT 'other' AFTER document_url,
  ADD INDEX idx_documents_category_visibility (category, visibility, active, display_order);

-- Place recognisable existing documents into sensible categories.
UPDATE documents SET category = 'summons' WHERE LOWER(title) LIKE '%summons%';
UPDATE documents SET category = 'minutes' WHERE LOWER(title) LIKE '%minute%';
UPDATE documents SET category = 'bylaws' WHERE LOWER(title) LIKE '%bylaw%' OR LOWER(title) LIKE '%by-law%';
UPDATE documents SET category = 'constitutions' WHERE LOWER(title) LIKE '%constitution%';