-- جدول وسائط المستخدم المرفوعة من التطبيق (شغّل بعد schema.sql)
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS user_media (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  asset_id VARCHAR(255) NOT NULL,
  media_type ENUM('image', 'video') NOT NULL,
  file_name VARCHAR(512) NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mime_type VARCHAR(128) NULL,
  storage_path VARCHAR(1024) NOT NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_asset (user_id, asset_id),
  KEY idx_user_uploaded (user_id, uploaded_at),
  CONSTRAINT fk_user_media_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;