/**
 * DATABASE MIGRATION: Add Propietario Matching Columns to Propiedad Table
 *
 * Purpose: Support THOTH's AI-powered reverse matching (propietarios → propiedades)
 * Author: Claude Code (THOTH's Algorithm)
 * Date: 2026-01-04
 * Related: /lamp/www/quantix/backoffice/helper/link_propiedades_propietarios.php
 *
 * What This Adds:
 * - propietario_nombre_propiedad: Full departamento text from matched propietario
 * - propietario_match_tier: Match quality tier (0-4)
 * - propietario_match_confidence: Match confidence percentage (0-100)
 * - propietario_match_pattern: Algorithm pattern used (e.g., 'tier1_exact')
 * - propietario_match_explanation: AI-generated human-readable explanation
 * - propietario_match_scores: JSON with multi-dimensional scores
 * - propietario_match_timestamp: When match was created/updated
 *
 * Why Separate from propietario_id:
 * - propietario_id = FK reference (structural relationship)
 * - propietario_nombre_propiedad = Semantic match reference (AI-discovered)
 * - Both can coexist for validation and conflict detection
 */

USE quantix;

-- ============================================================================
-- Add AI Matching Metadata Columns
-- ============================================================================

ALTER TABLE propiedad
ADD COLUMN propietario_nombre_propiedad TEXT NULL
    COMMENT 'Matched propietario.departamento via THOTH algorithm (full text with pipes)'
    AFTER propietario_id;

ALTER TABLE propiedad
ADD COLUMN propietario_match_tier INT NULL
    COMMENT 'Match tier: 0=combo, 1=perfect(95-100%), 2=high(80-94%), 3=medium(65-79%), 4=low(40-64%)'
    AFTER propietario_nombre_propiedad;

ALTER TABLE propiedad
ADD COLUMN propietario_match_confidence INT NULL
    COMMENT 'Match confidence percentage (0-100)'
    AFTER propietario_match_tier;

ALTER TABLE propiedad
ADD COLUMN propietario_match_pattern VARCHAR(100) NULL
    COMMENT 'Match pattern used: tier1_exact, tier2_contains, tier3_similarity, tier4_street, combo_*'
    AFTER propietario_match_confidence;

ALTER TABLE propiedad
ADD COLUMN propietario_match_explanation TEXT NULL
    COMMENT 'AI-generated explanation: tier name, score breakdown, method, warnings'
    AFTER propietario_match_pattern;

ALTER TABLE propiedad
ADD COLUMN propietario_match_scores JSON NULL
    COMMENT 'Multi-dimensional scores: {street:95, building:100, unit:90, overall:95, tier:1}'
    AFTER propietario_match_explanation;

ALTER TABLE propiedad
ADD COLUMN propietario_match_timestamp TIMESTAMP NULL
    COMMENT 'When this match was created/updated by THOTH algorithm'
    AFTER propietario_match_scores;

-- ============================================================================
-- Add Indexes for Query Performance
-- ============================================================================

-- Index for filtering by match tier
ALTER TABLE propiedad
ADD INDEX idx_propietario_match_tier (propietario_match_tier);

-- Index for filtering by match confidence
ALTER TABLE propiedad
ADD INDEX idx_propietario_match_confidence (propietario_match_confidence);

-- Composite index for high-quality matches (common query pattern)
ALTER TABLE propiedad
ADD INDEX idx_propietario_match_quality (propietario_match_tier, propietario_match_confidence);

-- ============================================================================
-- Verification Queries (Run After Migration)
-- ============================================================================

-- Verify columns added
-- SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_SCHEMA = 'quantix'
--   AND TABLE_NAME = 'propiedad'
--   AND COLUMN_NAME LIKE 'propietario_match%'
-- ORDER BY ORDINAL_POSITION;

-- Verify indexes created
-- SHOW INDEX FROM propiedad WHERE Key_name LIKE 'idx_propietario_match%';

-- ============================================================================
-- ROLLBACK SCRIPT (In Case of Issues)
-- ============================================================================

/*
-- Execute these if you need to undo this migration:

ALTER TABLE propiedad
DROP INDEX idx_propietario_match_quality;

ALTER TABLE propiedad
DROP INDEX idx_propietario_match_confidence;

ALTER TABLE propiedad
DROP INDEX idx_propietario_match_tier;

ALTER TABLE propiedad
DROP COLUMN propietario_match_timestamp;

ALTER TABLE propiedad
DROP COLUMN propietario_match_scores;

ALTER TABLE propiedad
DROP COLUMN propietario_match_explanation;

ALTER TABLE propiedad
DROP COLUMN propietario_match_pattern;

ALTER TABLE propiedad
DROP COLUMN propietario_match_confidence;

ALTER TABLE propiedad
DROP COLUMN propietario_match_tier;

ALTER TABLE propiedad
DROP COLUMN propietario_nombre_propiedad;
*/

-- ============================================================================
-- USAGE EXAMPLES (After Matcher Updates)
-- ============================================================================

/*
-- Find all high-confidence matches
SELECT nombre_propiedad, propietario_nombre_propiedad,
       propietario_match_tier, propietario_match_confidence
FROM propiedad
WHERE propietario_match_confidence >= 80
ORDER BY propietario_match_confidence DESC;

-- Find combo matches (multi-property owners)
SELECT nombre_propiedad, propietario_nombre_propiedad,
       propietario_match_explanation
FROM propiedad
WHERE propietario_match_tier = 0
ORDER BY propietario_nombre_propiedad;

-- Analyze match quality distribution
SELECT
    propietario_match_tier as tier,
    COUNT(*) as count,
    AVG(propietario_match_confidence) as avg_confidence,
    MIN(propietario_match_confidence) as min_confidence,
    MAX(propietario_match_confidence) as max_confidence
FROM propiedad
WHERE propietario_match_tier IS NOT NULL
GROUP BY propietario_match_tier
ORDER BY propietario_match_tier;

-- Find unmatched propiedades (after running matcher)
SELECT nombre_propiedad, direccion
FROM propiedad
WHERE propietario_match_tier IS NULL
  OR propietario_match_confidence = 0;

-- Compare AI match vs manual FK reference (conflict detection)
SELECT
    p.nombre_propiedad,
    p.propietario_nombre_propiedad as ai_match,
    prop.departamento as fk_match,
    p.propietario_match_confidence
FROM propiedad p
LEFT JOIN propietario prop ON p.propietario_id = prop.propietario_id
WHERE p.propietario_id IS NOT NULL
  AND p.propietario_nombre_propiedad IS NOT NULL
  AND p.propietario_nombre_propiedad != prop.departamento
ORDER BY p.propietario_match_confidence DESC;

-- Parse JSON scores for analysis
SELECT
    nombre_propiedad,
    propietario_nombre_propiedad,
    JSON_EXTRACT(propietario_match_scores, '$.street') as street_score,
    JSON_EXTRACT(propietario_match_scores, '$.building') as building_score,
    JSON_EXTRACT(propietario_match_scores, '$.unit') as unit_score,
    JSON_EXTRACT(propietario_match_scores, '$.overall') as overall_score
FROM propiedad
WHERE propietario_match_scores IS NOT NULL
ORDER BY JSON_EXTRACT(propietario_match_scores, '$.overall') DESC
LIMIT 20;
*/

-- ============================================================================
-- Migration Complete
-- ============================================================================

SELECT 'Migration 05_add_propietario_match_columns.sql completed successfully!' as status;
SELECT 'Run verification queries (uncomment above) to confirm schema changes.' as next_step;
