ALTER TABLE maintenance_orders ADD COLUMN source_request_id TEXT NOT NULL DEFAULT '';
CREATE UNIQUE INDEX idx_maintenance_orders_source_request ON maintenance_orders(source_request_id) WHERE source_request_id <> '';

CREATE TABLE intervention_requests (
  id TEXT PRIMARY KEY,
  asset_id TEXT NOT NULL,
  asset_label TEXT NOT NULL,
  source_type TEXT NOT NULL,
  reporter_user_id TEXT NOT NULL,
  reporter_name TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  location TEXT NOT NULL DEFAULT '',
  meter INTEGER NOT NULL DEFAULT 0,
  priority TEXT NOT NULL DEFAULT 'Normale',
  safety_impact INTEGER NOT NULL DEFAULT 0,
  immobilized INTEGER NOT NULL DEFAULT 0,
  approver_user_id TEXT NOT NULL,
  approver_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'À valider',
  decision_reason TEXT NOT NULL DEFAULT '',
  decided_by TEXT NOT NULL DEFAULT '',
  decided_at TEXT NOT NULL DEFAULT '',
  order_id TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX idx_intervention_requests_requester ON intervention_requests(reporter_user_id,created_at DESC);
CREATE INDEX idx_intervention_requests_approver_status ON intervention_requests(approver_user_id,status,created_at DESC);

CREATE TABLE intervention_request_events (
  id TEXT PRIMARY KEY,
  request_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  actor_user_id TEXT NOT NULL,
  actor_name TEXT NOT NULL,
  message TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE INDEX idx_intervention_request_events_request ON intervention_request_events(request_id,created_at);

CREATE TABLE intervention_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 idx_intervention_notifications_user_read ON intervention_notifications(user_id,is_read,created_at DESC);

PRAGMA optimize;