<?php
/**
 * ETHICA PILOT PREP — Prepare 500 HCPs DZ campaign
 * NO SENDING — just data + template
 */
header('Content-Type: application/json; charset=utf-8');

$pdo = new PDO("pgsql:host=10.1.0.3;port=5432;dbname=adx_system", "admin", "admin123");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$action = $_GET['action'] ?? 'stats';

if ($action === 'stats') {
    // HCP counts by country
    $st = $pdo->query("SELECT pays, count(*) as total, count(CASE WHEN email IS NOT NULL AND email != '' THEN 1 END) as with_email FROM ethica.medecins_validated GROUP BY pays ORDER BY total DESC");
    $countries = $st->fetchAll(PDO::FETCH_ASSOC);
    
    // Top specialties DZ with email
    $st2 = $pdo->query("SELECT specialite, count(*) as n FROM ethica.medecins_validated WHERE email IS NOT NULL AND email != '' AND pays='DZ' GROUP BY specialite ORDER BY n DESC LIMIT 15");
    $specs = $st2->fetchAll(PDO::FETCH_ASSOC);
    
    // Total ready
    $st3 = $pdo->query("SELECT count(*) FROM ethica.medecins_validated WHERE email IS NOT NULL AND email != ''");
    $total_email = $st3->fetchColumn();
    
    echo json_encode(['ok'=>true, 'countries'=>$countries, 'top_specialties_dz'=>$specs, 'total_with_email'=>(int)$total_email], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
}

if ($action === 'sample') {
    // Get 10 sample HCPs for preview
    $st = $pdo->query("SELECT nom, prenom, specialite, ville, email FROM ethica.medecins_validated WHERE email IS NOT NULL AND email != '' AND pays='DZ' ORDER BY random() LIMIT 10");
    echo json_encode(['ok'=>true, 'sample'=>$st->fetchAll(PDO::FETCH_ASSOC)], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
}

if ($action === 'select500') {
    // Select 500 DZ HCPs with email, balanced by specialty
    $st = $pdo->query("
        WITH ranked AS (
            SELECT id, nom, prenom, specialite, ville, email,
                   ROW_NUMBER() OVER (PARTITION BY specialite ORDER BY random()) as rn,
                   COUNT(*) OVER (PARTITION BY specialite) as spec_count
            FROM ethica.medecins_validated 
            WHERE email IS NOT NULL AND email != '' AND pays='DZ'
        )
        SELECT id, nom, prenom, specialite, ville, email
        FROM ranked 
        WHERE rn <= GREATEST(5, CEIL(500.0 * spec_count / (SELECT count(*) FROM ethica.medecins_validated WHERE email IS NOT NULL AND email != '' AND pays='DZ')))
        ORDER BY specialite, random()
        LIMIT 500
    ");
    $hcps = $st->fetchAll(PDO::FETCH_ASSOC);
    
    // Stats of selection
    $specs = [];
    foreach ($hcps as $h) {
        $s = $h['specialite'] ?? 'unknown';
        $specs[$s] = ($specs[$s] ?? 0) + 1;
    }
    arsort($specs);
    
    echo json_encode(['ok'=>true, 'count'=>count($hcps), 'by_specialty'=>$specs, 'preview'=>array_slice($hcps,0,5)], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
}