-- ============================================================================
-- Migration: Create propiedad_pms_analytics table + add nombre_propiedad_original
-- Purpose: Learning-based PMS matching analytics system
-- Author: Claude Code (Thoth's Algorithm)
-- Date: 2026-01-05
-- 
-- Usage:
--   /lamp/mysql/bin/mysql -u root -pM@chiavell1 --socket=/lamp/mysql/mysql.sock quantix < 06_create_propiedad_pms_analytics.sql
-- ============================================================================

-- ============================================================================
-- PART 1: Add nombre_propiedad_original column to propiedad
-- ============================================================================

-- Check if column exists first (idempotent)
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'quantix' 
    AND TABLE_NAME = 'propiedad' 
    AND COLUMN_NAME = 'nombre_propiedad_original'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE propiedad ADD COLUMN nombre_propiedad_original VARCHAR(255) NULL COMMENT ''Backup of original name before AI-suggested modification'' AFTER nombre_propiedad',
    'SELECT ''Column nombre_propiedad_original already exists'' AS status'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- PART 2: Create propiedad_pms_analytics table
-- ============================================================================

CREATE TABLE IF NOT EXISTS propiedad_pms_analytics (
    -- Primary key
    analytics_id VARCHAR(36) NOT NULL,
    
    -- Foreign key to propiedad
    propiedad_id VARCHAR(36) NOT NULL,
    
    -- Iteration tracking (1, 2, 3... increments each analysis run)
    iteration INT NOT NULL,
    
    -- ========================================================================
    -- Discovery data from PMS (this iteration)
    -- ========================================================================
    
    -- Discovered unit identifiers from reservations
    -- Example: ["101", "102", "201", "PH1"]
    discovered_units JSON,
    
    -- Cloudbeds match details
    -- Example: [{"property": "Mr W Tonala", "room_number": "502", "confidence": 95, "tier": 1}, ...]
    cloudbeds_matches JSON,
    
    -- Hostify match details
    -- Example: [{"anuncio": "Tonala - A", "confidence": 90, "tier": 2, "combo_type": null}, ...]
    hostify_matches JSON,
    
    -- ========================================================================
    -- Match quality metrics
    -- ========================================================================
    
    -- Confidence scores (0-100)
    match_confidence_avg DECIMAL(5,2) DEFAULT NULL,
    match_confidence_max DECIMAL(5,2) DEFAULT NULL,
    match_confidence_min DECIMAL(5,2) DEFAULT NULL,
    
    -- Best tier achieved (0=combo, 1=exact, 2=contains, 3=similarity, 4=partial)
    match_tier_best INT DEFAULT NULL,
    
    -- Match counts
    match_count_cloudbeds INT DEFAULT 0,
    match_count_hostify INT DEFAULT 0,
    total_reservations INT DEFAULT 0,
    
    -- ========================================================================
    -- Suggested improvements (this iteration)
    -- ========================================================================
    
    -- AI-suggested improved name for better matching
    suggested_name VARCHAR(255) DEFAULT NULL,
    
    -- Confidence in the suggestion (0-100)
    suggested_name_confidence INT DEFAULT NULL,
    
    -- Why we suggest this name change
    suggested_name_reason TEXT,
    
    -- Was the suggestion applied?
    suggestion_applied TINYINT(1) DEFAULT 0,
    suggestion_applied_at DATETIME DEFAULT NULL,
    
    -- ========================================================================
    -- Expansion suggestions (for multi-unit properties)
    -- ========================================================================
    
    -- Suggested expansion plan
    -- Example: {"num_units": 12, "units": ["101", "102", ...], "source": "pms", "discrepancy": 2}
    suggested_expansion JSON,
    
    -- Was the expansion applied?
    expansion_applied TINYINT(1) DEFAULT 0,
    expansion_applied_at DATETIME DEFAULT NULL,
    
    -- ========================================================================
    -- Delta from previous iteration (learning data)
    -- ========================================================================
    
    -- Change in average confidence from previous iteration
    delta_confidence DECIMAL(5,2) DEFAULT NULL,
    
    -- Change in total matches from previous iteration
    delta_matches INT DEFAULT NULL,
    
    -- Human-readable summary of changes
    delta_notes TEXT,
    
    -- ========================================================================
    -- Metadata
    -- ========================================================================
    
    -- When this analysis was performed
    analyzed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Who ran the analysis (usuario_id)
    analyzed_by VARCHAR(36) DEFAULT NULL,
    
    -- ========================================================================
    -- Constraints
    -- ========================================================================
    
    PRIMARY KEY (analytics_id),
    
    -- Each propiedad can have multiple iterations, but only one per iteration number
    UNIQUE KEY uk_propiedad_iteration (propiedad_id, iteration),
    
    -- Indexes for common queries
    INDEX idx_propiedad (propiedad_id),
    INDEX idx_iteration (iteration),
    INDEX idx_analyzed_at (analyzed_at),
    INDEX idx_confidence (match_confidence_avg),
    INDEX idx_suggestion_pending (suggestion_applied, suggested_name_confidence),
    
    -- Foreign key to propiedad (with CASCADE delete)
    CONSTRAINT fk_analytics_propiedad 
        FOREIGN KEY (propiedad_id) 
        REFERENCES propiedad(propiedad_id) 
        ON DELETE CASCADE
        ON UPDATE CASCADE
        
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Learning-based PMS matching analytics - stores one row per propiedad per iteration';

-- ============================================================================
-- PART 3: Create helper view for latest analytics per property
-- ============================================================================

CREATE OR REPLACE VIEW v_propiedad_latest_analytics AS
SELECT 
    a.*,
    p.nombre_propiedad,
    p.nombre_propiedad_original,
    p.num_deptos,
    p.direccion,
    p.propietario_id
FROM propiedad_pms_analytics a
INNER JOIN (
    SELECT propiedad_id, MAX(iteration) as max_iteration
    FROM propiedad_pms_analytics
    GROUP BY propiedad_id
) latest ON a.propiedad_id = latest.propiedad_id AND a.iteration = latest.max_iteration
INNER JOIN propiedad p ON a.propiedad_id = p.propiedad_id;

-- ============================================================================
-- PART 4: Create helper view for iteration summary
-- ============================================================================

CREATE OR REPLACE VIEW v_analytics_iteration_summary AS
SELECT 
    iteration,
    COUNT(*) as properties_analyzed,
    AVG(match_confidence_avg) as avg_confidence,
    MAX(match_confidence_max) as max_confidence,
    SUM(match_count_cloudbeds) as total_cloudbeds_matches,
    SUM(match_count_hostify) as total_hostify_matches,
    SUM(total_reservations) as total_reservations_matched,
    SUM(CASE WHEN suggested_name IS NOT NULL THEN 1 ELSE 0 END) as suggestions_generated,
    SUM(suggestion_applied) as suggestions_applied,
    SUM(expansion_applied) as expansions_applied,
    AVG(delta_confidence) as avg_delta_confidence,
    MIN(analyzed_at) as started_at,
    MAX(analyzed_at) as completed_at
FROM propiedad_pms_analytics
GROUP BY iteration
ORDER BY iteration DESC;

-- ============================================================================
-- Verification
-- ============================================================================

SELECT 'Migration 06_create_propiedad_pms_analytics.sql completed successfully!' AS status;

SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'quantix' 
AND TABLE_NAME IN ('propiedad', 'propiedad_pms_analytics');

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'quantix' 
AND TABLE_NAME = 'propiedad'
AND COLUMN_NAME = 'nombre_propiedad_original';
