<?php
declare(strict_types=1);

/**
 * Adds visit subject and detail on accounts (shown on Service visits).
 * Fresh installs already include this in schema.sql.
 * Run: C:\xampp\php\php.exe sql/migrate_visit_subject.php
 */
$base = dirname(__DIR__);
require $base . '/app/init.php';

$db = App\Core\Database::instance();
$pdo = $db->pdo();

$has = (int) $pdo->query(
    "SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'accounts' AND COLUMN_NAME = 'visit_subject'"
)->fetchColumn();

if ($has < 1) {
    $pdo->exec("ALTER TABLE accounts
        ADD COLUMN visit_subject VARCHAR(180) NULL AFTER next_visit_at,
        ADD COLUMN visit_detail TEXT NULL AFTER visit_subject");
    echo "Added accounts.visit_subject and visit_detail\n";
} else {
    echo "accounts.visit_subject already present\n";
}

$pdo->exec(
    "UPDATE accounts a
     INNER JOIN (
       SELECT j.account_id, j.tenant_id, j.subject, j.body
       FROM service_jobs j
       INNER JOIN (
         SELECT account_id, MAX(id) AS id FROM service_jobs GROUP BY account_id
       ) x ON x.id = j.id
     ) j ON j.account_id = a.id AND j.tenant_id = a.tenant_id
     SET a.visit_subject = j.subject, a.visit_detail = j.body
     WHERE a.visit_subject IS NULL OR a.visit_subject = ''"
);

$pdo->exec(
    "UPDATE accounts a
     INNER JOIN (
       SELECT act.related_id, act.tenant_id, act.subject, act.body
       FROM activities act
       INNER JOIN (
         SELECT related_id, MAX(id) AS id
         FROM activities
         WHERE related_type = 'account' AND type = 'visit'
           AND subject NOT IN ('Visit notes', 'Visit attended', 'Service job opened', 'Job note')
         GROUP BY related_id
       ) x ON x.id = act.id
     ) act ON act.related_id = a.id AND act.tenant_id = a.tenant_id
     SET a.visit_subject = COALESCE(NULLIF(a.visit_subject, ''), act.subject),
         a.visit_detail = COALESCE(NULLIF(a.visit_detail, ''), act.body)
     WHERE a.visit_subject IS NULL OR a.visit_subject = '' OR a.visit_detail IS NULL OR a.visit_detail = ''"
);

echo "Visit subject migrate finished.\n";
