-- ============================================================================
-- Migration: Add Match Metadata Tracking to PMS Reservation Tables
-- ============================================================================
-- Purpose: Track HOW and WHEN fuzzy matching was applied to link reservations
--          to propiedades. Enables audit trails and match quality analysis.
--
-- Author: Claude Code (Thoth's Metadata Edition)
-- Date: 2026-01-04
-- ============================================================================

-- ============================================================================
-- CLOUDBEDS RESERVA: Add match metadata columns
-- ============================================================================

ALTER TABLE cloudbeds_reserva
ADD COLUMN match_tier TINYINT NULL
    COMMENT 'Matching tier used: 0=combo, 1=perfect(95-100%), 2=high(80-94%), 3=medium(65-79%), 4=low(40-64%)'
    AFTER propiedad_id,
ADD COLUMN match_confidence TINYINT NULL
    COMMENT 'Match confidence percentage (0-100)'
    AFTER match_tier,
ADD COLUMN match_pattern VARCHAR(150) NULL
    COMMENT 'Pattern/method used for matching (e.g., "combo_doble_5y6", "hybrid_building+unit", "exact_match")'
    AFTER match_confidence,
ADD COLUMN match_timestamp TIMESTAMP NULL
    COMMENT 'When the match was applied by link_pms_propiedades.php'
    AFTER match_pattern;

-- ============================================================================
-- HOSTIFY RESERVA: Add match metadata columns
-- ============================================================================

ALTER TABLE hostify_reserva
ADD COLUMN match_tier TINYINT NULL
    COMMENT 'Matching tier used: 0=combo, 1=exact(100%), 2=contains(90%), 3=similarity(70%), 4=street(50-65%)'
    AFTER propiedad_id,
ADD COLUMN match_confidence TINYINT NULL
    COMMENT 'Match confidence percentage (0-100)'
    AFTER match_tier,
ADD COLUMN match_pattern VARCHAR(150) NULL
    COMMENT 'Pattern/method used for matching (e.g., "combo_doble_5y6", "contains_segment", "exact_match")'
    AFTER match_confidence,
ADD COLUMN match_timestamp TIMESTAMP NULL
    COMMENT 'When the match was applied by link_pms_propiedades.php'
    AFTER match_pattern;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Verify columns were added to cloudbeds_reserva
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'quantix'
  AND TABLE_NAME = 'cloudbeds_reserva'
  AND COLUMN_NAME IN ('match_tier', 'match_confidence', 'match_pattern', 'match_timestamp')
ORDER BY ORDINAL_POSITION;

-- Verify columns were added to hostify_reserva
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'quantix'
  AND TABLE_NAME = 'hostify_reserva'
  AND COLUMN_NAME IN ('match_tier', 'match_confidence', 'match_pattern', 'match_timestamp')
ORDER BY ORDINAL_POSITION;

-- ============================================================================
-- ANALYTICS QUERIES (for future use)
-- ============================================================================

-- Which matching methods are most common?
-- SELECT match_tier, match_pattern, COUNT(*) as count
-- FROM hostify_reserva
-- WHERE propiedad_id IS NOT NULL
-- GROUP BY match_tier, match_pattern
-- ORDER BY count DESC;

-- Which matches have low confidence (need review)?
-- SELECT anuncio, match_tier, match_confidence, match_pattern
-- FROM hostify_reserva
-- WHERE match_confidence < 70 AND propiedad_id IS NOT NULL
-- ORDER BY match_confidence;

-- Combo matches only
-- SELECT anuncio, match_pattern, match_confidence
-- FROM hostify_reserva
-- WHERE match_tier = 0
-- ORDER BY anuncio;

-- ============================================================================
-- END OF MIGRATION
-- ============================================================================
