
<?php
/**
 * SPONSOR & OFFER MANAGER
 * Connect to affiliate networks, manage offers, track conversions
 */
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.sponsors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    api_url TEXT,
    api_key TEXT,
    api_type VARCHAR(50) DEFAULT 'hasoffers',
    is_active BOOLEAN DEFAULT true,
    last_sync TIMESTAMP,
    offers_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.offers (
    id SERIAL PRIMARY KEY,
    sponsor_id INTEGER,
    external_id VARCHAR(100),
    name VARCHAR(255),
    description TEXT,
    vertical VARCHAR(100),
    payout FLOAT DEFAULT 0,
    payout_type VARCHAR(50) DEFAULT 'cpa',
    geo_targets TEXT,
    allowed_traffic TEXT,
    tracking_url TEXT,
    preview_url TEXT,
    creatives TEXT,
    status VARCHAR(50) DEFAULT 'active',
    epc FLOAT DEFAULT 0,
    conversion_rate FLOAT DEFAULT 0,
    total_clicks INTEGER DEFAULT 0,
    total_conversions INTEGER DEFAULT 0,
    last_conversion TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(sponsor_id, external_id)
);

CREATE TABLE IF NOT EXISTS admin.conversions (
    id SERIAL PRIMARY KEY,
    offer_id INTEGER,
    campaign_id INTEGER,
    click_id VARCHAR(255),
    payout FLOAT,
    ip_address VARCHAR(50),
    country VARCHAR(10),
    converted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin.creatives (
    id SERIAL PRIMARY KEY,
    offer_id INTEGER,
    type VARCHAR(50),
    name VARCHAR(255),
    url TEXT,
    size VARCHAR(50),
    preview TEXT,
    performance_score FLOAT DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    conversions INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true
);
");

class SponsorManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // ============================================
    // SPONSORS
    // ============================================
    
    public function addSponsor($data) {
        $this->pdo->prepare("INSERT INTO admin.sponsors (name, api_url, api_key, api_type) VALUES (?, ?, ?, ?)")
            ->execute([$data['name'], $data['api_url'] ?? '', $data['api_key'] ?? '', $data['api_type'] ?? 'hasoffers']);
        return ['success' => true, 'id' => $this->pdo->lastInsertId()];
    }
    
    public function syncOffers($sponsorId) {
        $sponsor = $this->pdo->query("SELECT * FROM admin.sponsors WHERE id = $sponsorId")->fetch(PDO::FETCH_ASSOC);
        if (!$sponsor) return ['error' => 'Sponsor not found'];
        
        // Call sponsor API based on type
        $offers = $this->fetchOffersFromAPI($sponsor);
        
        $synced = 0;
        foreach ($offers as $offer) {
            $this->pdo->prepare("INSERT INTO admin.offers (sponsor_id, external_id, name, description, vertical, payout, geo_targets, tracking_url, preview_url) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
                ON CONFLICT (sponsor_id, external_id) DO UPDATE SET name = ?, payout = ?, status = 'active'")
                ->execute([
                    $sponsorId, $offer['id'], $offer['name'], $offer['description'] ?? '',
                    $offer['vertical'] ?? 'general', $offer['payout'] ?? 0,
                    json_encode($offer['geo'] ?? []), $offer['tracking_url'] ?? '', $offer['preview_url'] ?? '',
                    $offer['name'], $offer['payout'] ?? 0
                ]);
            $synced++;
        }
        
        $this->pdo->exec("UPDATE admin.sponsors SET last_sync = NOW(), offers_count = $synced WHERE id = $sponsorId");
        
        return ['success' => true, 'synced' => $synced];
    }
    
    private function fetchOffersFromAPI($sponsor) {
        $type = $sponsor['api_type'];
        $url = $sponsor['api_url'];
        $key = $sponsor['api_key'];
        
        switch ($type) {
            case 'hasoffers':
                $endpoint = "$url/Apiv3/json?api_key=$key&Target=Affiliate_Offer&Method=findAll";
                break;
            case 'cake':
                $endpoint = "$url/api/1/get.asmx/Offers?api_key=$key";
                break;
            case 'everflow':
                $endpoint = "$url/v1/affiliates/offers";
                break;
            default:
                $endpoint = $url;
        }
        
        $ch = curl_init($endpoint);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 30,
            CURLOPT_HTTPHEADER => ['Authorization: Bearer ' . $key]
        ]);
        $response = curl_exec($ch);
        curl_close($ch);
        
        $data = json_decode($response, true);
        
        // Parse based on API type
        return $this->parseOfferResponse($type, $data);
    }
    
    private function parseOfferResponse($type, $data) {
        $offers = [];
        
        switch ($type) {
            case 'hasoffers':
                foreach ($data['response']['data'] ?? [] as $o) {
                    $offers[] = [
                        'id' => $o['Offer']['id'],
                        'name' => $o['Offer']['name'],
                        'payout' => $o['Offer']['default_payout'],
                        'tracking_url' => $o['Offer']['offer_url']
                    ];
                }
                break;
            default:
                // Generic parsing
                if (is_array($data)) {
                    foreach ($data as $o) {
                        $offers[] = [
                            'id' => $o['id'] ?? uniqid(),
                            'name' => $o['name'] ?? $o['title'] ?? 'Unknown',
                            'payout' => $o['payout'] ?? 0
                        ];
                    }
                }
        }
        
        return $offers;
    }
    
    // ============================================
    // OFFERS
    // ============================================
    
    public function addOffer($data) {
        $this->pdo->prepare("INSERT INTO admin.offers (sponsor_id, external_id, name, description, vertical, payout, payout_type, geo_targets, tracking_url, preview_url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
            ->execute([
                $data['sponsor_id'] ?? null, $data['external_id'] ?? uniqid(),
                $data['name'], $data['description'] ?? '', $data['vertical'] ?? 'general',
                $data['payout'] ?? 0, $data['payout_type'] ?? 'cpa',
                json_encode($data['geo_targets'] ?? []), $data['tracking_url'] ?? '', $data['preview_url'] ?? ''
            ]);
        return ['success' => true, 'id' => $this->pdo->lastInsertId()];
    }
    
    public function getOffersByVertical($vertical) {
        return $this->pdo->query("SELECT * FROM admin.offers WHERE vertical = '$vertical' AND status = 'active' ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getBestOffers($limit = 10) {
        return $this->pdo->query("SELECT o.*, s.name as sponsor_name FROM admin.offers o LEFT JOIN admin.sponsors s ON o.sponsor_id = s.id WHERE o.status = 'active' ORDER BY o.epc DESC, o.conversion_rate DESC LIMIT $limit")->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function matchOfferToData($offerId, $segmentCriteria) {
        $offer = $this->pdo->query("SELECT * FROM admin.offers WHERE id = $offerId")->fetch(PDO::FETCH_ASSOC);
        if (!$offer) return ['error' => 'Offer not found'];
        
        $geoTargets = json_decode($offer['geo_targets'] ?? '[]', true);
        
        // Find matching data segments
        $matchScore = 0;
        $recommendations = [];
        
        // Check geo match
        if (!empty($geoTargets)) {
            $recommendations[] = "Target countries: " . implode(', ', $geoTargets);
        }
        
        // Check vertical match
        $recommendations[] = "Best for vertical: " . $offer['vertical'];
        
        // EPC-based recommendation
        if ($offer['epc'] > 0.5) {
            $recommendations[] = "High EPC - prioritize quality traffic";
        }
        
        return [
            'offer' => $offer,
            'match_score' => $matchScore,
            'recommendations' => $recommendations
        ];
    }
    
    // ============================================
    // CONVERSIONS
    // ============================================
    
    public function recordConversion($data) {
        $this->pdo->prepare("INSERT INTO admin.conversions (offer_id, campaign_id, click_id, payout, ip_address, country) VALUES (?, ?, ?, ?, ?, ?)")
            ->execute([$data['offer_id'], $data['campaign_id'] ?? null, $data['click_id'] ?? '', $data['payout'] ?? 0, $data['ip'] ?? '', $data['country'] ?? '']);
        
        // Update offer stats
        $this->pdo->exec("UPDATE admin.offers SET total_conversions = total_conversions + 1, last_conversion = NOW() WHERE id = {$data['offer_id']}");
        
        return ['success' => true];
    }
    
    public function getConversionStats($offerId = null, $days = 30) {
        $where = $offerId ? "WHERE offer_id = $offerId" : "";
        $dateFilter = "AND converted_at > NOW() - INTERVAL '$days days'";
        if ($where) {
            $where .= " $dateFilter";
        } else {
            $where = "WHERE converted_at > NOW() - INTERVAL '$days days'";
        }
        
        return [
            'total_conversions' => $this->pdo->query("SELECT COUNT(*) FROM admin.conversions $where")->fetchColumn(),
            'total_revenue' => $this->pdo->query("SELECT COALESCE(SUM(payout), 0) FROM admin.conversions $where")->fetchColumn(),
            'by_country' => $this->pdo->query("SELECT country, COUNT(*) as count, SUM(payout) as revenue FROM admin.conversions $where GROUP BY country ORDER BY count DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC),
            'by_day' => $this->pdo->query("SELECT DATE(converted_at) as date, COUNT(*) as count, SUM(payout) as revenue FROM admin.conversions $where GROUP BY DATE(converted_at) ORDER BY date DESC LIMIT $days")->fetchAll(PDO::FETCH_ASSOC)
        ];
    }
    
    // ============================================
    // CREATIVES
    // ============================================
    
    public function addCreative($data) {
        $this->pdo->prepare("INSERT INTO admin.creatives (offer_id, type, name, url, size, preview) VALUES (?, ?, ?, ?, ?, ?)")
            ->execute([$data['offer_id'], $data['type'] ?? 'banner', $data['name'], $data['url'], $data['size'] ?? '', $data['preview'] ?? '']);
        return ['success' => true, 'id' => $this->pdo->lastInsertId()];
    }
    
    public function getBestCreatives($offerId) {
        return $this->pdo->query("SELECT * FROM admin.creatives WHERE offer_id = $offerId AND is_active = true ORDER BY performance_score DESC, conversions DESC")->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getStats() {
        return [
            'sponsors' => $this->pdo->query("SELECT COUNT(*) FROM admin.sponsors WHERE is_active = true")->fetchColumn(),
            'offers' => $this->pdo->query("SELECT COUNT(*) FROM admin.offers WHERE status = 'active'")->fetchColumn(),
            'conversions_today' => $this->pdo->query("SELECT COUNT(*) FROM admin.conversions WHERE DATE(converted_at) = CURRENT_DATE")->fetchColumn(),
            'revenue_today' => $this->pdo->query("SELECT COALESCE(SUM(payout), 0) FROM admin.conversions WHERE DATE(converted_at) = CURRENT_DATE")->fetchColumn(),
            'top_verticals' => $this->pdo->query("SELECT vertical, COUNT(*) as count FROM admin.offers GROUP BY vertical ORDER BY count DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC)
        ];
    }
}

$manager = new SponsorManager($pdo);
$action = $_POST['action'] ?? $_GET['action'] ?? '';

switch ($action) {
    case 'add_sponsor':
        echo json_encode($manager->addSponsor($_POST));
        break;
    case 'sync':
        echo json_encode($manager->syncOffers($_POST['sponsor_id']));
        break;
    case 'sponsors':
        echo json_encode(['sponsors' => $pdo->query("SELECT * FROM admin.sponsors ORDER BY id")->fetchAll(PDO::FETCH_ASSOC)]);
        break;
    case 'add_offer':
        echo json_encode($manager->addOffer($_POST));
        break;
    case 'offers':
        echo json_encode(['offers' => $pdo->query("SELECT o.*, s.name as sponsor_name FROM admin.offers o LEFT JOIN admin.sponsors s ON o.sponsor_id = s.id ORDER BY o.epc DESC")->fetchAll(PDO::FETCH_ASSOC)]);
        break;
    case 'best_offers':
        echo json_encode(['offers' => $manager->getBestOffers($_GET['limit'] ?? 10)]);
        break;
    case 'by_vertical':
        echo json_encode(['offers' => $manager->getOffersByVertical($_GET['vertical'])]);
        break;
    case 'match':
        echo json_encode($manager->matchOfferToData($_GET['offer_id'], $_GET['criteria'] ?? ''));
        break;
    case 'conversion':
        echo json_encode($manager->recordConversion($_POST));
        break;
    case 'conversion_stats':
        echo json_encode($manager->getConversionStats($_GET['offer_id'] ?? null, $_GET['days'] ?? 30));
        break;
    case 'add_creative':
        echo json_encode($manager->addCreative($_POST));
        break;
    case 'creatives':
        echo json_encode(['creatives' => $manager->getBestCreatives($_GET['offer_id'])]);
        break;
    case 'stats':
        echo json_encode($manager->getStats());
        break;
    default:
        echo json_encode(['name' => 'Sponsor Manager', 'actions' => ['add_sponsor','sync','sponsors','add_offer','offers','best_offers','by_vertical','match','conversion','conversion_stats','add_creative','creatives','stats']]);
}