
<?php
/**
 * DATA INTELLIGENCE ENGINE
 * Autonomous Data Collection, Segmentation & Matching
 */
header('Content-Type: application/json');
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$pdo->exec("
CREATE TABLE IF NOT EXISTS admin.data_sources (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    type VARCHAR(100),
    connection_config TEXT,
    last_sync TIMESTAMP,
    records_count INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE IF NOT EXISTS admin.data_profiles (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    country VARCHAR(50),
    language VARCHAR(10),
    interests TEXT,
    engagement_score FLOAT DEFAULT 0,
    last_open TIMESTAMP,
    last_click TIMESTAMP,
    total_sends INTEGER DEFAULT 0,
    total_opens INTEGER DEFAULT 0,
    total_clicks INTEGER DEFAULT 0,
    is_valid BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.data_segments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    criteria TEXT,
    size INTEGER DEFAULT 0,
    best_verticals TEXT,
    avg_engagement FLOAT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.offer_registry (
    id SERIAL PRIMARY KEY,
    offer_id VARCHAR(100) UNIQUE,
    name VARCHAR(255),
    vertical VARCHAR(100),
    geo_targets TEXT,
    payout FLOAT,
    epc FLOAT DEFAULT 0,
    conversion_rate FLOAT DEFAULT 0,
    best_segments TEXT,
    creative_urls TEXT,
    tracking_url TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.offer_data_matches (
    id SERIAL PRIMARY KEY,
    offer_id INTEGER,
    segment_id INTEGER,
    match_score FLOAT,
    predicted_conversion FLOAT,
    last_tested TIMESTAMP,
    actual_conversion FLOAT
);
");

class DataIntelligence {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // ============================================
    // DATA SEGMENTATION
    // ============================================
    
    public function createSegment($name, $criteria) {
        $this->pdo->prepare("INSERT INTO admin.data_segments (name, criteria) VALUES (?, ?) ON CONFLICT DO NOTHING")
            ->execute([$name, json_encode($criteria)]);
        
        $segmentId = $this->pdo->lastInsertId();
        $this->updateSegmentSize($segmentId);
        
        return ['success' => true, 'segment_id' => $segmentId];
    }
    
    public function autoSegment() {
        // Create standard segments automatically
        $segments = [
            ['Hot Openers', ['last_open_days' => 7, 'engagement_score_min' => 0.7]],
            ['Warm Openers', ['last_open_days' => 30, 'engagement_score_min' => 0.4]],
            ['Cold List', ['last_open_days' => 90, 'engagement_score_max' => 0.3]],
            ['Clickers', ['total_clicks_min' => 1]],
            ['High Value', ['total_clicks_min' => 3, 'engagement_score_min' => 0.8]],
            ['US Audience', ['country' => 'US']],
            ['EU Audience', ['country' => ['FR', 'DE', 'UK', 'IT', 'ES']]],
            ['FR Audience', ['country' => 'FR', 'language' => 'fr']],
            ['Never Opened', ['total_opens' => 0, 'total_sends_min' => 3]]
        ];
        
        foreach ($segments as $seg) {
            $this->createSegment($seg[0], $seg[1]);
        }
        
        return ['success' => true, 'segments_created' => count($segments)];
    }
    
    private function updateSegmentSize($segmentId) {
        // In real implementation, would count matching profiles
        $this->pdo->exec("UPDATE admin.data_segments SET size = (SELECT COUNT(*) FROM admin.data_profiles WHERE is_valid = true), last_updated = NOW() WHERE id = $segmentId");
    }
    
    public function getSegmentData($segmentId, $limit = 1000) {
        $segment = $this->pdo->query("SELECT * FROM admin.data_segments WHERE id = $segmentId")->fetch(PDO::FETCH_ASSOC);
        if (!$segment) return ['error' => 'Segment not found'];
        
        // Build query from criteria (simplified)
        $profiles = $this->pdo->query("SELECT email, first_name, country, engagement_score FROM admin.data_profiles WHERE is_valid = true ORDER BY engagement_score DESC LIMIT $limit")->fetchAll(PDO::FETCH_ASSOC);
        
        return ['segment' => $segment, 'profiles' => $profiles];
    }
    
    // ============================================
    // OFFER MATCHING
    // ============================================
    
    public function registerOffer($data) {
        $this->pdo->prepare("INSERT INTO admin.offer_registry (offer_id, name, vertical, geo_targets, payout, tracking_url, creative_urls) VALUES (?, ?, ?, ?, ?, ?, ?) ON CONFLICT (offer_id) DO UPDATE SET name = ?, vertical = ?, payout = ?")
            ->execute([
                $data['offer_id'], $data['name'], $data['vertical'], 
                json_encode($data['geo_targets'] ?? []), $data['payout'] ?? 0,
                $data['tracking_url'] ?? '', json_encode($data['creatives'] ?? []),
                $data['name'], $data['vertical'], $data['payout'] ?? 0
            ]);
        
        return ['success' => true];
    }
    
    public function matchOfferToData($offerId) {
        $offer = $this->pdo->query("SELECT * FROM admin.offer_registry WHERE id = $offerId OR offer_id = '$offerId'")->fetch(PDO::FETCH_ASSOC);
        if (!$offer) return ['error' => 'Offer not found'];
        
        $geoTargets = json_decode($offer['geo_targets'] ?? '[]', true);
        $vertical = $offer['vertical'];
        
        // Find best matching segments
        $segments = $this->pdo->query("SELECT * FROM admin.data_segments ORDER BY avg_engagement DESC")->fetchAll(PDO::FETCH_ASSOC);
        
        $matches = [];
        foreach ($segments as $seg) {
            $score = $this->calculateMatchScore($offer, $seg);
            $matches[] = [
                'segment_id' => $seg['id'],
                'segment_name' => $seg['name'],
                'match_score' => $score,
                'predicted_conversion' => $score * ($offer['conversion_rate'] ?: 0.01),
                'size' => $seg['size']
            ];
        }
        
        // Sort by score
        usort($matches, fn($a, $b) => $b['match_score'] <=> $a['match_score']);
        
        return ['offer' => $offer, 'matches' => array_slice($matches, 0, 5)];
    }
    
    private function calculateMatchScore($offer, $segment) {
        $score = 0.5; // Base score
        
        // Engagement boost
        $score += ($segment['avg_engagement'] ?? 0) * 0.3;
        
        // Size factor (prefer larger segments)
        if ($segment['size'] > 10000) $score += 0.1;
        if ($segment['size'] > 50000) $score += 0.1;
        
        return min(1.0, $score);
    }
    
    public function findBestOfferForSegment($segmentId) {
        $segment = $this->pdo->query("SELECT * FROM admin.data_segments WHERE id = $segmentId")->fetch(PDO::FETCH_ASSOC);
        if (!$segment) return ['error' => 'Segment not found'];
        
        $offers = $this->pdo->query("SELECT * FROM admin.offer_registry WHERE is_active = true ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC);
        
        $recommendations = [];
        foreach ($offers as $offer) {
            $score = $this->calculateMatchScore($offer, $segment);
            $recommendations[] = [
                'offer_id' => $offer['offer_id'],
                'offer_name' => $offer['name'],
                'vertical' => $offer['vertical'],
                'payout' => $offer['payout'],
                'match_score' => $score,
                'expected_revenue' => $score * $offer['payout'] * $segment['size'] * 0.001
            ];
        }
        
        usort($recommendations, fn($a, $b) => $b['expected_revenue'] <=> $a['expected_revenue']);
        
        return ['segment' => $segment, 'recommendations' => array_slice($recommendations, 0, 5)];
    }
    
    // ============================================
    // DATA QUALITY
    // ============================================
    
    public function analyzeDataQuality() {
        return [
            'total_profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles")->fetchColumn(),
            'valid_profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles WHERE is_valid = true")->fetchColumn(),
            'with_engagement' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles WHERE total_opens > 0")->fetchColumn(),
            'by_country' => $this->pdo->query("SELECT country, COUNT(*) as count FROM admin.data_profiles GROUP BY country ORDER BY count DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC),
            'avg_engagement' => $this->pdo->query("SELECT AVG(engagement_score) FROM admin.data_profiles")->fetchColumn()
        ];
    }
    
    public function updateEngagementScores() {
        // Calculate engagement score based on opens/clicks
        $this->pdo->exec("UPDATE admin.data_profiles SET engagement_score = 
            CASE 
                WHEN total_sends = 0 THEN 0.5
                ELSE LEAST(1.0, (total_opens::float / total_sends * 0.5) + (total_clicks::float / GREATEST(total_opens, 1) * 0.5))
            END
        ");
        
        return ['success' => true];
    }
    
    public function getStats() {
        return [
            'profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles")->fetchColumn(),
            'segments' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_segments")->fetchColumn(),
            'offers' => $this->pdo->query("SELECT COUNT(*) FROM admin.offer_registry WHERE is_active = true")->fetchColumn(),
            'sources' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_sources WHERE is_active = true")->fetchColumn()
        ];
    }
}

$dataEngine = new DataIntelligence($pdo);
$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch ($action) {
    case 'create_segment':
        echo json_encode($dataEngine->createSegment($_POST['name'], json_decode($_POST['criteria'], true)));
        break;
    case 'auto_segment':
        echo json_encode($dataEngine->autoSegment());
        break;
    case 'get_segment':
        echo json_encode($dataEngine->getSegmentData($_GET['segment_id'], $_GET['limit'] ?? 1000));
        break;
    case 'segments':
        echo json_encode(['segments' => $pdo->query("SELECT * FROM admin.data_segments ORDER BY size DESC")->fetchAll(PDO::FETCH_ASSOC)]);
        break;
    case 'register_offer':
        echo json_encode($dataEngine->registerOffer($_POST));
        break;
    case 'match_offer':
        echo json_encode($dataEngine->matchOfferToData($_GET['offer_id']));
        break;
    case 'recommend_offer':
        echo json_encode($dataEngine->findBestOfferForSegment($_GET['segment_id']));
        break;
    case 'offers':
        echo json_encode(['offers' => $pdo->query("SELECT * FROM admin.offer_registry ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC)]);
        break;
    case 'quality':
        echo json_encode($dataEngine->analyzeDataQuality());
        break;
    case 'update_engagement':
        echo json_encode($dataEngine->updateEngagementScores());
        break;
    case 'stats':
        echo json_encode($dataEngine->getStats());
        break;
    default:
        echo json_encode(['name' => 'Data Intelligence Engine', 'actions' => ['create_segment','auto_segment','get_segment','segments','register_offer','match_offer','recommend_offer','offers','quality','update_engagement','stats']]);
}