-- ============================================================================
-- Migration: Add AI Match Explanation & Score Tracking
-- ============================================================================
-- Purpose: Store detailed explanations and multi-dimensional scores for every
--          fuzzy match decision. Enables audit trails, ML training data, and
--          human review of low-confidence matches.
--
-- Author: Filemón Prime (Thoth's Algorithm v1.0)
-- Date: 2026-01-04
-- ============================================================================

-- ============================================================================
-- CLOUDBEDS RESERVA: Add AI explanation columns
-- ============================================================================

ALTER TABLE cloudbeds_reserva
ADD COLUMN match_explanation TEXT NULL
    COMMENT 'AI-generated explanation: tier name, score breakdown, method, warnings'
    AFTER match_pattern,
ADD COLUMN match_scores JSON NULL
    COMMENT 'Multi-dimensional scores: {street:95, building:100, unit:90, unit_type:100, descriptor:50, ...}'
    AFTER match_explanation;

-- ============================================================================
-- HOSTIFY RESERVA: Add AI explanation columns
-- ============================================================================

ALTER TABLE hostify_reserva
ADD COLUMN match_explanation TEXT NULL
    COMMENT 'AI-generated explanation: tier name, score breakdown, method, warnings'
    AFTER match_pattern,
ADD COLUMN match_scores JSON NULL
    COMMENT 'Multi-dimensional scores: {street:95, building:100, unit:90, unit_type:100, descriptor:50, ...}'
    AFTER match_explanation;

-- ============================================================================
-- 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_explanation', 'match_scores')
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_explanation', 'match_scores')
ORDER BY ORDINAL_POSITION;

-- ============================================================================
-- ANALYTICS QUERIES (for post-deployment analysis)
-- ============================================================================

-- Example: Match quality distribution by tier
-- SELECT
--     match_tier,
--     COUNT(*) as count,
--     AVG(match_confidence) as avg_confidence,
--     COUNT(CASE WHEN match_confidence >= 80 THEN 1 END) as high_confidence_count
-- FROM hostify_reserva
-- WHERE propiedad_id IS NOT NULL
-- GROUP BY match_tier
-- ORDER BY match_tier;

-- Example: Unmatched reasons breakdown
-- SELECT
--     SUBSTRING_INDEX(match_explanation, '\n', 1) as primary_reason,
--     COUNT(*) as count
-- FROM hostify_reserva
-- WHERE propiedad_id IS NULL AND match_explanation IS NOT NULL
-- GROUP BY primary_reason
-- ORDER BY count DESC;

-- Example: Low confidence matches needing review
-- SELECT
--     anuncio,
--     match_confidence,
--     match_pattern,
--     match_explanation,
--     JSON_EXTRACT(match_scores, '$.street') as street_score,
--     JSON_EXTRACT(match_scores, '$.unit') as unit_score
-- FROM hostify_reserva
-- WHERE match_confidence BETWEEN 40 AND 70
-- ORDER BY match_confidence DESC
-- LIMIT 50;

-- Example: Score breakdown analysis (which dimensions matter most?)
-- SELECT
--     match_tier,
--     AVG(JSON_EXTRACT(match_scores, '$.street')) as avg_street_score,
--     AVG(JSON_EXTRACT(match_scores, '$.building_number')) as avg_building_score,
--     AVG(JSON_EXTRACT(match_scores, '$.unit')) as avg_unit_score
-- FROM hostify_reserva
-- WHERE propiedad_id IS NOT NULL AND match_scores IS NOT NULL
-- GROUP BY match_tier;

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