<?php
/**
 * OJS Subscription Setup Script
 *
 * Upload to the OJS document root, execute in browser, then delete immediately.
 */

error_reporting(E_ALL);
ini_set('display_errors', '1');
header('Content-Type: text/html; charset=utf-8');

echo "<h2>OJS Subscription Setup</h2>";

// --- Step 0: Read config ---
echo "<h3>Step 0: Lettura config.inc.php</h3>";
$configPath = __DIR__ . '/config.inc.php';
if (!file_exists($configPath)) {
    die("<p style='color:red'>config.inc.php non trovato in " . htmlspecialchars(__DIR__) . "</p>");
}

$config = parse_ini_file($configPath, true);
if (!$config) {
    die("<p style='color:red'>parse_ini_file() fallita. Il file potrebbe avere un formato non standard.</p>");
}

$dbSection = $config['database'] ?? null;
if (!$dbSection) {
    echo "<p style='color:red'>Sezione [database] non trovata nel config. Sezioni trovate: " . htmlspecialchars(implode(', ', array_keys($config))) . "</p>";
    die();
}

$dbHost = $dbSection['host'] ?? 'localhost';
$dbUser = $dbSection['username'] ?? '';
$dbPass = $dbSection['password'] ?? '';
$dbName = $dbSection['name'] ?? '';

echo "<p>Host: " . htmlspecialchars($dbHost) . ", DB: " . htmlspecialchars($dbName) . ", User: " . htmlspecialchars($dbUser) . "</p>";

$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($db->connect_error) {
    die("<p style='color:red'>Connessione DB fallita: " . htmlspecialchars($db->connect_error) . "</p>");
}
$db->set_charset('utf8');
echo "<p style='color:green'>Connessione DB riuscita.</p>";

// --- Step 0b: Check table structure ---
echo "<h3>Step 0b: Verifica struttura tabelle</h3>";

$tables = ['journal_settings', 'subscription_types', 'subscription_type_settings', 'issues', 'custom_issue_orders'];
foreach ($tables as $t) {
    $r = $db->query("SHOW TABLES LIKE '{$t}'");
    $exists = $r && $r->num_rows > 0;
    echo "<p>Tabella <code>{$t}</code>: " . ($exists ? '<span style="color:green">esiste</span>' : '<span style="color:red">NON esiste</span>') . "</p>";
    if (!$exists && in_array($t, ['subscription_types', 'journal_settings', 'issues'])) {
        die("<p style='color:red'>Tabella critica mancante. Verifica che OJS sia installato correttamente.</p>");
    }
}

// Check subscription_types columns
$r = $db->query("DESCRIBE subscription_types");
if ($r) {
    $cols = [];
    while ($row = $r->fetch_assoc()) {
        $cols[] = $row['Field'];
    }
    echo "<p>Colonne <code>subscription_types</code>: " . htmlspecialchars(implode(', ', $cols)) . "</p>";
}

// --- Step 1: publishingMode ---
echo "<h3>Step 1: publishingMode = Subscription</h3>";

$r = $db->query("SELECT setting_value FROM journal_settings WHERE journal_id = 1 AND setting_name = 'publishingMode'");
if ($r && $r->num_rows > 0) {
    $current = $r->fetch_assoc()['setting_value'];
    echo "<p>Valore attuale: {$current}</p>";
    if ($current !== '1') {
        $db->query("UPDATE journal_settings SET setting_value = '1' WHERE journal_id = 1 AND setting_name = 'publishingMode'");
        echo "<p>Aggiornato a 1 (Subscription). Righe: " . $db->affected_rows . "</p>";
    } else {
        echo "<p style='color:green'>Gia' impostato correttamente.</p>";
    }
} else {
    $db->query("INSERT INTO journal_settings (journal_id, setting_name, setting_value, setting_type) VALUES (1, 'publishingMode', '1', 'int')");
    echo "<p>Inserito publishingMode = 1. Insert ID: " . $db->insert_id . "</p>";
}

if ($db->error) {
    echo "<p style='color:red'>Errore MySQL step 1: " . htmlspecialchars($db->error) . "</p>";
}

// --- Step 2: Subscription Type ---
echo "<h3>Step 2: Tipo di sottoscrizione individuale</h3>";

$result = $db->query("SELECT type_id FROM subscription_types WHERE journal_id = 1 AND institutional = 0 LIMIT 1");
if ($db->error) {
    echo "<p style='color:red'>Errore query subscription_types: " . htmlspecialchars($db->error) . "</p>";
    // Try without institutional filter
    echo "<p>Riprovo senza filtro institutional...</p>";
    $result = $db->query("SELECT type_id FROM subscription_types WHERE journal_id = 1 LIMIT 1");
    if ($db->error) {
        echo "<p style='color:red'>Errore: " . htmlspecialchars($db->error) . "</p>";
    }
}

$typeId = null;
if ($result && $result->num_rows > 0) {
    $row = $result->fetch_assoc();
    $typeId = $row['type_id'];
    echo "<p>Tipo sottoscrizione gia' esistente, type_id = <strong>{$typeId}</strong></p>";
} else {
    echo "<p>Nessun tipo trovato, ne creo uno nuovo...</p>";

    // Check actual columns available
    $colsResult = $db->query("DESCRIBE subscription_types");
    $availableCols = [];
    while ($c = $colsResult->fetch_assoc()) {
        $availableCols[] = $c['Field'];
    }

    // Build INSERT based on available columns
    $insertCols = ['journal_id'];
    $insertVals = ['1'];

    $optionalCols = [
        'cost'                  => '0.00',
        'currency_code_alpha'   => "'EUR'",
        'duration'              => '12',
        'format'                => '1',
        'institutional'         => '0',
        'membership'            => '0',
        'disable_public_display' => '0',
    ];

    foreach ($optionalCols as $col => $val) {
        if (in_array($col, $availableCols)) {
            $insertCols[] = $col;
            $insertVals[] = $val;
        }
    }

    $sql = "INSERT INTO subscription_types (" . implode(', ', $insertCols) . ") VALUES (" . implode(', ', $insertVals) . ")";
    echo "<p>SQL: <code>" . htmlspecialchars($sql) . "</code></p>";

    $db->query($sql);
    if ($db->error) {
        echo "<p style='color:red'>Errore INSERT subscription_types: " . htmlspecialchars($db->error) . "</p>";
    } else {
        $typeId = $db->insert_id;
        echo "<p style='color:green'>Creato type_id = <strong>{$typeId}</strong></p>";

        // Add names
        $stmtIt = $db->prepare("INSERT INTO subscription_type_settings (type_id, locale, setting_name, setting_value, setting_type) VALUES (?, 'it', 'name', 'Socio GuIT – Annuale', 'string')");
        if ($stmtIt) {
            $stmtIt->bind_param('i', $typeId);
            $stmtIt->execute();
            if ($stmtIt->error) echo "<p style='color:red'>Errore name IT: " . htmlspecialchars($stmtIt->error) . "</p>";
            $stmtIt->close();
        } else {
            echo "<p style='color:red'>Prepare fallita (name IT): " . htmlspecialchars($db->error) . "</p>";
        }

        $stmtEn = $db->prepare("INSERT INTO subscription_type_settings (type_id, locale, setting_name, setting_value, setting_type) VALUES (?, 'en', 'name', 'GuIT Member – Annual', 'string')");
        if ($stmtEn) {
            $stmtEn->bind_param('i', $typeId);
            $stmtEn->execute();
            if ($stmtEn->error) echo "<p style='color:red'>Errore name EN: " . htmlspecialchars($stmtEn->error) . "</p>";
            $stmtEn->close();
        } else {
            echo "<p style='color:red'>Prepare fallita (name EN): " . htmlspecialchars($db->error) . "</p>";
        }
    }
}

// --- Step 2b: formato tipo = Online (richiesto da OJS per i PDF) ---
echo "<h3>Step 2b: Formato tipo sottoscrizione (Online o Stampa+Online)</h3>";
if ($typeId) {
    $db->query(
        "UPDATE subscription_types SET format = 1 WHERE journal_id = 1 AND type_id = " . (int) $typeId .
        " AND institutional = 0 AND format NOT IN (1, 17)"
    );
    echo "<p>Aggiornamento format per type_id={$typeId} (solo se non era gia' 1 o 17). Affected rows: " . (int) $db->affected_rows . "</p>";
    if ($db->error) {
        echo "<p style='color:red'>Errore: " . htmlspecialchars($db->error) . "</p>";
    }
    echo "<p style='color:green'>OJS 3.4: i PDF richiedono format=1 (online) o 17 (stampa+online); il solo stampa (16) nasconde i galley.</p>";
} else {
    echo "<p style='color:orange'>Nessun type_id: salto.</p>";
}

// --- Step 3: Issue 36 (id=39) → subscription ---
echo "<h3>Step 3: Issue 36 (id=39) → subscription required</h3>";

$r = $db->query("SELECT issue_id, volume, number, year, published, access_status FROM issues WHERE journal_id = 1 ORDER BY issue_id DESC LIMIT 5");
if ($r) {
    echo "<p>Ultime 5 issue nel DB:</p><table border='1' cellpadding='4'><tr><th>issue_id</th><th>volume</th><th>number</th><th>year</th><th>published</th><th>access_status</th></tr>";
    while ($row = $r->fetch_assoc()) {
        echo "<tr>";
        foreach ($row as $v) echo "<td>" . htmlspecialchars($v ?? '') . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}

// OJS 3.4: ISSUE_ACCESS_SUBSCRIPTION = 2, ISSUE_ACCESS_OPEN = 1
$db->query("UPDATE issues SET access_status = 2, published = 1 WHERE issue_id = 39 AND journal_id = 1");
echo "<p>UPDATE issue_id=39 → access_status=2 (subscription): affected=" . $db->affected_rows . "</p>";
if ($db->error) echo "<p style='color:red'>Errore: " . htmlspecialchars($db->error) . "</p>";

// --- Step 4: All others → open access ---
echo "<h3>Step 4: Tutte le altre issue → open access</h3>";
$db->query("UPDATE issues SET access_status = 1 WHERE issue_id != 39 AND journal_id = 1");
echo "<p>UPDATE → access_status=1 (open): affected=" . $db->affected_rows . "</p>";
if ($db->error) echo "<p style='color:red'>Errore: " . htmlspecialchars($db->error) . "</p>";

// --- Step 5: custom_issue_orders ---
echo "<h3>Step 5: custom_issue_orders</h3>";
$r = $db->query("SELECT COUNT(*) as cnt FROM custom_issue_orders WHERE issue_id = 39 AND journal_id = 1");
if ($db->error) {
    echo "<p style='color:orange'>Tabella custom_issue_orders non disponibile (non critico): " . htmlspecialchars($db->error) . "</p>";
} elseif ($r) {
    $row = $r->fetch_assoc();
    if ($row['cnt'] == 0) {
        $db->query("INSERT INTO custom_issue_orders (issue_id, journal_id, seq) VALUES (39, 1, 0)");
        echo "<p>Aggiunta issue 39 a custom_issue_orders</p>";
    } else {
        echo "<p>Issue 39 gia' presente in custom_issue_orders</p>";
    }
}

// --- Summary ---
echo "<hr>";
echo "<h3>Riepilogo</h3>";
if ($typeId) {
    echo "<p style='font-size:18px;color:green;font-weight:bold'>Subscription Type ID: {$typeId}</p>";
    echo "<p>Usa questo valore nelle impostazioni del plugin OJS WP SSO Subscription.</p>";
} else {
    echo "<p style='color:red;font-weight:bold'>ATTENZIONE: non e' stato possibile creare o trovare un tipo di sottoscrizione. Controlla gli errori sopra.</p>";
}

// Clear OJS cache
$cleared = 0;
foreach (['cache/fc-*', 'cache/t_compile/*', 'cache/wc-*'] as $pattern) {
    $files = glob(__DIR__ . '/' . $pattern);
    if ($files) {
        array_map('unlink', $files);
        $cleared += count($files);
    }
}
echo "<p>Cache OJS svuotata: {$cleared} file rimossi.</p>";

echo "<p style='color:red;font-weight:bold'>ELIMINA QUESTO FILE IMMEDIATAMENTE!</p>";
$db->close();