CREATE TABLE IF NOT EXISTS plugins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(150) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  version VARCHAR(50) NOT NULL,
  description TEXT NULL,
  vendor_type ENUM('first_party','third_party') NOT NULL DEFAULT 'first_party',
  status ENUM('enabled','disabled','pending_purge','removed') NOT NULL DEFAULT 'enabled',
  install_path VARCHAR(255) NOT NULL,
  assets_path VARCHAR(255) NULL,
  manifest_json JSON NOT NULL,
  file_snapshot_json JSON NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  installed_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  created_by VARCHAR(150) NULL,
  updated_by VARCHAR(150) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plugin_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(150) NOT NULL,
  version VARCHAR(50) NOT NULL,
  manifest_json JSON NOT NULL,
  file_snapshot_json JSON NOT NULL,
  package_filename VARCHAR(255) NULL,
  package_sha256 CHAR(64) NULL,
  installed_at DATETIME NOT NULL,
  installed_by VARCHAR(150) NULL,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE,
  INDEX idx_plugin_versions_slug (slug),
  INDEX idx_plugin_versions_plugin_id (plugin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plugin_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(150) NOT NULL,
  action ENUM('install','replace','enable','disable','mark_pending_purge','purge','remove','validation_failed') NOT NULL,
  version_from VARCHAR(50) NULL,
  version_to VARCHAR(50) NULL,
  result ENUM('success','failure') NOT NULL,
  message TEXT NULL,
  manifest_snapshot_json JSON NULL,
  file_snapshot_json JSON NULL,
  actor VARCHAR(150) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_plugin_history_slug (slug),
  INDEX idx_plugin_history_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plugin_files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plugin_id BIGINT UNSIGNED NOT NULL,
  plugin_version_id BIGINT UNSIGNED NOT NULL,
  relative_path VARCHAR(255) NOT NULL,
  file_type ENUM('file','dir') NOT NULL,
  size_bytes BIGINT UNSIGNED NULL,
  sha256 CHAR(64) NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE,
  FOREIGN KEY (plugin_version_id) REFERENCES plugin_versions(id) ON DELETE CASCADE,
  INDEX idx_plugin_files_plugin_id (plugin_id),
  INDEX idx_plugin_files_version_id (plugin_version_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
