-- SafeCircle Community Alerts MVP
-- Creates a separate table. Does NOT alter or overwrite personal SOS tables.
CREATE TABLE IF NOT EXISTS community_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  category ENUM('robbery','accident','fire','flood','hazard','missing_person','other') NOT NULL DEFAULT 'other',
  description VARCHAR(700) NOT NULL,
  area_label VARCHAR(160) DEFAULT NULL,
  lat_public DECIMAL(8,2) DEFAULT NULL,
  lng_public DECIMAL(8,2) DEFAULT NULL,
  status ENUM('pending','verified','rejected','resolved') NOT NULL DEFAULT 'pending',
  reviewed_by INT UNSIGNED DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_community_status_created (status, created_at),
  KEY idx_community_expiry (expires_at),
  KEY idx_community_user_created (user_id, created_at),
  CONSTRAINT fk_community_alerts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;