<?php
declare(strict_types=1);

$base = dirname(__DIR__);
require $base . '/app/init.php';

$pdo = App\Core\Database::instance()->pdo();
$has = (int) $pdo->query(
    "SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'portal_documents' AND COLUMN_NAME = 'file_name'"
)->fetchColumn();
if ($has < 1) {
    $pdo->exec('ALTER TABLE portal_documents ADD COLUMN file_name VARCHAR(180) NULL AFTER body');
    echo "Added portal_documents.file_name\n";
} else {
    echo "portal_documents.file_name already present\n";
}

$hasSigned = (int) $pdo->query(
    "SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'signatures' AND COLUMN_NAME = 'signed_file_name'"
)->fetchColumn();
if ($hasSigned < 1) {
    $pdo->exec('ALTER TABLE signatures ADD COLUMN signed_file_name VARCHAR(180) NULL AFTER signer_name');
    echo "Added signatures.signed_file_name\n";
} else {
    echo "signatures.signed_file_name already present\n";
}

foreach (['quotes' => 'body', 'contracts' => 'status', 'invoices' => 'status'] as $table => $after) {
    $hasFile = (int) $pdo->query(
        "SELECT COUNT(*) FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = " . $pdo->quote($table) . " AND COLUMN_NAME = 'file_name'"
    )->fetchColumn();
    if ($hasFile < 1) {
        $pdo->exec('ALTER TABLE `' . $table . '` ADD COLUMN file_name VARCHAR(180) NULL AFTER `' . $after . '`');
        echo "Added $table.file_name\n";
    } else {
        echo "$table.file_name already present\n";
    }
}

$hasRenewed = (int) $pdo->query(
    "SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'contracts' AND COLUMN_NAME = 'renewed_from_id'"
)->fetchColumn();
if ($hasRenewed < 1) {
    $pdo->exec('ALTER TABLE contracts ADD COLUMN renewed_from_id INT UNSIGNED NULL AFTER file_name');
    $pdo->exec('ALTER TABLE contracts ADD KEY idx_ctr_renewed_from (tenant_id, renewed_from_id)');
    echo "Added contracts.renewed_from_id\n";
} else {
    echo "contracts.renewed_from_id already present\n";
}

$docs = str_replace('\\', '/', dirname($base)) . '/storage/docs';
if (!is_dir($docs) && !mkdir($docs, 0750, true) && !is_dir($docs)) {
    echo "Could not create $docs\n";
} else {
    echo "Storage folder ready: $docs\n";
}

$hidden = 0;
$declined = 0;
$groups = $pdo->query(
    "SELECT tenant_id, related_type, related_id
     FROM portal_documents
     WHERE related_type IS NOT NULL AND related_id IS NOT NULL
     GROUP BY tenant_id, related_type, related_id
     HAVING COUNT(*) > 1"
)->fetchAll(PDO::FETCH_ASSOC);
foreach ($groups as $g) {
    $stmt = $pdo->prepare(
        "SELECT d.id, s.id AS sig_id, s.status AS sig_status
         FROM portal_documents d
         LEFT JOIN signatures s ON s.document_id = d.id
           AND s.id = (SELECT MAX(s2.id) FROM signatures s2 WHERE s2.document_id = d.id)
         WHERE d.tenant_id = ? AND d.related_type = ? AND d.related_id = ?
         ORDER BY (s.status = 'pending') DESC, d.id DESC"
    );
    $stmt->execute([(int) $g['tenant_id'], $g['related_type'], (int) $g['related_id']]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($rows === []) {
        continue;
    }
    $keepId = (int) $rows[0]['id'];
    foreach ($rows as $i => $row) {
        if ((int) $row['id'] === $keepId) {
            continue;
        }
        if (($row['sig_status'] ?? '') === 'pending' && !empty($row['sig_id'])) {
            $pdo->prepare("UPDATE signatures SET status = 'declined', note = 'Replaced by a newer paper.' WHERE id = ?")
                ->execute([(int) $row['sig_id']]);
            $declined++;
        }
        if (($row['sig_status'] ?? '') !== 'signed') {
            $pdo->prepare('UPDATE portal_documents SET shared = 0 WHERE id = ?')->execute([(int) $row['id']]);
            $hidden++;
        }
    }
}
echo "Collapsed extra papers: declined $declined pending, hid $hidden copies\n";

$docsRoot = str_replace('\\', '/', dirname($base)) . '/storage/docs';
$sharedRenewals = 0;
$renewals = $pdo->query(
    "SELECT id, tenant_id, account_id, owner_id, number, title, file_name
     FROM contracts
     WHERE (renewed_from_id IS NOT NULL OR title LIKE 'Renewal: %')
       AND file_name IS NOT NULL AND file_name <> ''"
)->fetchAll(PDO::FETCH_ASSOC);
$insDoc = $pdo->prepare(
    "INSERT INTO portal_documents (tenant_id, account_id, owner_id, related_type, related_id, title, kind, body, file_name, shared)
     VALUES (?, ?, ?, 'contract', ?, ?, 'contract', ?, ?, 1)"
);
$hasDoc = $pdo->prepare(
    "SELECT id FROM portal_documents WHERE tenant_id = ? AND related_type = 'contract' AND related_id = ? AND shared = 1 LIMIT 1"
);
foreach ($renewals as $c) {
    $hasDoc->execute([(int) $c['tenant_id'], (int) $c['id']]);
    if ($hasDoc->fetch()) {
        continue;
    }
    $body = 'Contract ' . (string) $c['number'];
    $insDoc->execute([
        (int) $c['tenant_id'],
        (int) $c['account_id'],
        (int) $c['owner_id'],
        (int) $c['id'],
        (string) $c['title'],
        $body,
        (string) $c['file_name'],
    ]);
    $docId = (int) $pdo->lastInsertId();
    $src = $docsRoot . '/' . (int) $c['tenant_id'] . '/contract-' . (int) $c['id'] . '.pdf';
    $destDir = $docsRoot . '/' . (int) $c['tenant_id'];
    $dest = $destDir . '/' . $docId . '.pdf';
    if (is_file($src)) {
        if (!is_dir($destDir)) {
            mkdir($destDir, 0750, true);
        }
        copy($src, $dest);
    }
    $sharedRenewals++;
}
echo "Shared $sharedRenewals renewal contracts to the Customer Portal\n";
