-- Canonical validation registry + coverage gaps + execution proofs (FTT).
-- Used by web/api/validation_*.php and scripts/validation_engine/.

CREATE TABLE IF NOT EXISTS `ftt_validation_surfaces` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `surface_type` VARCHAR(64) NOT NULL DEFAULT '',
  `surface_name` VARCHAR(255) NOT NULL DEFAULT '',
  `owner_module` VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT NOT NULL,
  `required_layers_json` JSON NOT NULL,
  `test_requirement_profile` VARCHAR(64) NOT NULL DEFAULT 'full',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `version_tag` VARCHAR(64) NOT NULL DEFAULT '',
  `last_verified_build_id` VARCHAR(128) NOT NULL DEFAULT '',
  `android_route_or_tab_id` VARCHAR(255) NOT NULL DEFAULT '',
  `dashboard_route_or_page_id` VARCHAR(255) NOT NULL DEFAULT '',
  `python_gui_screen_id` VARCHAR(255) NOT NULL DEFAULT '',
  `api_group_id` VARCHAR(128) NOT NULL DEFAULT '',
  `db_entity_id` VARCHAR(128) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_surface_id` (`surface_id`),
  KEY `idx_android_tab` (`android_route_or_tab_id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_surface_layers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `layer_name` VARCHAR(64) NOT NULL DEFAULT '',
  `layer_ref` VARCHAR(255) NOT NULL DEFAULT '',
  `path_or_route` TEXT NOT NULL,
  `metadata_json` JSON NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_surface` (`surface_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_fields` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `field_id` VARCHAR(128) NOT NULL DEFAULT '',
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `label` VARCHAR(255) NOT NULL DEFAULT '',
  `field_type` VARCHAR(64) NOT NULL DEFAULT '',
  `required_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `validation_rules_json` JSON NOT NULL,
  `android_view_id` VARCHAR(255) NOT NULL DEFAULT '',
  `dashboard_selector` VARCHAR(255) NOT NULL DEFAULT '',
  `python_gui_widget_id` VARCHAR(255) NOT NULL DEFAULT '',
  `api_request_field` VARCHAR(255) NOT NULL DEFAULT '',
  `api_response_field` VARCHAR(255) NOT NULL DEFAULT '',
  `db_column_name` VARCHAR(255) NOT NULL DEFAULT '',
  `default_value` TEXT NOT NULL,
  `valid_examples_json` JSON NOT NULL,
  `invalid_examples_json` JSON NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_field_id` (`field_id`),
  KEY `idx_surface` (`surface_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_test_cases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_case_id` VARCHAR(128) NOT NULL DEFAULT '',
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `layer_name` VARCHAR(64) NOT NULL DEFAULT '',
  `test_type` VARCHAR(64) NOT NULL DEFAULT '',
  `severity` VARCHAR(32) NOT NULL DEFAULT 'medium',
  `automated` TINYINT(1) NOT NULL DEFAULT 0,
  `blocking` TINYINT(1) NOT NULL DEFAULT 1,
  `required_artifacts_json` JSON NOT NULL,
  `execution_entrypoint` VARCHAR(255) NOT NULL DEFAULT '',
  `freshness_hours` INT NOT NULL DEFAULT 24,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_test_case_id` (`test_case_id`),
  KEY `idx_surface` (`surface_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_execution_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `run_id` VARCHAR(128) NOT NULL DEFAULT '',
  `build_id` VARCHAR(128) NOT NULL DEFAULT '',
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `test_case_id` VARCHAR(128) NOT NULL DEFAULT '',
  `layer_name` VARCHAR(64) NOT NULL DEFAULT '',
  `status` VARCHAR(32) NOT NULL DEFAULT '',
  `correlation_id` VARCHAR(128) NOT NULL DEFAULT '',
  `started_at` DATETIME NULL,
  `finished_at` DATETIME NULL,
  `duration_ms` BIGINT NOT NULL DEFAULT 0,
  `summary_json` JSON NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_run_id` (`run_id`),
  KEY `idx_build` (`build_id`),
  KEY `idx_correlation` (`correlation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_execution_artifacts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `run_id` VARCHAR(128) NOT NULL DEFAULT '',
  `artifact_type` VARCHAR(64) NOT NULL DEFAULT '',
  `artifact_path` TEXT NOT NULL,
  `artifact_hash` VARCHAR(128) NOT NULL DEFAULT '',
  `metadata_json` JSON NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_run` (`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_coverage_gaps` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `build_id` VARCHAR(128) NOT NULL DEFAULT '',
  `surface_id` VARCHAR(128) NOT NULL DEFAULT '',
  `layer_name` VARCHAR(64) NOT NULL DEFAULT '',
  `gap_type` VARCHAR(64) NOT NULL DEFAULT '',
  `severity` VARCHAR(32) NOT NULL DEFAULT 'high',
  `blocking` TINYINT(1) NOT NULL DEFAULT 1,
  `message` TEXT NOT NULL,
  `remediation_json` JSON NOT NULL,
  `correlation_id` VARCHAR(128) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_build_layer` (`build_id`, `layer_name`),
  KEY `idx_surface` (`surface_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ftt_validation_build_gates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `build_id` VARCHAR(128) NOT NULL DEFAULT '',
  `artifact_type` VARCHAR(32) NOT NULL DEFAULT 'apk',
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending_scan',
  `blocking_reason` TEXT NOT NULL,
  `coverage_summary_json` JSON NOT NULL,
  `proof_summary_json` JSON NOT NULL,
  `correlation_id` VARCHAR(128) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_build_artifact` (`build_id`, `artifact_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;