<?php
/**
 * TrackPro v5 — Intelligence Engine Migration
 * Behavioral Profiling + Enhanced Identity Correlation
 * DELETE AFTER RUNNING
 */
error_reporting(E_ALL); ini_set('display_errors', 1);
$config = require __DIR__ . '/includes/config.php';
$pdo = new PDO("mysql:host={$config['db_host']};dbname={$config['db_name']};charset=utf8mb4", $config['db_user'], $config['db_pass'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
echo "<pre style='font-family:monospace;background:#0a0e17;color:#10b981;padding:2rem;min-height:100vh'>\nTrackPro v5 — Intelligence Engine Migration\n" . str_repeat('=',50) . "\n\n";
$ok=0;$skip=0;$fail=0;

function run($pdo,$sql,$label,&$ok,&$skip,&$fail){
    try{$pdo->exec($sql);echo "+ {$label}\n";$ok++;}
    catch(PDOException $e){
        if(strpos($e->getMessage(),'already exists')!==false||strpos($e->getMessage(),'Duplicate')!==false){echo "~ {$label} (exists)\n";$skip++;}
        else{echo "! {$label}: ".$e->getMessage()."\n";$fail++;}
    }
}

// ═══ Visitor Profiles Table ═════════════════════════════
echo "--- Visitor Intelligence Tables ---\n";
run($pdo,"CREATE TABLE IF NOT EXISTS `visitor_profiles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `visitor_id` BIGINT UNSIGNED NOT NULL,
    
    -- Wealth & Device Tier
    `device_tier` ENUM('budget','mid','premium','ultra') DEFAULT 'mid',
    `device_tier_score` TINYINT UNSIGNED DEFAULT 50,
    `device_tier_signals` TEXT NULL,
    
    -- Tech Proficiency
    `tech_level` ENUM('novice','basic','intermediate','advanced','expert') DEFAULT 'basic',
    `tech_level_score` TINYINT UNSIGNED DEFAULT 30,
    `tech_level_signals` TEXT NULL,
    
    -- Intent Classification
    `intent` ENUM('casual','researching','comparing','ready_to_act','returning','automated') DEFAULT 'casual',
    `intent_score` TINYINT UNSIGNED DEFAULT 0,
    `intent_signals` TEXT NULL,
    
    -- Engagement Profile
    `engagement_level` ENUM('bounce','glance','reader','engaged','power_user') DEFAULT 'glance',
    `engagement_score` TINYINT UNSIGNED DEFAULT 0,
    
    -- Behavioral Patterns
    `visit_pattern` ENUM('one_time','occasional','regular','frequent','obsessive') DEFAULT 'one_time',
    `active_hours` VARCHAR(48) NULL COMMENT 'JSON array of hour bins',
    `avg_session_duration` INT UNSIGNED DEFAULT 0,
    `total_interactions` INT UNSIGNED DEFAULT 0,
    
    -- Privacy Profile
    `privacy_consciousness` ENUM('none','low','moderate','high','paranoid') DEFAULT 'none',
    `privacy_score` TINYINT UNSIGNED DEFAULT 0,
    `privacy_signals` TEXT NULL,
    
    -- Platform Ecosystem
    `primary_ecosystem` ENUM('google','apple','microsoft','meta','other','mixed') DEFAULT 'other',
    `ecosystem_signals` TEXT NULL,
    
    -- Interest Indicators
    `interest_tags` TEXT NULL COMMENT 'JSON array of inferred interests',
    
    -- Demographics (statistical inference)
    `likely_age_range` VARCHAR(16) NULL,
    `likely_profession` VARCHAR(64) NULL,
    
    -- Metadata
    `confidence_score` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Overall profile confidence 0-100',
    `data_points_used` INT UNSIGNED DEFAULT 0,
    `last_profiled` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE INDEX `idx_visitor` (`visitor_id`),
    INDEX `idx_device_tier` (`device_tier`),
    INDEX `idx_tech_level` (`tech_level`),
    INDEX `idx_intent` (`intent`),
    FOREIGN KEY (`visitor_id`) REFERENCES `visitor_identities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","visitor_profiles table",$ok,$skip,$fail);

// ═══ Enhanced Identity Correlation ══════════════════════
run($pdo,"CREATE TABLE IF NOT EXISTS `identity_graph` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `cluster_id` VARCHAR(64) NOT NULL COMMENT 'Shared cluster identifier',
    `visitor_id` BIGINT UNSIGNED NOT NULL,
    `match_type` ENUM('fingerprint','ip_pattern','behavioral','cross_device','temporal') NOT NULL,
    `match_confidence` TINYINT UNSIGNED DEFAULT 50,
    `matched_with_visitor_id` BIGINT UNSIGNED NULL,
    `match_evidence` TEXT NULL COMMENT 'JSON evidence',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_cluster` (`cluster_id`),
    INDEX `idx_visitor` (`visitor_id`),
    INDEX `idx_matched` (`matched_with_visitor_id`),
    FOREIGN KEY (`visitor_id`) REFERENCES `visitor_identities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","identity_graph table",$ok,$skip,$fail);

// ═══ Enhanced visitor_identities columns ════════════════
echo "\n--- Enhanced Visitor Identity Columns ---\n";
$cols = [
    ['visitor_identities','fingerprint_stable','VARCHAR(16) NULL COMMENT "Stable cross-session fingerprint"'],
    ['visitor_identities','ip_history','TEXT NULL COMMENT "JSON array of seen IPs"'],
    ['visitor_identities','device_signature','VARCHAR(64) NULL COMMENT "Hardware-based device signature"'],
    ['visitor_identities','ua_hash','VARCHAR(16) NULL'],
    ['visitor_identities','screen_signature','VARCHAR(32) NULL COMMENT "screen+viewport+ratio combo"'],
    ['visitor_identities','font_signature','VARCHAR(16) NULL'],
    ['visitor_identities','gpu_signature','VARCHAR(64) NULL'],
    ['visitor_identities','timezone_signature','VARCHAR(32) NULL'],
    ['visitor_identities','language_signature','VARCHAR(64) NULL'],
    ['visitor_identities','cluster_id','VARCHAR(64) NULL'],
    ['visitor_identities','total_links_visited','INT UNSIGNED DEFAULT 1'],
    ['visitor_identities','risk_level','ENUM("low","medium","high","critical") DEFAULT "low"'],
    ['visitor_identities','profile_id','BIGINT UNSIGNED NULL'],
];
foreach($cols as [$t,$c,$def]){
    try{$ch=$pdo->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    if($ch->rowCount()>0){$skip++;continue;}
    $pdo->exec("ALTER TABLE `{$t}` ADD COLUMN `{$c}` {$def}");echo "+ {$t}.{$c}\n";$ok++;
    }catch(PDOException $e){echo "! {$t}.{$c}: ".$e->getMessage()."\n";$fail++;}
}

echo "\n".str_repeat('=',50)."\nDONE: {$ok} applied | {$skip} skipped | {$fail} failed\n\nDELETE THIS FILE\n</pre>";
