-- MySQL schema for capsules + encrypted assets

CREATE TABLE IF NOT EXISTS capsules (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_capsules_user_id_updated_at (user_id, updated_at),
  KEY idx_capsules_user_id_created_at (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS capsule_assets (
  id CHAR(36) PRIMARY KEY,
  capsule_id CHAR(36) NOT NULL,
  type ENUM('text','video','image','pdf','document') NOT NULL,
  s3_bucket VARCHAR(255) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  content_type VARCHAR(255) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  original_filename VARCHAR(1024) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,

  -- Crypto metadata (ciphertext is stored in S3; keys are stored wrapped per recipient).
  crypto_alg VARCHAR(64) NOT NULL,
  crypto_nonce BLOB NOT NULL,
  crypto_aad BLOB NULL,
  crypto_checksum BLOB NULL,

  -- JSON array of wrapped keys: [{recipientType, recipientId, wrappedKey, wrappedKeyAlg}, ...]
  wrapped_keys_json JSON NOT NULL,

  KEY idx_capsule_assets_capsule_id_created_at (capsule_id, created_at),
  KEY idx_capsule_assets_capsule_id_type (capsule_id, type),
  KEY idx_capsule_assets_s3_bucket_key (s3_bucket(64), s3_key(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

