This as the **v2 baseline schema**, not the forever-final schema.

That is the right point to stop and let Claude build the database, because the legacy schema in `Searchmercials_Structure.sql` is clearly carrying old account, affiliate, listing, category, click, and transaction patterns that should inform migration, not define the new domain model. You can see that in legacy tables like `iweb_accounts`, `iweb_hs_categories`, `iweb_acct_transactions`, `iweb_affiliate_info`, and `iweb_hyperseek_backfill_table` in the uploaded structure file, alongside the broader project analysis describing Searchmercials as a legacy directory/ad marketplace with accounts, click tracking, affiliates, categories, and Hyperseek lineage.  

Below is the prompt I would give Claude, followed by a **Phase 1 v2 SQL schema** to create now.

## Prompt for Claude

You are creating the initial Searchmercials v2 database schema in MySQL 8 for a new Laravel-based platform.

DB_Name: searchmercials_v2

Context:

* The legacy schema in `Searchmercials_Structure.sql` is a reference source only.
* Do not copy the legacy schema structure directly.
* Use it only to understand legacy concepts like accounts, listings, categories, click tracking, affiliate/publisher behavior, transactions, and imported business data.
* The new schema must use the `sm_` prefix consistently.
* This is a new normalized v2 schema for a job-driven local-search monetization platform.

Primary architectural rules:

1. `sm_vendors` is the canonical business entity.
2. `sm_listings` is the public searchable representation of a vendor.
3. `sm_vendor_locations` supports multi-location businesses.
4. `sm_categories` supports a hierarchical taxonomy.
5. `sm_search_queries`, `sm_search_results`, `sm_sessions`, `sm_event_impressions`, and `sm_event_clicks` capture user demand and result interaction.
6. `sm_claim_requests` supports listing claim workflows.
7. `sm_sources`, `sm_source_import_batches`, `sm_source_records`, and `sm_vendor_source_map` support ingestion and normalization from external sources.
8. `sm_job_definitions`, `sm_job_queue`, and `sm_job_runs` form the operational job architecture.
9. This database is for Laravel, so all tables should use:

   * `bigint unsigned` primary keys where appropriate
   * `created_at` and `updated_at` timestamps when relevant
   * InnoDB
   * `utf8mb4`
10. Use foreign keys where the relationship is stable and beneficial.
11. Add practical indexes for likely lookup paths.
12. Avoid overengineering Phase 1. Do not create the monetization tables yet unless explicitly requested later.

Deliverables:

* Produce a clean MySQL 8 SQL file that creates the Phase 1 Searchmercials v2 schema.
* Use database name `searchmercials_v2`.
* Include `DROP TABLE IF EXISTS` in dependency-safe order.
* Include `CREATE TABLE` statements in creation-safe order.
* Include keys, constraints, indexes, and enums only where they are genuinely helpful.
* Include brief SQL comments above each table.
* Do not generate Laravel migrations yet. SQL only.

Create these Phase 1 tables:

* sm_tenants
* sm_accounts
* sm_users
* sm_roles
* sm_user_role_map
* sm_domains
* sm_vendors
* sm_vendor_locations
* sm_categories
* sm_vendor_category_map
* sm_listings
* sm_listing_media
* sm_listing_attributes
* sm_sources
* sm_source_import_batches
* sm_source_records
* sm_vendor_source_map
* sm_search_queries
* sm_search_results
* sm_sessions
* sm_event_impressions
* sm_event_clicks
* sm_claim_requests
* sm_claim_verifications
* sm_job_definitions
* sm_job_queue
* sm_job_runs
* sm_job_errors

Important implementation notes:

* Use `uuid CHAR(36)` columns where useful for public-safe identifiers.
* Use JSON columns where payloads are semi-structured.
* Use decimal lat/lng instead of strings.
* Use `claim_status`, `verification_status`, and `status` fields as short VARCHAR or ENUM where appropriate.
* `sm_categories.parent_id` should self-reference categories.
* `sm_vendor_category_map` should support `is_primary` and `confidence_score`.
* `sm_search_results` should snapshot ranking position at the time of search.
* `sm_event_clicks.click_type` should support at least `listing`, `website`, `phone`, `direction`, `claim`, and `ad`.
* `sm_job_definitions.job_code` should support dotted naming like `import.search_results` and `enrich.vendor_ai`.
* `sm_job_runs` should represent execution attempts for queued jobs.
* Keep the schema clean, readable, production-ready, and compatible with Laravel conventions.

Output only the final SQL file content.

## SQL for Claude to create now

```sql
CREATE DATABASE IF NOT EXISTS `searchmercials_v2`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `searchmercials_v2`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `sm_job_errors`;
DROP TABLE IF EXISTS `sm_job_runs`;
DROP TABLE IF EXISTS `sm_job_queue`;
DROP TABLE IF EXISTS `sm_job_definitions`;
DROP TABLE IF EXISTS `sm_claim_verifications`;
DROP TABLE IF EXISTS `sm_claim_requests`;
DROP TABLE IF EXISTS `sm_event_clicks`;
DROP TABLE IF EXISTS `sm_event_impressions`;
DROP TABLE IF EXISTS `sm_search_results`;
DROP TABLE IF EXISTS `sm_search_queries`;
DROP TABLE IF EXISTS `sm_sessions`;
DROP TABLE IF EXISTS `sm_vendor_source_map`;
DROP TABLE IF EXISTS `sm_source_records`;
DROP TABLE IF EXISTS `sm_source_import_batches`;
DROP TABLE IF EXISTS `sm_sources`;
DROP TABLE IF EXISTS `sm_listing_attributes`;
DROP TABLE IF EXISTS `sm_listing_media`;
DROP TABLE IF EXISTS `sm_listings`;
DROP TABLE IF EXISTS `sm_vendor_category_map`;
DROP TABLE IF EXISTS `sm_categories`;
DROP TABLE IF EXISTS `sm_vendor_locations`;
DROP TABLE IF EXISTS `sm_vendors`;
DROP TABLE IF EXISTS `sm_domains`;
DROP TABLE IF EXISTS `sm_user_role_map`;
DROP TABLE IF EXISTS `sm_roles`;
DROP TABLE IF EXISTS `sm_users`;
DROP TABLE IF EXISTS `sm_accounts`;
DROP TABLE IF EXISTS `sm_tenants`;

-- Tenant / white-label root
CREATE TABLE `sm_tenants` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `settings_json` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_tenants_uuid` (`uuid`),
  UNIQUE KEY `uq_sm_tenants_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Account / organization entity
CREATE TABLE `sm_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `account_type` ENUM('vendor','advertiser','publisher','admin','agency') NOT NULL DEFAULT 'vendor',
  `name` VARCHAR(150) NOT NULL,
  `legal_name` VARCHAR(200) NULL,
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(40) NULL,
  `website` VARCHAR(255) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `lifecycle_stage` VARCHAR(50) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_accounts_uuid` (`uuid`),
  KEY `idx_sm_accounts_tenant_id` (`tenant_id`),
  KEY `idx_sm_accounts_type_status` (`account_type`,`status`),
  KEY `idx_sm_accounts_email` (`email`),
  CONSTRAINT `fk_sm_accounts_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Human users
CREATE TABLE `sm_users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(40) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `last_login_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_users_uuid` (`uuid`),
  UNIQUE KEY `uq_sm_users_email` (`email`),
  KEY `idx_sm_users_account_id` (`account_id`),
  CONSTRAINT `fk_sm_users_account`
    FOREIGN KEY (`account_id`) REFERENCES `sm_accounts` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Roles
CREATE TABLE `sm_roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User to role map
CREATE TABLE `sm_user_role_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_user_role_map` (`user_id`,`role_id`,`tenant_id`),
  KEY `idx_sm_user_role_map_role_id` (`role_id`),
  KEY `idx_sm_user_role_map_tenant_id` (`tenant_id`),
  CONSTRAINT `fk_sm_user_role_map_user`
    FOREIGN KEY (`user_id`) REFERENCES `sm_users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_user_role_map_role`
    FOREIGN KEY (`role_id`) REFERENCES `sm_roles` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_user_role_map_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Domains for white-label / branded access
CREATE TABLE `sm_domains` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `domain` VARCHAR(190) NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `ssl_status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_domains_domain` (`domain`),
  KEY `idx_sm_domains_tenant_id` (`tenant_id`),
  CONSTRAINT `fk_sm_domains_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Canonical business entity
CREATE TABLE `sm_vendors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `primary_account_id` BIGINT UNSIGNED NULL,
  `canonical_name` VARCHAR(190) NOT NULL,
  `legal_name` VARCHAR(190) NULL,
  `display_name` VARCHAR(190) NOT NULL,
  `description_short` VARCHAR(500) NULL,
  `description_long` TEXT NULL,
  `website` VARCHAR(255) NULL,
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(40) NULL,
  `claim_status` ENUM('unclaimed','pending','claimed','locked') NOT NULL DEFAULT 'unclaimed',
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'unverified',
  `source_confidence_score` DECIMAL(5,2) NULL,
  `enrichment_status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `active_status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_vendors_uuid` (`uuid`),
  KEY `idx_sm_vendors_tenant_id` (`tenant_id`),
  KEY `idx_sm_vendors_primary_account_id` (`primary_account_id`),
  KEY `idx_sm_vendors_claim_status` (`claim_status`),
  KEY `idx_sm_vendors_display_name` (`display_name`),
  CONSTRAINT `fk_sm_vendors_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_vendors_primary_account`
    FOREIGN KEY (`primary_account_id`) REFERENCES `sm_accounts` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vendor locations
CREATE TABLE `sm_vendor_locations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `vendor_id` BIGINT UNSIGNED NOT NULL,
  `location_name` VARCHAR(190) NULL,
  `address1` VARCHAR(190) NULL,
  `address2` VARCHAR(190) NULL,
  `city` VARCHAR(120) NULL,
  `state` VARCHAR(120) NULL,
  `postal_code` VARCHAR(20) NULL,
  `country_code` CHAR(2) NULL,
  `latitude` DECIMAL(10,7) NULL,
  `longitude` DECIMAL(10,7) NULL,
  `phone` VARCHAR(40) NULL,
  `email` VARCHAR(190) NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `service_area_json` JSON NULL,
  `geo_hash` VARCHAR(32) NULL,
  `active_status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_vendor_locations_uuid` (`uuid`),
  KEY `idx_sm_vendor_locations_vendor_id` (`vendor_id`),
  KEY `idx_sm_vendor_locations_city_state` (`city`,`state`),
  KEY `idx_sm_vendor_locations_postal_code` (`postal_code`),
  KEY `idx_sm_vendor_locations_lat_lng` (`latitude`,`longitude`),
  CONSTRAINT `fk_sm_vendor_locations_vendor`
    FOREIGN KEY (`vendor_id`) REFERENCES `sm_vendors` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hierarchical category taxonomy
CREATE TABLE `sm_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `parent_id` BIGINT UNSIGNED NULL,
  `slug` VARCHAR(190) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `full_path` VARCHAR(500) NULL,
  `level_depth` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `description` TEXT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_categories_uuid` (`uuid`),
  UNIQUE KEY `uq_sm_categories_tenant_slug` (`tenant_id`,`slug`),
  KEY `idx_sm_categories_parent_id` (`parent_id`),
  KEY `idx_sm_categories_name` (`name`),
  CONSTRAINT `fk_sm_categories_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_categories_parent`
    FOREIGN KEY (`parent_id`) REFERENCES `sm_categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vendor category mapping
CREATE TABLE `sm_vendor_category_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendor_id` BIGINT UNSIGNED NOT NULL,
  `category_id` BIGINT UNSIGNED NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `source` VARCHAR(80) NULL,
  `confidence_score` DECIMAL(5,2) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_vendor_category_map` (`vendor_id`,`category_id`),
  KEY `idx_sm_vendor_category_map_category_id` (`category_id`),
  KEY `idx_sm_vendor_category_map_primary` (`vendor_id`,`is_primary`),
  CONSTRAINT `fk_sm_vendor_category_map_vendor`
    FOREIGN KEY (`vendor_id`) REFERENCES `sm_vendors` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_vendor_category_map_category`
    FOREIGN KEY (`category_id`) REFERENCES `sm_categories` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Public searchable listing
CREATE TABLE `sm_listings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `vendor_id` BIGINT UNSIGNED NOT NULL,
  `primary_location_id` BIGINT UNSIGNED NULL,
  `listing_type` ENUM('organic','claimed','promoted','imported') NOT NULL DEFAULT 'organic',
  `title` VARCHAR(190) NOT NULL,
  `subtitle` VARCHAR(255) NULL,
  `slug` VARCHAR(190) NOT NULL,
  `summary` VARCHAR(500) NULL,
  `body_content` LONGTEXT NULL,
  `primary_category_id` BIGINT UNSIGNED NULL,
  `listing_status` ENUM('draft','pending','published','suspended','archived') NOT NULL DEFAULT 'draft',
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'unverified',
  `ranking_score` DECIMAL(10,4) NULL DEFAULT 0.0000,
  `quality_score` DECIMAL(10,4) NULL DEFAULT 0.0000,
  `completeness_score` DECIMAL(10,4) NULL DEFAULT 0.0000,
  `review_score` DECIMAL(5,2) NULL DEFAULT 0.00,
  `review_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `image_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `video_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `claimed_at` DATETIME NULL,
  `published_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_listings_uuid` (`uuid`),
  UNIQUE KEY `uq_sm_listings_slug` (`slug`),
  KEY `idx_sm_listings_vendor_id` (`vendor_id`),
  KEY `idx_sm_listings_primary_location_id` (`primary_location_id`),
  KEY `idx_sm_listings_primary_category_id` (`primary_category_id`),
  KEY `idx_sm_listings_status_type` (`listing_status`,`listing_type`),
  KEY `idx_sm_listings_ranking_score` (`ranking_score`),
  CONSTRAINT `fk_sm_listings_vendor`
    FOREIGN KEY (`vendor_id`) REFERENCES `sm_vendors` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_listings_primary_location`
    FOREIGN KEY (`primary_location_id`) REFERENCES `sm_vendor_locations` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_listings_primary_category`
    FOREIGN KEY (`primary_category_id`) REFERENCES `sm_categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Listing media
CREATE TABLE `sm_listing_media` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `listing_id` BIGINT UNSIGNED NOT NULL,
  `media_type` ENUM('image','video','document','logo') NOT NULL,
  `storage_disk` VARCHAR(50) NOT NULL,
  `storage_path` VARCHAR(500) NOT NULL,
  `mime_type` VARCHAR(120) NULL,
  `title` VARCHAR(190) NULL,
  `alt_text` VARCHAR(255) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_listing_media_listing_id` (`listing_id`),
  KEY `idx_sm_listing_media_type_status` (`media_type`,`status`),
  CONSTRAINT `fk_sm_listing_media_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Flexible listing attributes
CREATE TABLE `sm_listing_attributes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `listing_id` BIGINT UNSIGNED NOT NULL,
  `attribute_key` VARCHAR(120) NOT NULL,
  `attribute_value` TEXT NULL,
  `attribute_type` VARCHAR(50) NULL,
  `source` VARCHAR(80) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_listing_attributes_listing_id` (`listing_id`),
  KEY `idx_sm_listing_attributes_key` (`attribute_key`),
  CONSTRAINT `fk_sm_listing_attributes_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- External data sources
CREATE TABLE `sm_sources` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(100) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `source_type` ENUM('api','scrape','upload','manual','partner') NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `config_json` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_sources_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Import batches
CREATE TABLE `sm_source_import_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `source_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `initiated_by_user_id` BIGINT UNSIGNED NULL,
  `import_type` VARCHAR(80) NOT NULL,
  `search_term` VARCHAR(255) NULL,
  `search_geo` VARCHAR(255) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `total_records` INT UNSIGNED NOT NULL DEFAULT 0,
  `processed_records` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_source_import_batches_uuid` (`uuid`),
  KEY `idx_sm_source_import_batches_source_id` (`source_id`),
  KEY `idx_sm_source_import_batches_tenant_id` (`tenant_id`),
  KEY `idx_sm_source_import_batches_user_id` (`initiated_by_user_id`),
  CONSTRAINT `fk_sm_source_import_batches_source`
    FOREIGN KEY (`source_id`) REFERENCES `sm_sources` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_source_import_batches_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_source_import_batches_user`
    FOREIGN KEY (`initiated_by_user_id`) REFERENCES `sm_users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Raw source records
CREATE TABLE `sm_source_records` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `external_record_id` VARCHAR(255) NOT NULL,
  `external_url` VARCHAR(500) NULL,
  `raw_payload_json` JSON NOT NULL,
  `normalized_payload_json` JSON NULL,
  `dedupe_hash` CHAR(64) NULL,
  `processing_status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_source_records_source_id` (`source_id`),
  KEY `idx_sm_source_records_batch_id` (`batch_id`),
  KEY `idx_sm_source_records_external_record_id` (`external_record_id`),
  KEY `idx_sm_source_records_dedupe_hash` (`dedupe_hash`),
  CONSTRAINT `fk_sm_source_records_source`
    FOREIGN KEY (`source_id`) REFERENCES `sm_sources` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_source_records_batch`
    FOREIGN KEY (`batch_id`) REFERENCES `sm_source_import_batches` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mapping vendor to external source identity
CREATE TABLE `sm_vendor_source_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendor_id` BIGINT UNSIGNED NOT NULL,
  `source_id` BIGINT UNSIGNED NOT NULL,
  `external_record_id` VARCHAR(255) NOT NULL,
  `external_place_id` VARCHAR(255) NULL,
  `source_url` VARCHAR(500) NULL,
  `confidence_score` DECIMAL(5,2) NULL,
  `is_primary_source` TINYINT(1) NOT NULL DEFAULT 0,
  `last_seen_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_vendor_source_map` (`vendor_id`,`source_id`,`external_record_id`),
  KEY `idx_sm_vendor_source_map_source_id` (`source_id`),
  KEY `idx_sm_vendor_source_map_external_place_id` (`external_place_id`),
  CONSTRAINT `fk_sm_vendor_source_map_vendor`
    FOREIGN KEY (`vendor_id`) REFERENCES `sm_vendors` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_vendor_source_map_source`
    FOREIGN KEY (`source_id`) REFERENCES `sm_sources` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Search query log
CREATE TABLE `sm_search_queries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `session_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `raw_query` VARCHAR(255) NOT NULL,
  `normalized_query` VARCHAR(255) NULL,
  `detected_location` VARCHAR(255) NULL,
  `category_id` BIGINT UNSIGNED NULL,
  `latitude` DECIMAL(10,7) NULL,
  `longitude` DECIMAL(10,7) NULL,
  `radius_miles` DECIMAL(8,2) NULL,
  `intent_type` VARCHAR(50) NULL,
  `result_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `search_source` VARCHAR(50) NOT NULL DEFAULT 'site',
  `searched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_search_queries_uuid` (`uuid`),
  KEY `idx_sm_search_queries_tenant_id` (`tenant_id`),
  KEY `idx_sm_search_queries_session_id` (`session_id`),
  KEY `idx_sm_search_queries_user_id` (`user_id`),
  KEY `idx_sm_search_queries_category_id` (`category_id`),
  KEY `idx_sm_search_queries_searched_at` (`searched_at`),
  CONSTRAINT `fk_sm_search_queries_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_search_queries_user`
    FOREIGN KEY (`user_id`) REFERENCES `sm_users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_search_queries_category`
    FOREIGN KEY (`category_id`) REFERENCES `sm_categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Session tracking
CREATE TABLE `sm_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `account_id` BIGINT UNSIGNED NULL,
  `session_source` VARCHAR(50) NOT NULL DEFAULT 'site',
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `referrer` VARCHAR(500) NULL,
  `device_type` VARCHAR(50) NULL,
  `geo_json` JSON NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_sessions_uuid` (`uuid`),
  KEY `idx_sm_sessions_tenant_id` (`tenant_id`),
  KEY `idx_sm_sessions_user_id` (`user_id`),
  KEY `idx_sm_sessions_account_id` (`account_id`),
  KEY `idx_sm_sessions_started_at` (`started_at`),
  CONSTRAINT `fk_sm_sessions_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_sessions_user`
    FOREIGN KEY (`user_id`) REFERENCES `sm_users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_sessions_account`
    FOREIGN KEY (`account_id`) REFERENCES `sm_accounts` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `sm_search_queries`
  ADD CONSTRAINT `fk_sm_search_queries_session`
  FOREIGN KEY (`session_id`) REFERENCES `sm_sessions` (`id`)
  ON DELETE SET NULL ON UPDATE CASCADE;

-- Search result snapshot
CREATE TABLE `sm_search_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `search_query_id` BIGINT UNSIGNED NOT NULL,
  `listing_id` BIGINT UNSIGNED NOT NULL,
  `rank_position` INT UNSIGNED NOT NULL,
  `ranking_score` DECIMAL(10,4) NULL DEFAULT 0.0000,
  `was_promoted` TINYINT(1) NOT NULL DEFAULT 0,
  `promotion_source` VARCHAR(80) NULL,
  `shown_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_search_results_rank` (`search_query_id`,`rank_position`),
  KEY `idx_sm_search_results_listing_id` (`listing_id`),
  KEY `idx_sm_search_results_promoted` (`was_promoted`),
  CONSTRAINT `fk_sm_search_results_query`
    FOREIGN KEY (`search_query_id`) REFERENCES `sm_search_queries` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_search_results_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Impression events
CREATE TABLE `sm_event_impressions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `search_query_id` BIGINT UNSIGNED NULL,
  `listing_id` BIGINT UNSIGNED NULL,
  `position` INT UNSIGNED NULL,
  `context_type` VARCHAR(50) NOT NULL DEFAULT 'search_results',
  `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_event_impressions_session_id` (`session_id`),
  KEY `idx_sm_event_impressions_query_id` (`search_query_id`),
  KEY `idx_sm_event_impressions_listing_id` (`listing_id`),
  KEY `idx_sm_event_impressions_occurred_at` (`occurred_at`),
  CONSTRAINT `fk_sm_event_impressions_session`
    FOREIGN KEY (`session_id`) REFERENCES `sm_sessions` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_event_impressions_query`
    FOREIGN KEY (`search_query_id`) REFERENCES `sm_search_queries` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_event_impressions_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Click events
CREATE TABLE `sm_event_clicks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `search_query_id` BIGINT UNSIGNED NULL,
  `listing_id` BIGINT UNSIGNED NULL,
  `click_type` ENUM('listing','website','phone','direction','claim','ad') NOT NULL,
  `destination_url` VARCHAR(500) NULL,
  `fraud_score` DECIMAL(5,2) NULL,
  `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_event_clicks_session_id` (`session_id`),
  KEY `idx_sm_event_clicks_query_id` (`search_query_id`),
  KEY `idx_sm_event_clicks_listing_id` (`listing_id`),
  KEY `idx_sm_event_clicks_type` (`click_type`),
  KEY `idx_sm_event_clicks_occurred_at` (`occurred_at`),
  CONSTRAINT `fk_sm_event_clicks_session`
    FOREIGN KEY (`session_id`) REFERENCES `sm_sessions` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_event_clicks_query`
    FOREIGN KEY (`search_query_id`) REFERENCES `sm_search_queries` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_event_clicks_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Claim requests
CREATE TABLE `sm_claim_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `vendor_id` BIGINT UNSIGNED NOT NULL,
  `listing_id` BIGINT UNSIGNED NULL,
  `requester_name` VARCHAR(190) NOT NULL,
  `requester_email` VARCHAR(190) NOT NULL,
  `requester_phone` VARCHAR(40) NULL,
  `requester_role` VARCHAR(100) NULL,
  `business_email` VARCHAR(190) NULL,
  `submitted_payload_json` JSON NULL,
  `verification_method` VARCHAR(50) NULL,
  `status` ENUM('pending','verifying','approved','denied','expired') NOT NULL DEFAULT 'pending',
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME NULL,
  `resolved_by_user_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_claim_requests_uuid` (`uuid`),
  KEY `idx_sm_claim_requests_vendor_id` (`vendor_id`),
  KEY `idx_sm_claim_requests_listing_id` (`listing_id`),
  KEY `idx_sm_claim_requests_status` (`status`),
  KEY `idx_sm_claim_requests_resolved_by` (`resolved_by_user_id`),
  CONSTRAINT `fk_sm_claim_requests_vendor`
    FOREIGN KEY (`vendor_id`) REFERENCES `sm_vendors` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_claim_requests_listing`
    FOREIGN KEY (`listing_id`) REFERENCES `sm_listings` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_claim_requests_resolved_by`
    FOREIGN KEY (`resolved_by_user_id`) REFERENCES `sm_users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Claim verification steps
CREATE TABLE `sm_claim_verifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `claim_request_id` BIGINT UNSIGNED NOT NULL,
  `verification_type` ENUM('email','phone','domain','document','manual') NOT NULL,
  `verification_target` VARCHAR(255) NULL,
  `verification_code` VARCHAR(100) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `evidence_json` JSON NULL,
  `verified_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_claim_verifications_claim_request_id` (`claim_request_id`),
  KEY `idx_sm_claim_verifications_status` (`status`),
  CONSTRAINT `fk_sm_claim_verifications_claim_request`
    FOREIGN KEY (`claim_request_id`) REFERENCES `sm_claim_requests` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Job definitions
CREATE TABLE `sm_job_definitions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_code` VARCHAR(150) NOT NULL,
  `job_name` VARCHAR(190) NOT NULL,
  `job_domain` VARCHAR(50) NOT NULL,
  `queue_name` VARCHAR(80) NOT NULL DEFAULT 'default',
  `retry_limit` SMALLINT UNSIGNED NOT NULL DEFAULT 3,
  `timeout_seconds` INT UNSIGNED NOT NULL DEFAULT 300,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_job_definitions_job_code` (`job_code`),
  KEY `idx_sm_job_definitions_domain` (`job_domain`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Job queue
CREATE TABLE `sm_job_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `job_definition_id` BIGINT UNSIGNED NOT NULL,
  `tenant_id` BIGINT UNSIGNED NULL,
  `related_type` VARCHAR(120) NULL,
  `related_id` BIGINT UNSIGNED NULL,
  `parent_job_id` BIGINT UNSIGNED NULL,
  `priority` INT NOT NULL DEFAULT 100,
  `scheduled_for` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `payload_json` JSON NULL,
  `status` ENUM('queued','reserved','running','completed','failed','canceled') NOT NULL DEFAULT 'queued',
  `attempt_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `max_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 3,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_job_queue_uuid` (`uuid`),
  KEY `idx_sm_job_queue_job_definition_id` (`job_definition_id`),
  KEY `idx_sm_job_queue_tenant_id` (`tenant_id`),
  KEY `idx_sm_job_queue_parent_job_id` (`parent_job_id`),
  KEY `idx_sm_job_queue_status_scheduled` (`status`,`scheduled_for`),
  CONSTRAINT `fk_sm_job_queue_job_definition`
    FOREIGN KEY (`job_definition_id`) REFERENCES `sm_job_definitions` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_job_queue_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sm_job_queue_parent_job`
    FOREIGN KEY (`parent_job_id`) REFERENCES `sm_job_queue` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Job execution attempts
CREATE TABLE `sm_job_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_queue_id` BIGINT UNSIGNED NOT NULL,
  `run_number` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `worker_name` VARCHAR(150) NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` DATETIME NULL,
  `status` ENUM('running','completed','failed','canceled') NOT NULL DEFAULT 'running',
  `output_json` JSON NULL,
  `metrics_json` JSON NULL,
  `error_message` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_job_runs_job_queue_id` (`job_queue_id`),
  KEY `idx_sm_job_runs_status` (`status`),
  KEY `idx_sm_job_runs_started_at` (`started_at`),
  CONSTRAINT `fk_sm_job_runs_job_queue`
    FOREIGN KEY (`job_queue_id`) REFERENCES `sm_job_queue` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Job error records
CREATE TABLE `sm_job_errors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_run_id` BIGINT UNSIGNED NOT NULL,
  `error_code` VARCHAR(80) NULL,
  `error_class` VARCHAR(190) NULL,
  `error_message` TEXT NOT NULL,
  `stack_trace` LONGTEXT NULL,
  `context_json` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sm_job_errors_job_run_id` (`job_run_id`),
  CONSTRAINT `fk_sm_job_errors_job_run`
    FOREIGN KEY (`job_run_id`) REFERENCES `sm_job_runs` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
```

This gives Claude a clean starting point and keeps the new database aligned with the real platform direction instead of dragging legacy table design forward from `Searchmercials_Structure.sql`. 

The next step should be the **Laravel migration plan**, because once Claude creates this schema, we want your Laravel models, migrations, factories, and seed order to match it exactly.