ALTER TABLE app_users ADD COLUMN manager_user_id TEXT NOT NULL DEFAULT '';
CREATE INDEX IF NOT EXISTS idx_app_users_manager ON app_users(manager_user_id,status);

CREATE TABLE IF NOT EXISTS purchase_requests (
  id TEXT PRIMARY KEY, source_type TEXT NOT NULL, source_ref TEXT NOT NULL DEFAULT '', priority TEXT NOT NULL,
  requester_user_id TEXT NOT NULL DEFAULT '', requester_name TEXT NOT NULL, approver_user_id TEXT NOT NULL,
  approver_name TEXT NOT NULL, needed_at TEXT NOT NULL, supplier TEXT NOT NULL DEFAULT '', cost_center TEXT NOT NULL,
  justification TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'À approuver', rejection_reason TEXT NOT NULL DEFAULT '',
  order_ref TEXT NOT NULL DEFAULT '', lines_json TEXT NOT NULL, created_by TEXT NOT NULL, created_at TEXT NOT NULL,
  decided_by TEXT NOT NULL DEFAULT '', decided_at TEXT NOT NULL DEFAULT '', updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_purchase_requests_requester ON purchase_requests(requester_user_id,created_at DESC);
CREATE INDEX IF NOT EXISTS idx_purchase_requests_approver_status ON purchase_requests(approver_user_id,status,created_at DESC);

CREATE TABLE IF NOT EXISTS purchase_notifications (
  id TEXT PRIMARY KEY, user_id TEXT NOT NULL, type TEXT NOT NULL, title TEXT NOT NULL, message TEXT NOT NULL,
  request_id TEXT NOT NULL, is_read INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_purchase_notifications_user_read ON purchase_notifications(user_id,is_read,created_at DESC);
PRAGMA optimize;