-- =====================================================================
-- Additive-only incremental DDL for searchmercials_v2.
-- =====================================================================
--
--   Appends to the baseline schema — never drops or truncates anything.
--   Safe to run against a populated database.
--
--   Re-runnability:
--     CREATE TABLE statements use CREATE TABLE IF NOT EXISTS, so re-runs
--       on a current DB are no-ops for those blocks.
--     ALTER TABLE ... ADD COLUMN statements are NOT idempotent on MySQL
--       8 (no ADD COLUMN IF NOT EXISTS). A second run will fail at the
--       first "Duplicate column name" error. That's a signal — it means
--       the column already exists and you probably don't need to re-run
--       this file. If you do need to, comment out the blocks that have
--       already been applied before re-running.
--
--   Workflow when adding new DDL:
--     1. Append the new block to the bottom of THIS file with a dated
--        section comment (see the existing blocks as examples).
--     2. Run just the new block against each environment (not the whole
--        file — see the rerun caveat above).
--     3. Commit.
--
--   For a fresh DB, run searchmercials_v2_phase1-baseline.sql FIRST,
--   then this file.
--
-- =====================================================================

USE `searchmercials_v2`;

-- ---------------------------------------------------------------
-- 2026-04-14: metadata system
-- Add per-page OG image + per-category SEO/OG fields. Existing
-- sm_pages already carries meta_title/meta_description/meta_keywords.
-- ---------------------------------------------------------------
ALTER TABLE sm_pages
    ADD COLUMN og_image_path VARCHAR(500) NULL AFTER meta_keywords;

ALTER TABLE sm_categories
    ADD COLUMN meta_title       VARCHAR(255) NULL AFTER description,
    ADD COLUMN meta_description TEXT         NULL AFTER meta_title,
    ADD COLUMN meta_keywords    VARCHAR(500) NULL AFTER meta_description,
    ADD COLUMN og_image_path    VARCHAR(500) NULL AFTER meta_keywords,
    ADD COLUMN og_alt_text      VARCHAR(255) NULL AFTER og_image_path;

-- ---------------------------------------------------------------
-- 2026-04-14: metadata hardening phase 2
-- Per-page/category canonical + robots overrides, plus listing
-- metadata scaffolding (no UI yet — resolver reads columns when set).
-- ---------------------------------------------------------------
ALTER TABLE sm_pages
    ADD COLUMN canonical_override VARCHAR(500) NULL AFTER og_image_path,
    ADD COLUMN robots_override    VARCHAR(100) NULL AFTER canonical_override;

ALTER TABLE sm_categories
    ADD COLUMN canonical_override VARCHAR(500) NULL AFTER og_alt_text,
    ADD COLUMN robots_override    VARCHAR(100) NULL AFTER canonical_override;

ALTER TABLE sm_listings
    ADD COLUMN meta_title       VARCHAR(255) NULL,
    ADD COLUMN meta_description TEXT         NULL,
    ADD COLUMN meta_keywords    VARCHAR(500) NULL,
    ADD COLUMN og_image_path    VARCHAR(500) NULL;

-- ---------------------------------------------------------------
-- 2026-04-14: category icon class (Font Awesome)
-- ---------------------------------------------------------------
ALTER TABLE sm_categories
    ADD COLUMN icon_class VARCHAR(255) NULL AFTER og_alt_text;

-- ---------------------------------------------------------------
-- 2026-04-14: category governance & per-category pricing
-- ---------------------------------------------------------------
ALTER TABLE sm_categories
    ADD COLUMN base_price DECIMAL(10,2) NULL AFTER icon_class;

ALTER TABLE sm_tenant_category_map
    ADD COLUMN icon_class_override VARCHAR(255) NULL,
    ADD COLUMN price_override      DECIMAL(10,2) NULL;

CREATE TABLE IF NOT EXISTS sm_category_requests (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid                  CHAR(36)        NOT NULL,
    tenant_id             BIGINT UNSIGNED NULL,
    vendor_id             BIGINT UNSIGNED NULL,
    listing_id            BIGINT UNSIGNED NULL,
    requested_by_user_id  BIGINT UNSIGNED NULL,
    request_source        VARCHAR(20)     NOT NULL DEFAULT 'platform',
    parent_category_id    BIGINT UNSIGNED NULL,
    proposed_name         VARCHAR(190)    NOT NULL,
    proposed_slug         VARCHAR(190)    NULL,
    proposed_description  TEXT            NULL,
    proposed_icon_class   VARCHAR(255)    NULL,
    proposed_price        DECIMAL(10,2)   NULL,
    status                VARCHAR(20)     NOT NULL DEFAULT 'pending',
    approved_category_id  BIGINT UNSIGNED NULL,
    reviewed_by_user_id   BIGINT UNSIGNED NULL,
    review_notes          TEXT            NULL,
    created_at            TIMESTAMP       NULL,
    updated_at            TIMESTAMP       NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_cat_req_uuid_unique (uuid),
    KEY sm_cat_req_status_idx (status),
    KEY sm_cat_req_tenant_idx (tenant_id),
    KEY sm_cat_req_vendor_idx (vendor_id),
    KEY sm_cat_req_source_idx (request_source),
    CONSTRAINT sm_cat_req_tenant_fk   FOREIGN KEY (tenant_id)            REFERENCES sm_tenants(id)    ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_parent_fk   FOREIGN KEY (parent_category_id)   REFERENCES sm_categories(id) ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_approved_fk FOREIGN KEY (approved_category_id) REFERENCES sm_categories(id) ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_vendor_fk   FOREIGN KEY (vendor_id)            REFERENCES sm_vendors(id)    ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_listing_fk  FOREIGN KEY (listing_id)           REFERENCES sm_listings(id)   ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_user_fk     FOREIGN KEY (requested_by_user_id) REFERENCES sm_users(id)      ON DELETE SET NULL,
    CONSTRAINT sm_cat_req_reviewer_fk FOREIGN KEY (reviewed_by_user_id)  REFERENCES sm_users(id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- 2026-04-14: pricing snapshot persistence on listings
-- Save-time snapshot: full quote line items + resolved rule + version,
-- so a listing's billed amount is reproducible later regardless of
-- subsequent tenant pricing rule changes.
-- ---------------------------------------------------------------
ALTER TABLE sm_listings
    ADD COLUMN pricing_currency           VARCHAR(8)    NULL,
    ADD COLUMN pricing_subtotal           DECIMAL(10,2) NULL,
    ADD COLUMN pricing_snapshot_json      JSON          NULL,
    ADD COLUMN pricing_rule_snapshot_json JSON          NULL,
    ADD COLUMN pricing_version            VARCHAR(50)   NULL,
    ADD COLUMN pricing_calculated_at      TIMESTAMP     NULL;

-- ---------------------------------------------------------------
-- 2026-04-14: pricing lock + integrity hash
-- pricing_locked_at: when set, save flow MUST NOT recalculate pricing
--   or alter selected categories. Snapshot is authoritative.
-- pricing_hash: sha256 of pricing_snapshot_json — tamper detection +
--   cross-system reconciliation. Regenerated only when snapshot writes.
-- ---------------------------------------------------------------
ALTER TABLE sm_listings
    ADD COLUMN pricing_locked_at TIMESTAMP   NULL,
    ADD COLUMN pricing_hash      VARCHAR(64) NULL;

-- ---------------------------------------------------------------
-- 2026-04-14: vendor marketplace dashboard schema
-- Listing-marketplace participation (Category = Marketplace; a listing
-- can join many marketplaces, each with its own service radius + plan).
-- RFQ matching + notifications + bids tables. Performance stats per
-- listing-marketplace per day. Suggestions table for the expansion panel.
-- ---------------------------------------------------------------

ALTER TABLE sm_rfq_requests
    ADD COLUMN title           VARCHAR(190)  NULL AFTER company,
    ADD COLUMN location_label  VARCHAR(190)  NULL,
    ADD COLUMN city            VARCHAR(120)  NULL,
    ADD COLUMN state           VARCHAR(120)  NULL,
    ADD COLUMN postal_code     VARCHAR(20)   NULL,
    ADD COLUMN country_code    VARCHAR(2)    NULL DEFAULT 'US',
    ADD COLUMN latitude        DECIMAL(10,7) NULL,
    ADD COLUMN longitude       DECIMAL(10,7) NULL,
    ADD COLUMN radius_miles    DECIMAL(8,2)  NULL DEFAULT 25.00,
    ADD COLUMN bid_deadline_at TIMESTAMP     NULL,
    MODIFY COLUMN status ENUM('new','contacted','closed','spam','open','matched','expired')
        NOT NULL DEFAULT 'new';

CREATE TABLE IF NOT EXISTS sm_listing_marketplaces (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    listing_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    is_primary_marketplace TINYINT(1) NOT NULL DEFAULT 0,
    listing_plan_type VARCHAR(20) NOT NULL DEFAULT 'featured',
    rfq_enabled TINYINT(1) NOT NULL DEFAULT 0,
    service_radius_miles DECIMAL(8,2) NOT NULL DEFAULT 25.00,
    service_center_location_id BIGINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    started_at TIMESTAMP NULL,
    ended_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_lm_uuid (uuid),
    UNIQUE KEY sm_lm_listing_category (listing_id, category_id),
    KEY sm_lm_category_active (category_id, is_active, rfq_enabled),
    KEY sm_lm_vendor (vendor_id),
    KEY sm_lm_tenant (tenant_id),
    CONSTRAINT sm_lm_tenant_fk   FOREIGN KEY (tenant_id)   REFERENCES sm_tenants(id)          ON DELETE CASCADE,
    CONSTRAINT sm_lm_vendor_fk   FOREIGN KEY (vendor_id)   REFERENCES sm_vendors(id)          ON DELETE CASCADE,
    CONSTRAINT sm_lm_listing_fk  FOREIGN KEY (listing_id)  REFERENCES sm_listings(id)         ON DELETE CASCADE,
    CONSTRAINT sm_lm_category_fk FOREIGN KEY (category_id) REFERENCES sm_categories(id)       ON DELETE CASCADE,
    CONSTRAINT sm_lm_loc_fk      FOREIGN KEY (service_center_location_id)
                                 REFERENCES sm_vendor_locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_listing_marketplace_stats (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    listing_marketplace_id BIGINT UNSIGNED NOT NULL,
    period_date DATE NOT NULL,
    impressions_count INT UNSIGNED NOT NULL DEFAULT 0,
    listing_views_count INT UNSIGNED NOT NULL DEFAULT 0,
    rfqs_matched_count INT UNSIGNED NOT NULL DEFAULT 0,
    bids_submitted_count INT UNSIGNED NOT NULL DEFAULT 0,
    bids_won_count INT UNSIGNED NOT NULL DEFAULT 0,
    bids_lost_count INT UNSIGNED NOT NULL DEFAULT 0,
    avg_response_minutes INT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_lms_period (listing_marketplace_id, period_date),
    CONSTRAINT sm_lms_lm_fk FOREIGN KEY (listing_marketplace_id) REFERENCES sm_listing_marketplaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_rfq_matches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rfq_request_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NULL,
    listing_id BIGINT UNSIGNED NULL,
    listing_marketplace_id BIGINT UNSIGNED NULL,
    match_type VARCHAR(30) NOT NULL DEFAULT 'claimed_vendor',
    distance_miles DECIMAL(8,2) NULL,
    notification_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    matched_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_rm_rfq_listing (rfq_request_id, listing_id),
    KEY sm_rm_rfq (rfq_request_id),
    KEY sm_rm_vendor (vendor_id),
    KEY sm_rm_listing (listing_id),
    CONSTRAINT sm_rm_rfq_fk     FOREIGN KEY (rfq_request_id)         REFERENCES sm_rfq_requests(id)        ON DELETE CASCADE,
    CONSTRAINT sm_rm_vendor_fk  FOREIGN KEY (vendor_id)              REFERENCES sm_vendors(id)             ON DELETE SET NULL,
    CONSTRAINT sm_rm_listing_fk FOREIGN KEY (listing_id)             REFERENCES sm_listings(id)            ON DELETE SET NULL,
    CONSTRAINT sm_rm_lm_fk      FOREIGN KEY (listing_marketplace_id) REFERENCES sm_listing_marketplaces(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_rfq_notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rfq_match_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NULL,
    listing_id BIGINT UNSIGNED NULL,
    notification_channel VARCHAR(20) NOT NULL DEFAULT 'email',
    notification_type VARCHAR(40) NOT NULL DEFAULT 'rfq_available',
    recipient VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NULL,
    body_snapshot TEXT NULL,
    provider_message_id VARCHAR(255) NULL,
    delivery_status VARCHAR(30) NOT NULL DEFAULT 'queued',
    sent_at TIMESTAMP NULL,
    opened_at TIMESTAMP NULL,
    clicked_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    KEY sm_rn_match (rfq_match_id),
    KEY sm_rn_vendor (vendor_id),
    KEY sm_rn_status (delivery_status),
    CONSTRAINT sm_rn_match_fk FOREIGN KEY (rfq_match_id) REFERENCES sm_rfq_matches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_rfq_bids (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    rfq_request_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    listing_id BIGINT UNSIGNED NOT NULL,
    listing_marketplace_id BIGINT UNSIGNED NULL,
    amount DECIMAL(10,2) NOT NULL,
    message TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'submitted',
    submitted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_rb_uuid (uuid),
    UNIQUE KEY sm_rb_rfq_listing (rfq_request_id, listing_id),
    KEY sm_rb_rfq (rfq_request_id),
    KEY sm_rb_vendor (vendor_id),
    KEY sm_rb_status (status),
    CONSTRAINT sm_rb_rfq_fk     FOREIGN KEY (rfq_request_id)         REFERENCES sm_rfq_requests(id)        ON DELETE CASCADE,
    CONSTRAINT sm_rb_vendor_fk  FOREIGN KEY (vendor_id)              REFERENCES sm_vendors(id)             ON DELETE CASCADE,
    CONSTRAINT sm_rb_listing_fk FOREIGN KEY (listing_id)             REFERENCES sm_listings(id)            ON DELETE CASCADE,
    CONSTRAINT sm_rb_lm_fk      FOREIGN KEY (listing_marketplace_id) REFERENCES sm_listing_marketplaces(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_vendor_marketplace_suggestions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vendor_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    suggestion_reason VARCHAR(255) NULL,
    estimated_rfq_count INT UNSIGNED NULL,
    estimated_competition_count INT UNSIGNED NULL,
    priority_score DECIMAL(5,2) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_vms_unique (vendor_id, category_id),
    CONSTRAINT sm_vms_vendor_fk   FOREIGN KEY (vendor_id)   REFERENCES sm_vendors(id)    ON DELETE CASCADE,
    CONSTRAINT sm_vms_category_fk FOREIGN KEY (category_id) REFERENCES sm_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── A/B Experimentation Infrastructure ─────────────────────────────────────
-- Reusable experiment engine. Variant assignment is deterministic per
-- assignment_key (see ExperimentContext::assignmentKey()) so a vendor
-- opening an initial email and a reminder email for the same RFQ sees
-- the same subject-line variant. Events are the unit of funnel
-- attribution (email_opened, claim_completed, upgrade_completed, etc).

CREATE TABLE IF NOT EXISTS sm_experiments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    scope_type ENUM('global','tenant','vendor','listing','rfq') NOT NULL DEFAULT 'global',
    target_surface VARCHAR(40) NOT NULL,
    status ENUM('draft','active','paused','completed') NOT NULL DEFAULT 'draft',
    traffic_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    targeting_rules_json JSON NULL,
    start_at TIMESTAMP NULL,
    end_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_exp_uuid (uuid),
    UNIQUE KEY sm_exp_code (code),
    KEY sm_exp_status_surface (status, target_surface)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_experiment_variants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    experiment_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(190) NOT NULL,
    weight INT UNSIGNED NOT NULL DEFAULT 50,
    payload_json JSON NOT NULL,
    is_control TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_ev_exp_code (experiment_id, code),
    KEY sm_ev_exp (experiment_id),
    CONSTRAINT sm_ev_exp_fk FOREIGN KEY (experiment_id) REFERENCES sm_experiments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_experiment_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    experiment_id BIGINT UNSIGNED NOT NULL,
    variant_id BIGINT UNSIGNED NOT NULL,
    tenant_id BIGINT UNSIGNED NULL,
    vendor_id BIGINT UNSIGNED NULL,
    listing_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    session_id VARCHAR(64) NULL,
    rfq_request_id BIGINT UNSIGNED NULL,
    assignment_key VARCHAR(190) NOT NULL,
    assigned_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    UNIQUE KEY sm_ea_exp_key (experiment_id, assignment_key),
    KEY sm_ea_variant (variant_id),
    KEY sm_ea_vendor (vendor_id),
    KEY sm_ea_user (user_id),
    KEY sm_ea_session (session_id),
    KEY sm_ea_rfq (rfq_request_id),
    CONSTRAINT sm_ea_exp_fk     FOREIGN KEY (experiment_id)  REFERENCES sm_experiments(id)         ON DELETE CASCADE,
    CONSTRAINT sm_ea_variant_fk FOREIGN KEY (variant_id)     REFERENCES sm_experiment_variants(id) ON DELETE CASCADE,
    CONSTRAINT sm_ea_tenant_fk  FOREIGN KEY (tenant_id)      REFERENCES sm_tenants(id)             ON DELETE SET NULL,
    CONSTRAINT sm_ea_vendor_fk  FOREIGN KEY (vendor_id)      REFERENCES sm_vendors(id)             ON DELETE SET NULL,
    CONSTRAINT sm_ea_listing_fk FOREIGN KEY (listing_id)     REFERENCES sm_listings(id)            ON DELETE SET NULL,
    CONSTRAINT sm_ea_user_fk    FOREIGN KEY (user_id)        REFERENCES sm_users(id)               ON DELETE SET NULL,
    CONSTRAINT sm_ea_rfq_fk     FOREIGN KEY (rfq_request_id) REFERENCES sm_rfq_requests(id)        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sm_experiment_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    experiment_id BIGINT UNSIGNED NOT NULL,
    variant_id BIGINT UNSIGNED NOT NULL,
    assignment_id BIGINT UNSIGNED NULL,
    tenant_id BIGINT UNSIGNED NULL,
    vendor_id BIGINT UNSIGNED NULL,
    listing_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    session_id VARCHAR(64) NULL,
    rfq_request_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(60) NOT NULL,
    event_value DECIMAL(12,2) NULL,
    meta_json JSON NULL,
    occurred_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NULL,
    PRIMARY KEY (id),
    KEY sm_eev_exp_var_evt (experiment_id, variant_id, event_name),
    KEY sm_eev_rfq (rfq_request_id),
    KEY sm_eev_vendor (vendor_id),
    KEY sm_eev_user (user_id),
    KEY sm_eev_assignment (assignment_id),
    CONSTRAINT sm_eev_exp_fk        FOREIGN KEY (experiment_id)  REFERENCES sm_experiments(id)            ON DELETE CASCADE,
    CONSTRAINT sm_eev_variant_fk    FOREIGN KEY (variant_id)     REFERENCES sm_experiment_variants(id)    ON DELETE CASCADE,
    CONSTRAINT sm_eev_assignment_fk FOREIGN KEY (assignment_id)  REFERENCES sm_experiment_assignments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- 2026-04-14: capture previously-orphaned sm_listings columns.
-- These were added by ad-hoc DDL and never recorded in the phase1
-- files; folding them in now so a fresh install works end-to-end.
-- is_featured / rfq_enabled / contact_visible / seo_follow_enabled:
--   denormalised paid-plan flags flipped by UpgradeController.
-- video_url: paid-plan video embed (Listing::allowsVideo).
-- ---------------------------------------------------------------
ALTER TABLE sm_listings
    ADD COLUMN is_featured        TINYINT(1)   NOT NULL DEFAULT 0 AFTER verification_status,
    ADD COLUMN rfq_enabled        TINYINT(1)   NOT NULL DEFAULT 0 AFTER is_featured,
    ADD COLUMN contact_visible    TINYINT(1)   NOT NULL DEFAULT 0 AFTER rfq_enabled,
    ADD COLUMN seo_follow_enabled TINYINT(1)   NOT NULL DEFAULT 0 AFTER contact_visible,
    ADD COLUMN video_url          VARCHAR(500) NULL              AFTER body_content;

-- ---------------------------------------------------------------
-- 2026-04-23: identity model — user_id as the only relational key
-- for user-owned data. Email becomes a write-only snapshot. These
-- FKs were missing (the requester_user_id columns existed but were
-- unforeignkeyed); adding them closes the loophole that let queries
-- fall back to email lookups.
-- Matching Laravel migration:
--   2026_04_23_000001_add_requester_user_id_fk_to_rfq_tables.php
-- ---------------------------------------------------------------
ALTER TABLE sm_rfq_requests
    ADD CONSTRAINT fk_sm_rfq_requests_requester_user
        FOREIGN KEY (requester_user_id) REFERENCES sm_users(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE sm_rfqs
    ADD CONSTRAINT fk_sm_rfqs_requester_user
        FOREIGN KEY (requester_user_id) REFERENCES sm_users(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------
-- 2026-04-23: contact messages — add requester_user_id (was missing
-- entirely). Buyer-owned contact threads now attribute by user_id
-- instead of email, so email changes don't break access.
-- Matching Laravel migration:
--   2026_04_23_000002_add_requester_user_id_to_sm_contact_messages.php
-- Companion Artisan command for historical data:
--   php artisan contact:backfill-requester-user-id [--dry-run]
-- ---------------------------------------------------------------
ALTER TABLE sm_contact_messages
    ADD COLUMN requester_user_id BIGINT UNSIGNED NULL AFTER assigned_to_user_id,
    ADD INDEX idx_sm_contact_messages_requester (requester_user_id),
    ADD CONSTRAINT fk_sm_contact_messages_requester_user
        FOREIGN KEY (requester_user_id) REFERENCES sm_users(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------
-- 2026-04-23: RFQ processing lifecycle marker.
-- Disambiguates "queued — matching in progress" from "processed,
-- no matches found" (which look identical in the count columns).
--
--   status=open + processing_finished_at IS NULL    → queued / in-flight
--   status=open + processing_finished_at IS NOT NULL → processed, no matches
--   status=matched                                   → processed, matches found
--
-- Set at the end of a successful ProcessRfqMatchesJob pass; read by
-- RfqExecutionReportService::deriveFinalStatus() to produce the
-- "queued" pill on tenant + platform admin RFQ views.
-- Matching Laravel migration:
--   2026_04_23_000003_add_processing_finished_at_to_sm_rfq_requests.php
-- ---------------------------------------------------------------
ALTER TABLE sm_rfq_requests
    ADD COLUMN processing_finished_at TIMESTAMP NULL AFTER expires_at,
    ADD INDEX idx_sm_rfq_requests_processing_finished (processing_finished_at);

-- ---------------------------------------------------------------
-- 2026-04-23: sm_contact_message_replies — sender_user_id FK
-- (was missing, left the column loose). Closes the consistency
-- gap so a purged user doesn't leave dangling sender references
-- in reply rows. Applied via runtime ALTER, not a Laravel migration.
--
-- Before applying, orphan rows (sender_user_id pointing at a dead
-- sm_users.id) were nulled by the comprehensive-audit cleanup pass.
-- ---------------------------------------------------------------
ALTER TABLE sm_contact_message_replies
    ADD CONSTRAINT fk_sm_cmr_sender_user
        FOREIGN KEY (sender_user_id) REFERENCES sm_users(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------
-- 2026-04-23: one-shot data patch — back-stamp processing_finished_at
-- on historical RFQs that completed before the column existed. Run
-- once after the column is in place. Treats any open/matched/closed
-- RFQ older than 4h without a stamp as "already processed" so the
-- admin UI stops rendering them as the new "queued" state forever.
-- ---------------------------------------------------------------
UPDATE sm_rfq_requests
SET processing_finished_at = COALESCE(closed_at, updated_at, created_at)
WHERE processing_finished_at IS NULL
  AND status IN ('open','matched','contacted','closed','expired')
  AND created_at < (NOW() - INTERVAL 4 HOUR);

-- ---------------------------------------------------------------
-- 2026-04-23: sm_rfq_attachments — video transcoding pipeline (Phase 1).
-- Adds a minimal processing state machine so buyer-uploaded videos
-- can be normalized to MP4/H.264/AAC via a background ffmpeg job
-- before they're served to vendors. Images stay 'ready' from the
-- moment they land; videos transit processing → ready | failed.
--
-- Column semantics:
--   processing_state       'ready' (images & pre-existing rows),
--                          'processing' (video just uploaded),
--                          'ready' (transcoded successfully),
--                          'failed' (transcode errored out).
--   processing_error       Last ~2 KB of ffmpeg stderr + exit code
--                          on failure. Admin-facing debug only.
--   original_storage_path  Path to the raw upload while a video is
--                          being processed. Nulled once storage_path
--                          has been rewritten to the transcoded .mp4.
--                          Always null for images.
--   poster_path            Disk path to a JPG thumbnail generated
--                          from the processed video (best-effort —
--                          null if poster generation fails; tile
--                          falls back to native <video preload=metadata>).
--
-- Default for existing rows is 'ready' — accurate, since none of
-- them went through the pipeline and they're already playable via
-- the browser's native MOV/MP4 support.
-- ---------------------------------------------------------------
ALTER TABLE sm_rfq_attachments
    ADD COLUMN processing_state      VARCHAR(16)  NOT NULL DEFAULT 'ready' AFTER size_bytes,
    ADD COLUMN processing_error      TEXT         NULL     AFTER processing_state,
    ADD COLUMN original_storage_path VARCHAR(500) NULL     AFTER storage_path,
    ADD COLUMN poster_path           VARCHAR(500) NULL     AFTER original_storage_path,
    ADD INDEX idx_sm_rfq_attachments_state (processing_state);
