-- =====================================================================
-- ⚠️  DESTRUCTIVE — BASELINE SCHEMA FOR FRESH DATABASES ONLY  ⚠️
-- =====================================================================
--
--   This file DROPS and RECREATES every core table. Running it against
--   a populated database WILL wipe tenants, users, accounts, vendors,
--   listings, locations, sessions, and everything else enumerated in
--   the DROP TABLE block below.
--
--   DO NOT PIPE THIS FILE INTO MYSQL WITHOUT CHECKING FIRST.
--
--   Use cases (in order of safety):
--     ✓ Spinning up a brand-new local/test database from scratch
--     ✓ CI seed step where the DB is explicitly disposable
--     ✗ Production
--     ✗ Any populated dev DB you don't want to re-seed from scratch
--
--   If you only need to add the newer tables or columns, use
--   searchmercials_v2_phase1-incremental.sql instead — that file is
--   additive and safe to re-run.
--
--   Before running:
--     mysqldump -u root searchmercials_v2 > /tmp/backup-$(date +%s).sql
--
--   After running, re-seed app data:
--     php artisan db:seed --force
--
-- =====================================================================

-- =====================================================================
-- Searchmercials v2 — Phase 1 schema
-- Target: MySQL 8 / Laravel 10+
-- Database: searchmercials_v2
--
-- Source DDL: searchmercials_v2_DDL.md
-- Reviewed against:
--   /var/www/html/Searchmercials/Documentation/{02-data-model,03-modules-breakdown,
--     07-code-health,08-security-audit,13-affiliates-and-data}.md
--   /var/www/html/searchmercials_v2/Documentation/Searchmercials_Structure.sql
--
-- Material changes vs the DDL draft:
--   1. sm_listings.tenant_id column + FK + composite UNIQUE(tenant_id, slug)
--      and composite index for tenant-scoped queries.
--   2. sm_users.email_verified_at + remember_token (Laravel auth conventions).
--   3. sm_accounts.account_type and sm_listings.listing_type changed from
--      ENUM to VARCHAR(40) for forward-compatibility with legacy account/
--      listing-type taxonomies.
--   4. sm_search_queries.raw_query widened from VARCHAR(255) to VARCHAR(500)
--      and a composite (tenant_id, searched_at) index added for time-series
--      reporting.
--   5. sm_listings.listing_status retained as ENUM (stable workflow).
--   6. Table-level and column-level COMMENTs throughout (self-describing schema).
-- =====================================================================

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

USE `searchmercials_v2`;



-- ---------------------------------------------------------------------
-- sm_tenants
-- Description: Root tenant registry for the Searchmercials v2 platform.
-- Purpose: Defines each branded or white-label tenant environment and
--   serves as the top-level scope for domains, accounts, categories,
--   listings, and activity data.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_tenants` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for tenant',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `name` VARCHAR(150) NOT NULL COMMENT 'Human-readable tenant name',
  `slug` VARCHAR(150) NOT NULL COMMENT 'URL-safe tenant slug used in routing and references',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, suspended, archived, etc.)',
  `settings_json` JSON NULL COMMENT 'Tenant-level configuration: branding, feature flags, search defaults, locale',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Root tenant registry for white-label environments; top-level scope for all platform data.';

-- ---------------------------------------------------------------------
-- sm_accounts
-- Description: Organizational account record for businesses, advertisers,
--   publishers, agencies, and admins.
-- Purpose: Stores the owning business or platform entity that users belong
--   to and that may later participate in billing, listing ownership,
--   advertising, or publisher relationships.
-- Notes: account_type is VARCHAR (not ENUM) so legacy account-type values
--   can be migrated without a schema change; a reference table may be
--   added later if a controlled vocabulary is needed.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for account',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (tenant scope)',
  `account_type` VARCHAR(40) NOT NULL DEFAULT 'vendor' COMMENT 'Role classification: vendor, advertiser, publisher, admin, agency (extensible)',
  `name` VARCHAR(150) NOT NULL COMMENT 'Display name for the account',
  `legal_name` VARCHAR(200) NULL COMMENT 'Registered legal entity name if different from display',
  `email` VARCHAR(190) NULL COMMENT 'Primary contact email for the account',
  `phone` VARCHAR(40) NULL COMMENT 'Primary contact phone for the account',
  `website` VARCHAR(255) NULL COMMENT 'Primary website URL for the account',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, suspended, closed, etc.)',
  `lifecycle_stage` VARCHAR(50) NULL COMMENT 'CRM-style lifecycle stage (lead, onboarding, customer, churned, etc.)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Organizational accounts (vendors, advertisers, publishers, admins) that own users and participate in platform activity.';

-- ---------------------------------------------------------------------
-- sm_users
-- Description: Human user accounts associated with platform accounts.
-- Purpose: Stores login-capable users for the system, including
--   authentication-related fields needed by Laravel and the relationship
--   to the parent account.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for user',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `account_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_accounts (owning account)',
  `first_name` VARCHAR(100) NOT NULL COMMENT 'User first name',
  `last_name` VARCHAR(100) NOT NULL COMMENT 'User last name',
  `email` VARCHAR(190) NOT NULL COMMENT 'Login email (globally unique)',
  `email_verified_at` DATETIME NULL COMMENT 'Timestamp of email verification (Laravel convention)',
  `password_hash` VARCHAR(255) NOT NULL COMMENT 'Hashed password (bcrypt/argon via Laravel Hash facade)',
  `remember_token` VARCHAR(100) NULL COMMENT '"Remember me" auth token (Laravel convention)',
  `phone` VARCHAR(40) NULL COMMENT 'Optional contact phone number',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, disabled, locked, etc.)',
  `last_login_at` DATETIME NULL COMMENT 'Timestamp of most recent successful login',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Human user accounts with authentication data linked to an account.';

-- ---------------------------------------------------------------------
-- sm_roles
-- Description: Master list of application roles.
-- Purpose: Defines reusable authorization roles such as admin, manager,
--   publisher, advertiser, or support for access control across the platform.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for role',
  `code` VARCHAR(80) NOT NULL COMMENT 'Machine-readable role code used in authorization checks (e.g., admin, publisher)',
  `name` VARCHAR(120) NOT NULL COMMENT 'Human-readable role name',
  `description` VARCHAR(255) NULL COMMENT 'Optional description of role responsibilities',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Master role definitions for access control.';

-- ---------------------------------------------------------------------
-- sm_user_role_map
-- Description: Tenant-scoped assignment of roles to users.
-- Purpose: Connects users to roles within a tenant so permissions can be
--   managed cleanly in multi-tenant environments.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_user_role_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for role assignment',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_users',
  `role_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_roles',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (role is scoped to this tenant)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Tenant-scoped mapping of users to roles for authorization.';

-- ---------------------------------------------------------------------
-- sm_domains
-- Description: Domain mapping table for branded or white-label access.
-- Purpose: Associates custom domains with tenants and tracks whether each
--   domain is primary, active, and SSL-ready.
-- Notes: Replaces the per-brand vhost configuration encoded in legacy
--   `fixlinks.sh` and the per-brand redirect_url maps in /data/config/*.inc.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_domains` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for domain record',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (owning tenant)',
  `domain` VARCHAR(190) NOT NULL COMMENT 'Fully-qualified domain name (hostname only, no scheme)',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag indicating primary canonical domain for the tenant (0/1)',
  `ssl_status` VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT 'TLS provisioning state (pending, issued, failed, expired)',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, disabled, etc.)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Domain mapping for tenant-specific branding and access.';

-- ---------------------------------------------------------------------
-- sm_vendors
-- Description: Canonical business entity table.
-- Purpose: Represents the real-world business or service provider being
--   indexed, claimed, enriched, and shown through listings on Searchmercials.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_vendors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for vendor',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Tenant that owns this vendor record',
  `primary_account_id` BIGINT UNSIGNED NULL COMMENT 'Owning account if vendor is claimed/managed',
  `canonical_name` VARCHAR(190) NOT NULL COMMENT 'Normalized canonical business name used for deduplication',
  `legal_name` VARCHAR(190) NULL COMMENT 'Registered legal business name if available',
  `display_name` VARCHAR(190) NOT NULL COMMENT 'Frontend display name shown to users',
  `description_short` VARCHAR(500) NULL COMMENT 'Short summary description for listing previews',
  `description_long` TEXT NULL COMMENT 'Full detailed business description',
  `website` VARCHAR(255) NULL COMMENT 'Primary website URL of the business',
  `email` VARCHAR(190) NULL COMMENT 'Primary contact email',
  `phone` VARCHAR(40) NULL COMMENT 'Primary contact phone number',
  `claim_status` ENUM('unclaimed','pending','claimed','locked') NOT NULL DEFAULT 'unclaimed'
    COMMENT 'Ownership status: unclaimed=no owner; pending=claim in review; claimed=owned; locked=admin-frozen',
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'unverified'
    COMMENT 'Verification state (unverified, verified, rejected, etc.)',
  `source_confidence_score` DECIMAL(5,2) NULL COMMENT 'Confidence score from ingestion sources (0.00-100.00)',
  `enrichment_status` VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT 'AI/data enrichment pipeline status (pending, in_progress, enriched, failed)',
  `active_status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational state of vendor (active, disabled, merged, etc.)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Canonical business entities representing real-world vendors.';

-- ---------------------------------------------------------------------
-- sm_vendor_locations
-- Description: Physical or service-area locations for vendors.
-- Purpose: Supports multi-location businesses by storing address, contact,
--   and geospatial data for each vendor location.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_vendor_locations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for location',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for location',
  `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_vendors (owning vendor)',
  `location_name` VARCHAR(190) NULL COMMENT 'Optional name for location (e.g., "Downtown Office")',
  `address1` VARCHAR(190) NULL COMMENT 'Primary street address line',
  `address2` VARCHAR(190) NULL COMMENT 'Secondary address info (suite, unit, floor)',
  `city` VARCHAR(120) NULL COMMENT 'City name',
  `state` VARCHAR(120) NULL COMMENT 'State, province, or region',
  `postal_code` VARCHAR(20) NULL COMMENT 'ZIP or postal code',
  `country_code` CHAR(2) NULL COMMENT 'ISO 3166-1 alpha-2 country code',
  `latitude` DECIMAL(10,7) NULL COMMENT 'Latitude in decimal degrees for geospatial queries',
  `longitude` DECIMAL(10,7) NULL COMMENT 'Longitude in decimal degrees for geospatial queries',
  `phone` VARCHAR(40) NULL COMMENT 'Location-specific phone number',
  `email` VARCHAR(190) NULL COMMENT 'Location-specific email',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag indicating primary location for the vendor (0/1)',
  `service_area_json` JSON NULL COMMENT 'Service coverage area for mobile/home-service businesses: {type, polygons, radius_miles, cities, postal_codes}',
  `geo_hash` VARCHAR(32) NULL COMMENT 'Geohash string for spatial bucketing and proximity queries',
  `active_status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Location operational status (active, closed, temporary_closed)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Vendor physical or service-area locations with geospatial data.';

-- ---------------------------------------------------------------------
-- sm_categories
-- Description: Hierarchical listing and vendor taxonomy.
-- Purpose: Stores the normalized category tree used to organize vendors,
--   listings, searches, and future monetization targets.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for category',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for category',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (tenant scope)',
  `parent_id` BIGINT UNSIGNED NULL COMMENT 'Self-referencing foreign key to parent category (NULL for root nodes)',
  `slug` VARCHAR(190) NOT NULL COMMENT 'URL-safe slug, unique within a tenant',
  `name` VARCHAR(190) NOT NULL COMMENT 'Display name shown to users',
  `full_path` VARCHAR(500) NULL COMMENT 'Denormalized breadcrumb path (e.g., "Home Services / Plumbing / Emergency")',
  `level_depth` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Depth in the tree; 0 = root',
  `description` TEXT NULL COMMENT 'Optional long-form category description',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, hidden, archived)',
  `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display ordering within the parent (ascending)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Hierarchical category taxonomy for organizing vendors and listings.';

-- ---------------------------------------------------------------------
-- sm_vendor_category_map
-- Description: Many-to-many mapping between vendors and categories.
-- Purpose: Connects vendors to one or more categories while allowing
--   confidence scoring and primary-category designation.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_vendor_category_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for mapping row',
  `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_vendors',
  `category_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_categories',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag indicating the vendor''s primary category (0/1)',
  `source` VARCHAR(80) NULL COMMENT 'Origin of the mapping (manual, import:google, enrich:ai, etc.)',
  `confidence_score` DECIMAL(5,2) NULL COMMENT 'Confidence the vendor belongs to this category (0.00-100.00)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Mapping of vendors to categories with primary flag and confidence scoring.';

-- ---------------------------------------------------------------------
-- sm_listings
-- Description: Public-facing searchable listings for vendors.
-- Purpose: Represents the publishable search result entity shown to users,
--   separate from the canonical vendor record, with tenant scoping,
--   ranking, quality, and workflow status.
-- Notes: tenant_id is denormalized from vendor for query performance and
--   to enforce per-tenant slug uniqueness. listing_type is VARCHAR (not
--   ENUM) so legacy listing types from iweb_hyperseek_* tables can be
--   carried in as-is. listing_status remains an ENUM because it's a
--   stable workflow.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_listings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for listing',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Tenant scope (denormalized from vendor for query performance)',
  `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_vendors (backing business entity)',
  `primary_location_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_vendor_locations (primary shown address)',
  `listing_type` VARCHAR(40) NOT NULL DEFAULT 'organic' COMMENT 'Listing flavor: organic, claimed, promoted, imported (extensible)',
  `title` VARCHAR(190) NOT NULL COMMENT 'Primary listing title',
  `subtitle` VARCHAR(255) NULL COMMENT 'Secondary title or tagline',
  `slug` VARCHAR(190) NOT NULL COMMENT 'SEO-friendly URL slug, unique within the tenant',
  `summary` VARCHAR(500) NULL COMMENT 'Short summary for search-result display',
  `body_content` LONGTEXT NULL COMMENT 'Full listing content body (markdown or HTML)',
  `primary_category_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_categories (primary classification)',
  `listing_status` ENUM('draft','pending','published','suspended','archived') NOT NULL DEFAULT 'draft'
    COMMENT 'Publishing workflow: draft=editing; pending=review; published=live; suspended=blocked; archived=retired',
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'unverified' COMMENT 'Listing verification state (unverified, verified, rejected, etc.)',
  `ranking_score` DECIMAL(10,4) NULL DEFAULT 0.0000 COMMENT 'Computed search ranking score (higher = higher)',
  `quality_score` DECIMAL(10,4) NULL DEFAULT 0.0000 COMMENT 'Content/data quality score for downgrading poor listings',
  `completeness_score` DECIMAL(10,4) NULL DEFAULT 0.0000 COMMENT 'Profile completeness score (0-100 scale nominal)',
  `review_score` DECIMAL(5,2) NULL DEFAULT 0.00 COMMENT 'Average user rating (e.g., 0.00-5.00)',
  `review_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cached count of reviews',
  `image_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cached count of attached images',
  `video_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cached count of attached videos',
  `claimed_at` DATETIME NULL COMMENT 'Timestamp when the vendor claimed this listing',
  `published_at` DATETIME NULL COMMENT 'Timestamp when the listing first went live',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_listings_uuid` (`uuid`),
  UNIQUE KEY `uq_sm_listings_tenant_slug` (`tenant_id`,`slug`),
  KEY `idx_sm_listings_tenant_id` (`tenant_id`),
  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_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `sm_tenants` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  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
  COMMENT='Public searchable listings representing vendors with ranking and workflow states.';

-- ---------------------------------------------------------------------
-- sm_listing_media
-- Description: Media assets attached to listings.
-- Purpose: Stores references to images, videos, documents, or logos
--   associated with a listing for search display and profile presentation.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_listing_media` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for media asset',
  `listing_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_listings',
  `media_type` ENUM('image','video','document','logo') NOT NULL COMMENT 'Asset kind: image=photo; video=clip; document=PDF/doc; logo=brand mark',
  `storage_disk` VARCHAR(50) NOT NULL COMMENT 'Laravel filesystem disk name (s3, public, local, etc.)',
  `storage_path` VARCHAR(500) NOT NULL COMMENT 'Path or key within the storage disk',
  `mime_type` VARCHAR(120) NULL COMMENT 'MIME type (image/jpeg, video/mp4, application/pdf, ...)',
  `title` VARCHAR(190) NULL COMMENT 'Optional title/caption for the asset',
  `alt_text` VARCHAR(255) NULL COMMENT 'Accessibility alt-text for images',
  `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display ordering within the listing (ascending)',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, pending_scan, rejected, deleted)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Media assets (images, video, documents, logos) attached to listings.';

-- ---------------------------------------------------------------------
-- sm_listing_attributes
-- Description: Flexible attribute store for listing metadata.
-- Purpose: Holds semi-structured listing facts such as amenities, services,
--   payment methods, certifications, or custom attributes without
--   requiring schema changes.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_listing_attributes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for attribute row',
  `listing_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_listings',
  `attribute_key` VARCHAR(120) NOT NULL COMMENT 'Attribute key (e.g., "payment_methods", "hours.mon", "has_wifi")',
  `attribute_value` TEXT NULL COMMENT 'Attribute value as stored text (may be JSON-encoded when needed)',
  `attribute_type` VARCHAR(50) NULL COMMENT 'Value type hint: string, bool, int, decimal, json, datetime',
  `source` VARCHAR(80) NULL COMMENT 'Origin of the attribute (manual, import:google, enrich:ai, etc.)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Flexible key-value attribute storage for listing metadata (EAV pattern).';

-- ---------------------------------------------------------------------
-- sm_sources
-- Description: Registry of external or internal data sources.
-- Purpose: Defines where imported records originate, such as APIs,
--   scrapers, uploads, manual entry, or partner feeds.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_sources` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for source',
  `code` VARCHAR(100) NOT NULL COMMENT 'Machine-readable source code (e.g., google_places, yelp_api, csv_upload)',
  `name` VARCHAR(150) NOT NULL COMMENT 'Human-readable source name',
  `source_type` ENUM('api','scrape','upload','manual','partner') NOT NULL
    COMMENT 'Acquisition mode: api=REST/GraphQL; scrape=crawler; upload=user CSV; manual=hand entry; partner=feed',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active' COMMENT 'Operational status (active, paused, deprecated)',
  `config_json` JSON NULL COMMENT 'Source-specific config: endpoints, keys (ref), rate limits, auth mode, mapping rules',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sm_sources_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Registry of external or internal data sources for ingestion.';

-- ---------------------------------------------------------------------
-- sm_source_import_batches
-- Description: Import batch tracker for source ingestion runs.
-- Purpose: Groups source records into a single import session so progress,
--   origin, operator, and processing status can be tracked.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_source_import_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for batch',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for the batch run',
  `source_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_sources',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (tenant scope)',
  `initiated_by_user_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_users (operator who started the batch; NULL for system)',
  `import_type` VARCHAR(80) NOT NULL COMMENT 'Import mode code (full_sync, incremental, search_query, upload, enrich_only)',
  `search_term` VARCHAR(255) NULL COMMENT 'Original search term if the batch was driven by a query',
  `search_geo` VARCHAR(255) NULL COMMENT 'Original geographic filter if the batch was driven by a geo query',
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT 'Batch lifecycle: pending, running, completed, failed, canceled',
  `total_records` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Expected total record count for the batch',
  `processed_records` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Records processed so far',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `completed_at` DATETIME NULL COMMENT 'Timestamp when the batch finished (success or failure)',
  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
  COMMENT='Batch tracking for data import operations from sources.';

-- ---------------------------------------------------------------------
-- sm_source_records
-- Description: Raw and normalized source data records.
-- Purpose: Stores imported upstream records before and during normalization
--   so vendor creation, deduplication, and auditing can be performed safely.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_source_records` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for raw source record',
  `source_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_sources',
  `batch_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_source_import_batches',
  `external_record_id` VARCHAR(255) NOT NULL COMMENT 'Record ID as issued by the external source',
  `external_url` VARCHAR(500) NULL COMMENT 'URL of the record at the external source if applicable',
  `raw_payload_json` JSON NOT NULL COMMENT 'Verbatim payload from the source (structure varies by source)',
  `normalized_payload_json` JSON NULL COMMENT 'Normalized form: {canonical_name, address, categories, hours, contacts, ...}',
  `dedupe_hash` CHAR(64) NULL COMMENT 'SHA-256 hash of normalized identity fields for dedup lookups',
  `processing_status` VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT 'Lifecycle: pending, normalized, matched, merged, skipped, failed',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Raw and normalized records from external sources used for vendor creation and deduplication.';

-- ---------------------------------------------------------------------
-- sm_vendor_source_map
-- Description: Mapping between vendors and upstream source identities.
-- Purpose: Preserves the relationship between a normalized vendor and its
--   originating external records for deduplication, refresh, attribution,
--   and sync.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_vendor_source_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for mapping row',
  `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_vendors',
  `source_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_sources',
  `external_record_id` VARCHAR(255) NOT NULL COMMENT 'Record ID at the external source',
  `external_place_id` VARCHAR(255) NULL COMMENT 'Optional place/business ID from the source (e.g., Google Place ID)',
  `source_url` VARCHAR(500) NULL COMMENT 'URL of the record at the source if applicable',
  `confidence_score` DECIMAL(5,2) NULL COMMENT 'Match confidence between vendor and external record (0.00-100.00)',
  `is_primary_source` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag marking this source as the authoritative source for the vendor (0/1)',
  `last_seen_at` DATETIME NULL COMMENT 'Timestamp of the most recent sync/confirmation from this source',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Mapping between vendors and their external source identities.';

-- ---------------------------------------------------------------------
-- sm_search_queries
-- Description: Search activity log for all user queries.
-- Purpose: Captures what users searched for, where they searched, what
--   intent was inferred, and how many results were returned for analytics,
--   ranking, and monetization.
-- Notes: raw_query widened to 500 to accommodate long-tail queries.
--   Composite (tenant_id, searched_at) index supports tenant time-series
--   reports.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_search_queries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for search query',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (tenant scope)',
  `session_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_sessions (visitor session)',
  `user_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_users (logged-in user, if any)',
  `raw_query` VARCHAR(500) NOT NULL COMMENT 'Exact query text as typed by the user',
  `normalized_query` VARCHAR(500) NULL COMMENT 'Cleaned/normalized query used for ranking and analytics',
  `detected_location` VARCHAR(255) NULL COMMENT 'Inferred location text (city/region) extracted from query or session',
  `category_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_categories (inferred category intent)',
  `latitude` DECIMAL(10,7) NULL COMMENT 'Searcher latitude if available',
  `longitude` DECIMAL(10,7) NULL COMMENT 'Searcher longitude if available',
  `radius_miles` DECIMAL(8,2) NULL COMMENT 'Search radius in miles when used',
  `intent_type` VARCHAR(50) NULL COMMENT 'Classified search intent (local_service, info, transactional, navigational, etc.)',
  `result_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of results returned for this query',
  `search_source` VARCHAR(50) NOT NULL DEFAULT 'site' COMMENT 'Origin channel (site, api, widget, partner_feed)',
  `searched_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the search was executed',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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_tenant_searched_at` (`tenant_id`,`searched_at`),
  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
  COMMENT='Log of user search queries with intent, geo, and result metadata.';

-- ---------------------------------------------------------------------
-- sm_sessions
-- Description: Session-level activity and request context table.
-- Purpose: Tracks a browsing session across users or anonymous visitors,
--   including traffic source, device, IP, referrer, and tenant context.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for session',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier (visitor-facing session token)',
  `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_tenants (tenant scope)',
  `user_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_users (if authenticated)',
  `account_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_accounts (if authenticated)',
  `session_source` VARCHAR(50) NOT NULL DEFAULT 'site' COMMENT 'Traffic origin (site, api, widget, partner, etc.)',
  `ip_address` VARCHAR(45) NULL COMMENT 'Client IP (IPv4 or IPv6)',
  `user_agent` TEXT NULL COMMENT 'Client User-Agent string',
  `referrer` VARCHAR(500) NULL COMMENT 'HTTP referrer URL',
  `device_type` VARCHAR(50) NULL COMMENT 'Inferred device class (desktop, mobile, tablet, bot)',
  `geo_json` JSON NULL COMMENT 'Geo-enrichment payload: {country, region, city, postal, lat, lng, timezone}',
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the session began',
  `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of the most recent activity in the session',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Session tracking for users and anonymous visitors including device and referrer data.';

-- Forward-declared FK for sm_search_queries.session_id (sm_sessions defined above).
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;

-- ---------------------------------------------------------------------
-- sm_search_results
-- Description: Snapshot of search result ordering at query time.
-- Purpose: Records which listings appeared for a search and their positions
--   so ranking behavior, promoted placement, and click attribution can be
--   analyzed historically.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_search_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for result row',
  `search_query_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_search_queries',
  `listing_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_listings (result shown)',
  `rank_position` INT UNSIGNED NOT NULL COMMENT 'Zero- or one-based position in the result list at query time',
  `ranking_score` DECIMAL(10,4) NULL DEFAULT 0.0000 COMMENT 'Computed ranking score at query time (snapshot)',
  `was_promoted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag indicating the slot was paid/promoted (0/1)',
  `promotion_source` VARCHAR(80) NULL COMMENT 'Source of the promotion (campaign code, auction id, sponsor tier, etc.)',
  `shown_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the result set was rendered',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Snapshot of search result rankings for each query.';

-- ---------------------------------------------------------------------
-- sm_event_impressions
-- Description: Impression event log for shown listings or search results.
-- Purpose: Records when a listing was displayed to a user so visibility,
--   ranking exposure, and downstream performance can be measured.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_event_impressions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for impression event',
  `session_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_sessions',
  `search_query_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_search_queries (if impression was inside a search)',
  `listing_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_listings (the listing that was shown)',
  `position` INT UNSIGNED NULL COMMENT 'Display position within its context (e.g., slot in results)',
  `context_type` VARCHAR(50) NOT NULL DEFAULT 'search_results' COMMENT 'Where the impression occurred (search_results, category_page, detail_related, widget, etc.)',
  `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the impression was recorded',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Impression events tracking when listings are displayed.';

-- ---------------------------------------------------------------------
-- sm_event_clicks
-- Description: Click interaction event log.
-- Purpose: Tracks user interactions with listings such as listing opens,
--   website clicks, phone clicks, directions, claims, or ad clicks for
--   attribution and later billing logic.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_event_clicks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for click event',
  `session_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_sessions',
  `search_query_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_search_queries (if the click originated from a search)',
  `listing_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_listings (the listing clicked)',
  `click_type` ENUM('listing','website','phone','direction','claim','ad') NOT NULL
    COMMENT 'Interaction type: listing=open detail; website=outbound URL; phone=call tap; direction=map/directions; claim=claim CTA; ad=promoted unit',
  `destination_url` VARCHAR(500) NULL COMMENT 'Destination URL for website/ad clicks',
  `fraud_score` DECIMAL(5,2) NULL COMMENT 'Fraud-detection score assigned to the click (0.00-100.00; higher = more suspicious)',
  `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the click was recorded',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Click event tracking for user interactions with listings and actions.';

-- ---------------------------------------------------------------------
-- sm_claim_requests
-- Description: Listing or vendor ownership claim requests.
-- Purpose: Stores requests from users attempting to claim a business
--   listing, including requester details, verification method, and
--   approval workflow state.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_claim_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for claim request',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_vendors (target vendor)',
  `listing_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_listings (specific listing if applicable)',
  `requester_name` VARCHAR(190) NOT NULL COMMENT 'Name supplied by the claimant',
  `requester_email` VARCHAR(190) NOT NULL COMMENT 'Email supplied by the claimant',
  `requester_phone` VARCHAR(40) NULL COMMENT 'Phone supplied by the claimant',
  `requester_role` VARCHAR(100) NULL COMMENT 'Stated role of the claimant (owner, manager, agency, etc.)',
  `business_email` VARCHAR(190) NULL COMMENT 'Claimant''s business-domain email used for verification',
  `submitted_payload_json` JSON NULL COMMENT 'Raw form submission and any uploaded evidence metadata',
  `verification_method` VARCHAR(50) NULL COMMENT 'Chosen verification path (email, phone, domain, document, manual)',
  `status` ENUM('pending','verifying','approved','denied','expired') NOT NULL DEFAULT 'pending'
    COMMENT 'Workflow: pending=new; verifying=in review; approved=granted; denied=rejected; expired=timeout',
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the request was submitted',
  `resolved_at` DATETIME NULL COMMENT 'Timestamp the request reached a terminal status',
  `resolved_by_user_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_users (admin who resolved the request)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Requests to claim ownership of a vendor or listing.';

-- ---------------------------------------------------------------------
-- sm_claim_verifications
-- Description: Verification steps for claim workflows.
-- Purpose: Tracks each verification attempt or requirement tied to a claim
--   request, such as email, phone, domain, document, or manual review.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_claim_verifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for verification step',
  `claim_request_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_claim_requests',
  `verification_type` ENUM('email','phone','domain','document','manual') NOT NULL
    COMMENT 'Verification channel: email=code to email; phone=SMS/voice; domain=DNS/file; document=upload; manual=admin review',
  `verification_target` VARCHAR(255) NULL COMMENT 'Target identifier being verified (email address, phone, domain, file ref)',
  `verification_code` VARCHAR(100) NULL COMMENT 'One-time token/code issued for the verification',
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending' COMMENT 'Lifecycle (pending, sent, passed, failed, expired)',
  `evidence_json` JSON NULL COMMENT 'Structured evidence: {timestamps, ip, headers, file_refs, admin_notes}',
  `verified_at` DATETIME NULL COMMENT 'Timestamp when the step was successfully verified',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Verification steps and outcomes for claim requests.';

-- ---------------------------------------------------------------------
-- sm_job_definitions
-- Description: Registry of supported system job types.
-- Purpose: Defines reusable operational job classes such as imports,
--   normalization, enrichment, outreach, and claim verification with
--   queue and retry behavior.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_job_definitions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for job definition',
  `job_code` VARCHAR(150) NOT NULL COMMENT 'Dotted job code (e.g., import.search_results, enrich.vendor_ai)',
  `job_name` VARCHAR(190) NOT NULL COMMENT 'Human-readable job name',
  `job_domain` VARCHAR(50) NOT NULL COMMENT 'Functional domain (import, enrich, notify, claim, billing, housekeeping, etc.)',
  `queue_name` VARCHAR(80) NOT NULL DEFAULT 'default' COMMENT 'Queue/pool name the job is dispatched to',
  `retry_limit` SMALLINT UNSIGNED NOT NULL DEFAULT 3 COMMENT 'Default max retry attempts for runs of this job',
  `timeout_seconds` INT UNSIGNED NOT NULL DEFAULT 300 COMMENT 'Default per-run timeout in seconds',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Flag indicating whether the job type is dispatchable (0/1)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Definitions of system job types for background processing workflows.';

-- ---------------------------------------------------------------------
-- sm_job_queue
-- Description: Queued job execution table.
-- Purpose: Stores scheduled and in-flight jobs that drive the system's
--   operational workflows, including related records, payloads, priority,
--   and execution state.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_job_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for queued job',
  `uuid` CHAR(36) NOT NULL COMMENT 'Public unique identifier for external references',
  `job_definition_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_job_definitions',
  `tenant_id` BIGINT UNSIGNED NULL COMMENT 'Foreign key to sm_tenants (tenant scope; NULL for platform-wide jobs)',
  `related_type` VARCHAR(120) NULL COMMENT 'Polymorphic target model (e.g., App\\Models\\Vendor)',
  `related_id` BIGINT UNSIGNED NULL COMMENT 'Polymorphic target row id',
  `parent_job_id` BIGINT UNSIGNED NULL COMMENT 'Self-referencing foreign key to the job that spawned this one',
  `priority` INT NOT NULL DEFAULT 100 COMMENT 'Numeric priority (lower = more urgent in typical queue semantics)',
  `scheduled_for` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Earliest time the job is eligible to run',
  `payload_json` JSON NULL COMMENT 'Job payload: arguments, context, and options consumed by the worker',
  `status` ENUM('queued','reserved','running','completed','failed','canceled') NOT NULL DEFAULT 'queued'
    COMMENT 'Lifecycle: queued=waiting; reserved=claimed by worker; running=executing; completed/failed/canceled=terminal',
  `attempt_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of execution attempts made so far',
  `max_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 3 COMMENT 'Per-job override for maximum attempts',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update 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
  COMMENT='Queue of jobs to be executed for ingestion, enrichment, and operations.';

-- ---------------------------------------------------------------------
-- sm_job_runs
-- Description: Execution attempt history for queued jobs.
-- Purpose: Records each run attempt of a queued job so timing, output,
--   status, retries, and operational diagnostics can be tracked.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_job_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for job run',
  `job_queue_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_job_queue',
  `run_number` SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Attempt number for this queued job (1-based)',
  `worker_name` VARCHAR(150) NULL COMMENT 'Identifier of the worker/host that executed the run',
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp the run started',
  `finished_at` DATETIME NULL COMMENT 'Timestamp the run finished (null while in progress)',
  `status` ENUM('running','completed','failed','canceled') NOT NULL DEFAULT 'running'
    COMMENT 'Run outcome: running=in progress; completed/failed/canceled=terminal',
  `output_json` JSON NULL COMMENT 'Structured run output (created ids, counts, summary)',
  `metrics_json` JSON NULL COMMENT 'Structured metrics (duration_ms, memory_mb, api_calls, tokens)',
  `error_message` TEXT NULL COMMENT 'Short error summary for failed runs (details in sm_job_errors)',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Execution history of job runs including timing and status.';

-- ---------------------------------------------------------------------
-- sm_job_errors
-- Description: Detailed error records for failed job runs.
-- Purpose: Stores structured exception and context information for
--   troubleshooting failed operational jobs without overloading the main
--   job tables.
-- ---------------------------------------------------------------------
CREATE TABLE `sm_job_errors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary internal identifier for error record',
  `job_run_id` BIGINT UNSIGNED NOT NULL COMMENT 'Foreign key to sm_job_runs',
  `error_code` VARCHAR(80) NULL COMMENT 'Short error code for triage (e.g., RATE_LIMIT, AUTH_FAILED, TIMEOUT)',
  `error_class` VARCHAR(190) NULL COMMENT 'Fully-qualified exception class name',
  `error_message` TEXT NOT NULL COMMENT 'Error message body',
  `stack_trace` LONGTEXT NULL COMMENT 'Captured stack trace for diagnostics',
  `context_json` JSON NULL COMMENT 'Structured context at time of failure: {request, response, variables, external_refs}',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation 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
  COMMENT='Detailed error logs for failed job executions.';

