<?php

declare(strict_types=1);

/**
 * Zavvion Events local/staging database installer.
 *
 * This command is intentionally extensionless to match the other project CLI
 * scripts and reduce local antivirus false positives on standalone PHP tools.
 */

$root = dirname(__DIR__);

if (file_exists($root . '/vendor/autoload.php')) {
    require_once $root . '/vendor/autoload.php';
}

/**
 * @return array<string, string>
 */
function zv_install_env(string $path): array
{
    if (!is_file($path)) {
        return [];
    }

    $values = [];
    foreach (file($path, FILE_IGNORE_NEW_LINES) ?: [] as $line) {
        $line = trim($line);
        if ($line === '' || str_starts_with($line, '#') || !str_contains($line, '=')) {
            continue;
        }

        [$key, $value] = explode('=', $line, 2);
        $key = trim($key);
        $value = trim($value);
        if (
            (str_starts_with($value, '"') && str_ends_with($value, '"'))
            || (str_starts_with($value, "'") && str_ends_with($value, "'"))
        ) {
            $value = substr($value, 1, -1);
        }

        $values[$key] = $value;
    }

    return $values;
}

/**
 * @return array<string, string|bool>
 */
function zv_install_args(array $argv): array
{
    $options = [
        'schema' => 'docs/schema.sql',
        'seed' => false,
        'allow-demo-seed' => false,
        'fresh' => false,
        'yes' => false,
        'dry-run' => false,
        'help' => false,
    ];

    foreach (array_slice($argv, 1) as $arg) {
        $arg = (string) $arg;
        if ($arg === '--help' || $arg === '-h') {
            $options['help'] = true;
            continue;
        }
        if ($arg === '--seed') {
            $options['seed'] = true;
            continue;
        }
        if ($arg === '--allow-demo-seed') {
            $options['allow-demo-seed'] = true;
            continue;
        }
        if ($arg === '--no-seed') {
            $options['seed'] = false;
            continue;
        }
        if ($arg === '--fresh') {
            $options['fresh'] = true;
            continue;
        }
        if ($arg === '--yes' || $arg === '-y') {
            $options['yes'] = true;
            continue;
        }
        if ($arg === '--dry-run') {
            $options['dry-run'] = true;
            continue;
        }
        if (str_starts_with($arg, '--') && str_contains($arg, '=')) {
            [$key, $value] = explode('=', substr($arg, 2), 2);
            $options[$key] = $value;
        }
    }

    return $options;
}

function zv_install_help(): void
{
    echo <<<'HELP'
Zavvion Events database installer

Usage:
  php bin/install-database [options]

Options:
  --database=zavvion_events   Override DB name from DB_DSN
  --host=127.0.0.1            Override MySQL host from DB_DSN
  --port=3306                 Override MySQL port from DB_DSN
  --user=root                 Override DB_USERNAME / DB_USER
  --password=secret           Override DB_PASSWORD
  --schema=docs/schema.sql    Schema file to import
  --seed --allow-demo-seed    Seed demo MVP data for local/dev/test only
  --no-seed                   Import schema only (default)
  --fresh --yes               Drop and recreate the database first
  --dry-run                   Print planned actions without changing DB
  --help                      Show this help

Common XAMPP command:
  C:\xampp\php\php.exe bin\install-database --fresh --yes --seed --allow-demo-seed

After install checks:
  C:\xampp\php\php.exe bin\check-mvp-smoke --base-url=http://localhost/zavvion-events/public
  C:\xampp\php\php.exe bin\check-local-live-run --base-url=http://localhost/zavvion-events/public

Demo users seeded for local/staging review:
  admin@zavvion.test / ChangeMe123!
  platform.admin@zavvion.test / ChangeMe123!
  organiser@zavvion.test / ChangeMe123!
  organiser.admin@zavvion.test / ChangeMe123!
  event.manager@zavvion.test / ChangeMe123!
  cashier@zavvion.test / ChangeMe123!
  scanner@zavvion.test / ChangeMe123!
  customer@zavvion.test / ChangeMe123!

HELP;
}

/**
 * @return array<string, string>
 */
function zv_install_parse_mysql_dsn(string $dsn): array
{
    if (!str_starts_with($dsn, 'mysql:')) {
        throw new InvalidArgumentException('Only mysql: DSNs are supported by this installer.');
    }

    $parts = [];
    foreach (explode(';', substr($dsn, 6)) as $piece) {
        if (!str_contains($piece, '=')) {
            continue;
        }
        [$key, $value] = explode('=', $piece, 2);
        $parts[trim($key)] = trim($value);
    }

    return $parts;
}

/**
 * @param array<string, string> $parts
 */
function zv_install_server_dsn(array $parts): string
{
    $host = $parts['host'] ?? '127.0.0.1';
    $port = $parts['port'] ?? '3306';
    $charset = $parts['charset'] ?? 'utf8mb4';

    return "mysql:host={$host};port={$port};charset={$charset}";
}

function zv_install_database_dsn(array $parts, string $database): string
{
    $host = $parts['host'] ?? '127.0.0.1';
    $port = $parts['port'] ?? '3306';
    $charset = $parts['charset'] ?? 'utf8mb4';

    return "mysql:host={$host};port={$port};dbname={$database};charset={$charset}";
}

function zv_install_quote_identifier(string $identifier): string
{
    if (!preg_match('/^[A-Za-z0-9_]+$/', $identifier)) {
        throw new InvalidArgumentException("Unsafe MySQL identifier: {$identifier}");
    }

    return '`' . $identifier . '`';
}

/**
 * @return list<string>
 */
function zv_install_split_sql(string $sql): array
{
    $sql = preg_replace('/^\xEF\xBB\xBF/', '', $sql) ?? $sql;
    $statements = [];
    $buffer = '';
    $quote = null;
    $length = strlen($sql);

    for ($i = 0; $i < $length; $i++) {
        $char = $sql[$i];
        $buffer .= $char;

        if ($quote !== null) {
            if ($char === '\\') {
                if ($i + 1 < $length) {
                    $buffer .= $sql[++$i];
                }
                continue;
            }

            if ($char === $quote) {
                if ($i + 1 < $length && $sql[$i + 1] === $quote && $quote !== '`') {
                    $buffer .= $sql[++$i];
                    continue;
                }
                $quote = null;
            }
            continue;
        }

        if ($char === "'" || $char === '"' || $char === '`') {
            $quote = $char;
            continue;
        }

        if ($char === ';') {
            $statement = trim(substr($buffer, 0, -1));
            if ($statement !== '') {
                $statements[] = $statement;
            }
            $buffer = '';
        }
    }

    $last = trim($buffer);
    if ($last !== '') {
        $statements[] = $last;
    }

    return $statements;
}

function zv_install_import_schema(PDO $pdo, string $schemaPath): int
{
    if (!is_file($schemaPath)) {
        throw new RuntimeException("Schema file not found: {$schemaPath}");
    }

    $count = 0;
    $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    try {
        foreach (zv_install_split_sql((string) file_get_contents($schemaPath)) as $statement) {
            $pdo->exec($statement);
            $count++;
        }
    } finally {
        $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
    }

    return $count;
}

/**
 * @param array<string, mixed> $row
 * @param list<string>|null $updateColumns
 */
function zv_install_upsert(PDO $pdo, string $table, array $row, ?array $updateColumns = null): void
{
    $columns = array_keys($row);
    $quotedColumns = array_map(static fn (string $column): string => '`' . $column . '`', $columns);
    $placeholders = array_map(static fn (string $column): string => ':' . $column, $columns);

    if ($updateColumns === null) {
        $updateColumns = array_values(array_filter($columns, static fn (string $column): bool => $column !== 'id'));
    }

    $updates = $updateColumns === []
        ? '`id` = `id`'
        : implode(', ', array_map(static fn (string $column): string => '`' . $column . '` = VALUES(`' . $column . '`)', $updateColumns));

    $sql = 'INSERT INTO `' . $table . '` (' . implode(', ', $quotedColumns) . ') VALUES (' . implode(', ', $placeholders) . ') ON DUPLICATE KEY UPDATE ' . $updates;
    $pdo->prepare($sql)->execute($row);
}

/**
 * @param array<string, mixed> $row
 */
function zv_install_insert_ignore(PDO $pdo, string $table, array $row): void
{
    $columns = array_keys($row);
    $quotedColumns = array_map(static fn (string $column): string => '`' . $column . '`', $columns);
    $placeholders = array_map(static fn (string $column): string => ':' . $column, $columns);
    $sql = 'INSERT IGNORE INTO `' . $table . '` (' . implode(', ', $quotedColumns) . ') VALUES (' . implode(', ', $placeholders) . ')';
    $pdo->prepare($sql)->execute($row);
}

function zv_install_seed_identity(PDO $pdo): void
{
    $now = date('Y-m-d H:i:s');
    $passwordHash = password_hash('ChangeMe123!', PASSWORD_BCRYPT);

    foreach ([
        ['aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'Platform Admin', 'admin@zavvion.test', null],
        ['77777777777777777777777777777777', 'Demo Platform Admin', 'platform.admin@zavvion.test', null],
        ['bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', 'Demo Organiser', 'organiser@zavvion.test', null],
        ['99999999999999999999999999999999', 'Demo Organiser Admin', 'organiser.admin@zavvion.test', null],
        ['dddddddddddddddddddddddddddddddd', 'Demo Event Manager', 'event.manager@zavvion.test', null],
        ['eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 'Demo Cashier', 'cashier@zavvion.test', null],
        ['ffffffffffffffffffffffffffffffff', 'Demo Ticket Scanner', 'scanner@zavvion.test', null],
        ['cccccccccccccccccccccccccccccccc', 'Demo Customer', 'customer@zavvion.test', null],
    ] as [$id, $name, $email, $phone]) {
        zv_install_upsert($pdo, 'users', [
            'id' => $id,
            'name' => $name,
            'email' => $email,
            'phone' => $phone,
            'password_hash' => $passwordHash,
            'email_verified_at' => $now,
            'phone_verified_at' => null,
            'status' => 'active',
            'created_at' => $now,
            'updated_at' => $now,
            'deleted_at' => null,
        ], ['name', 'status', 'email_verified_at', 'updated_at']);
    }
}

function zv_install_seed(PDO $pdo): array
{
    $now = date('Y-m-d H:i:s');

    $pdo->beginTransaction();
    try {
        zv_install_seed_identity($pdo);

        $roles = [
            ['10000000000000000000000000000001', 'platform_super_admin', 'Platform Super Admin', 'platform'],
            ['10000000000000000000000000000002', 'platform_admin', 'Platform Admin', 'platform'],
            ['10000000000000000000000000000003', 'organiser_owner', 'Organiser Owner', 'organiser'],
            ['10000000000000000000000000000004', 'organiser_admin', 'Organiser Admin', 'organiser'],
            ['10000000000000000000000000000005', 'event_manager', 'Event Manager', 'event'],
            ['10000000000000000000000000000006', 'cashier', 'Cashier', 'event'],
            ['10000000000000000000000000000007', 'ticket_scanner', 'Ticket Scanner', 'event'],
            ['10000000000000000000000000000008', 'customer', 'Customer', 'customer'],
        ];
        foreach ($roles as [$id, $key, $name, $scope]) {
            zv_install_upsert($pdo, 'roles', [
                'id' => $id,
                'role_key' => $key,
                'name' => $name,
                'scope' => $scope,
                'is_system' => 1,
                'created_at' => $now,
                'updated_at' => $now,
            ], ['name', 'scope', 'is_system', 'updated_at']);
        }

        $permissions = [
            ['20000000000000000000000000000001', 'platform.manage', 'Manage platform settings', 'admin'],
            ['20000000000000000000000000000002', 'organisers.review', 'Review organiser applications', 'admin'],
            ['20000000000000000000000000000003', 'events.manage', 'Manage events', 'event'],
            ['20000000000000000000000000000004', 'venues.manage', 'Manage venues and seat maps', 'venue'],
            ['20000000000000000000000000000005', 'tickets.manage', 'Manage ticket types and inventory', 'ticket'],
            ['20000000000000000000000000000006', 'orders.manage', 'Manage orders and attendees', 'order'],
            ['20000000000000000000000000000007', 'box_office.sell', 'Sell tickets at box office', 'checkout'],
            ['20000000000000000000000000000008', 'scanner.scan', 'Scan tickets', 'scanner'],
            ['20000000000000000000000000000009', 'finance.view', 'View finance and reports', 'finance'],
            ['20000000000000000000000000000010', 'customer.tickets', 'Manage own tickets', 'customer'],
            ['20000000000000000000000000000011', 'staff.manage', 'Manage organiser staff', 'organiser'],
            ['20000000000000000000000000000012', 'media.manage', 'Manage site and event media', 'media'],
            ['20000000000000000000000000000013', 'products.manage', 'Manage merchandise products', 'shop'],
            ['20000000000000000000000000000014', 'promos.manage', 'Manage promo codes', 'promo'],
            ['20000000000000000000000000000015', 'donations.manage', 'Manage donation settings', 'donation'],
            ['20000000000000000000000000000016', 'attendees.import', 'Import attendee data', 'attendee'],
            ['20000000000000000000000000000017', 'scanner.manage', 'Manage scanner devices', 'scanner'],
            ['20000000000000000000000000000018', 'payments.manage', 'Manage organiser payment setup', 'payment'],
            ['20000000000000000000000000000019', 'reports.view', 'View operational reports', 'report'],
            ['20000000000000000000000000000020', 'compliance.manage', 'Manage compliance and privacy requests', 'compliance'],
            ['20000000000000000000000000000021', 'features.manage', 'Manage organiser feature controls', 'admin'],
            ['20000000000000000000000000000022', 'finance.manage', 'Manage tax and fee rules', 'finance'],
        ];
        foreach ($permissions as [$id, $key, $name, $module]) {
            zv_install_upsert($pdo, 'permissions', [
                'id' => $id,
                'permission_key' => $key,
                'name' => $name,
                'module' => $module,
                'created_at' => $now,
            ], ['name', 'module']);
        }

        $rolePermissions = [
            'platform_super_admin' => array_column($permissions, 1),
            'platform_admin' => ['platform.manage', 'organisers.review', 'events.manage', 'orders.manage', 'finance.view', 'reports.view', 'compliance.manage', 'features.manage', 'media.manage', 'finance.manage'],
            'organiser_owner' => ['events.manage', 'venues.manage', 'tickets.manage', 'orders.manage', 'box_office.sell', 'scanner.scan', 'scanner.manage', 'finance.view', 'reports.view', 'staff.manage', 'media.manage', 'products.manage', 'promos.manage', 'donations.manage', 'attendees.import', 'payments.manage'],
            'organiser_admin' => ['events.manage', 'venues.manage', 'tickets.manage', 'orders.manage', 'box_office.sell', 'scanner.scan', 'scanner.manage', 'finance.view', 'reports.view', 'staff.manage', 'media.manage', 'products.manage', 'promos.manage', 'donations.manage', 'attendees.import', 'payments.manage'],
            'event_manager' => ['events.manage', 'venues.manage', 'tickets.manage', 'orders.manage', 'scanner.scan', 'scanner.manage', 'reports.view', 'media.manage', 'products.manage', 'promos.manage', 'donations.manage', 'attendees.import'],
            'cashier' => ['orders.manage', 'box_office.sell'],
            'ticket_scanner' => ['scanner.scan'],
            'customer' => ['customer.tickets'],
        ];
        $attach = $pdo->prepare('INSERT IGNORE INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r INNER JOIN permissions p ON p.permission_key = :permission_key WHERE r.role_key = :role_key');
        foreach ($rolePermissions as $roleKey => $permissionKeys) {
            foreach ($permissionKeys as $permissionKey) {
                $attach->execute(['role_key' => $roleKey, 'permission_key' => $permissionKey]);
            }
        }

        zv_install_upsert($pdo, 'organisers', [
            'id' => '30000000000000000000000000000001',
            'name' => 'Riverside Productions',
            'slug' => 'riverside-productions',
            'status' => 'approved',
            'country' => 'GB',
            'tax_identifier' => 'GB-DEMO-001',
            'website' => 'https://example.test',
            'description' => 'Sample approved organiser for local MVP handover.',
            'created_at' => $now,
            'updated_at' => $now,
        ], ['name', 'status', 'country', 'tax_identifier', 'website', 'description', 'updated_at']);

        foreach ([
            [
                'id' => '30000000000000000000000000000011',
                'organisation_name' => 'Northstar Live',
                'contact_name' => 'Asha Perera',
                'email' => 'asha@northstar-live.test',
                'phone' => '+447700900111',
                'address' => '22 Harbour Road, London',
                'country' => 'GB',
                'business_details' => 'Company registration and public liability insurance pending manual review.',
                'status' => 'submitted',
                'reviewed_by' => null,
                'reviewed_at' => null,
                'created_at' => $now,
            ],
            [
                'id' => '30000000000000000000000000000012',
                'organisation_name' => 'Lotus Arts Collective',
                'contact_name' => 'Mina Shah',
                'email' => 'mina@lotus-arts.test',
                'phone' => '+447700900222',
                'address' => '8 Gallery Lane, Manchester',
                'country' => 'GB',
                'business_details' => 'Manual KYC documents received; awaiting final platform approval.',
                'status' => 'submitted',
                'reviewed_by' => null,
                'reviewed_at' => null,
                'created_at' => $now,
            ],
            [
                'id' => '30000000000000000000000000000013',
                'organisation_name' => 'Harbour Comedy Rooms',
                'contact_name' => 'Ben Lewis',
                'email' => 'ben@harbour-comedy.test',
                'phone' => '+447700900333',
                'address' => '5 Pier Street, Bristol',
                'country' => 'GB',
                'business_details' => 'Disabled sample application for workflow review.',
                'status' => 'rejected',
                'reviewed_by' => 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
                'reviewed_at' => $now,
                'created_at' => $now,
            ],
        ] as $application) {
            zv_install_upsert($pdo, 'organiser_applications', $application, [
                'organisation_name', 'contact_name', 'email', 'phone', 'address', 'country',
                'business_details', 'status', 'reviewed_by', 'reviewed_at',
            ]);
        }

        zv_install_upsert($pdo, 'organiser_staff', [
            'id' => '31000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'user_id' => 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',
            'status' => 'active',
            'created_at' => $now,
        ], ['status']);

        foreach ([
            ['31000000000000000000000000000002', '99999999999999999999999999999999'],
            ['31000000000000000000000000000003', 'dddddddddddddddddddddddddddddddd'],
            ['31000000000000000000000000000004', 'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'],
            ['31000000000000000000000000000005', 'ffffffffffffffffffffffffffffffff'],
        ] as [$staffId, $userId]) {
            zv_install_upsert($pdo, 'organiser_staff', [
                'id' => $staffId,
                'organiser_id' => '30000000000000000000000000000001',
                'user_id' => $userId,
                'status' => 'active',
                'created_at' => $now,
            ], ['status']);
        }

        foreach ([
            ['32000000000000000000000000000001', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', '10000000000000000000000000000001', null],
            ['32000000000000000000000000000002', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', '10000000000000000000000000000003', '30000000000000000000000000000001'],
            ['32000000000000000000000000000003', 'cccccccccccccccccccccccccccccccc', '10000000000000000000000000000008', null],
            ['32000000000000000000000000000004', '99999999999999999999999999999999', '10000000000000000000000000000004', '30000000000000000000000000000001'],
            ['32000000000000000000000000000005', 'dddddddddddddddddddddddddddddddd', '10000000000000000000000000000005', '30000000000000000000000000000001'],
            ['32000000000000000000000000000006', 'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', '10000000000000000000000000000006', '30000000000000000000000000000001'],
            ['32000000000000000000000000000007', 'ffffffffffffffffffffffffffffffff', '10000000000000000000000000000007', '30000000000000000000000000000001'],
            ['32000000000000000000000000000008', '77777777777777777777777777777777', '10000000000000000000000000000002', null],
        ] as [$id, $userId, $roleId, $organiserId]) {
            zv_install_insert_ignore($pdo, 'user_roles', [
                'id' => $id,
                'user_id' => $userId,
                'role_id' => $roleId,
                'organiser_id' => $organiserId,
                'event_id' => null,
                'created_at' => $now,
            ]);
        }

        zv_install_upsert($pdo, 'event_categories', [
            'id' => '33000000000000000000000000000001',
            'name' => 'Live Music',
            'slug' => 'live-music',
            'created_at' => $now,
        ], ['name']);

        zv_install_upsert($pdo, 'venues', [
            'id' => '34000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'name' => 'Riverside Hall',
            'address' => '1 Demo Street, London',
            'country' => 'GB',
            'floor_plan_path' => null,
            'created_at' => $now,
            'updated_at' => $now,
        ], ['name', 'address', 'country', 'updated_at']);
        zv_install_upsert($pdo, 'venue_sections', [
            'id' => '35000000000000000000000000000001',
            'venue_id' => '34000000000000000000000000000001',
            'name' => 'Stalls',
            'sort_order' => 1,
            'created_at' => $now,
        ], ['name', 'sort_order']);
        zv_install_upsert($pdo, 'venue_seat_maps', [
            'id' => '36000000000000000000000000000001',
            'venue_id' => '34000000000000000000000000000001',
            'name' => 'Main Hall',
            'layout_json' => json_encode(['version' => 1, 'mode' => 'reserved', 'rows' => 3, 'columns' => 12], JSON_THROW_ON_ERROR),
            'created_at' => $now,
            'updated_at' => $now,
        ], ['name', 'layout_json', 'updated_at']);

        zv_install_upsert($pdo, 'events', [
            'id' => '39000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'category_id' => '33000000000000000000000000000001',
            'venue_id' => '34000000000000000000000000000001',
            'title' => 'Demo Gala',
            'slug' => 'demo-gala',
            'description' => 'A seeded event with reserved seats, adult and child tickets, merchandise, donations, and promo-code support.',
            'start_at' => '2026-06-20 19:30:00',
            'end_at' => '2026-06-20 22:30:00',
            'timezone' => 'Europe/London',
            'city' => 'London',
            'country_code' => 'GB',
            'currency_code' => 'GBP',
            'locale' => 'en_GB',
            'date_style' => 'medium',
            'time_style' => '24h',
            'address_display_options' => json_encode(['hide_postal_code' => false, 'use_short_country_name' => true], JSON_THROW_ON_ERROR),
            'online_payment_enabled' => 1,
            'counter_cash_enabled' => 1,
            'counter_card_enabled' => 1,
            'counter_external_card_enabled' => 1,
            'seating_mode' => 'reserved',
            'status' => 'published',
            'visibility' => 'public',
            'donation_enabled' => 1,
            'merchandise_enabled' => 1,
            'promo_enabled' => 1,
            'max_tickets_per_order' => 10,
            'refund_policy_text' => 'Refund decisions and approved refund processing are the organiser responsibility under their published event policy.',
            'all_ages_allowed' => 1,
            'minimum_age' => null,
            'child_tickets_allowed' => 1,
            'infants_allowed' => 1,
            'under_18_requires_adult' => 1,
            'age_restriction_message' => 'Under-18s must attend with an adult ticket holder.',
            'venue_age_note' => 'Photo ID may be requested for concession tickets.',
            'seo_title' => 'Demo Gala Tickets',
            'seo_description' => 'Demo Gala tickets on Zavvion Events.',
            'created_at' => $now,
            'updated_at' => $now,
        ], ['description', 'start_at', 'end_at', 'timezone', 'city', 'country_code', 'currency_code', 'locale', 'date_style', 'time_style', 'address_display_options', 'online_payment_enabled', 'counter_cash_enabled', 'counter_card_enabled', 'counter_external_card_enabled', 'status', 'visibility', 'donation_enabled', 'merchandise_enabled', 'promo_enabled', 'max_tickets_per_order', 'refund_policy_text', 'all_ages_allowed', 'minimum_age', 'child_tickets_allowed', 'infants_allowed', 'under_18_requires_adult', 'age_restriction_message', 'venue_age_note', 'seo_title', 'seo_description', 'updated_at']);

        $rows = ['A', 'B', 'C'];
        $seatCounter = 1;
        foreach ($rows as $rowIndex => $rowLabel) {
            for ($column = 1; $column <= 12; $column++) {
                $seatId = '37' . str_pad((string) $seatCounter, 30, '0', STR_PAD_LEFT);
                $eventSeatId = '38' . str_pad((string) $seatCounter, 30, '0', STR_PAD_LEFT);
                $sold = in_array($seatCounter, [8, 9, 14], true);
                zv_install_upsert($pdo, 'seats', [
                    'id' => $seatId,
                    'venue_seat_map_id' => '36000000000000000000000000000001',
                    'venue_section_id' => '35000000000000000000000000000001',
                    'label' => $rowLabel . $column,
                    'row_label' => $rowLabel,
                    'seat_number' => (string) $column,
                    'seat_type' => $column === 6 ? 'accessible' : 'standard',
                    'x' => $column * 10,
                    'y' => 20 + ($rowIndex * 10),
                    'is_active' => 1,
                    'created_at' => $now,
                ], ['label', 'row_label', 'seat_number', 'seat_type', 'x', 'y', 'is_active']);
                zv_install_upsert($pdo, 'event_seats', [
                    'id' => $eventSeatId,
                    'event_id' => '39000000000000000000000000000001',
                    'seat_id' => $seatId,
                    'status' => $sold ? 'sold' : 'available',
                    'created_at' => $now,
                    'updated_at' => $now,
                ], ['updated_at']);
                $seatCounter++;
            }
        }

        zv_install_upsert($pdo, 'tax_rules', [
            'id' => '40000000000000000000000000000001',
            'label' => 'VAT',
            'country' => 'GB',
            'region' => null,
            'rate_percent' => 20,
            'applies_to_ticket' => 1,
            'applies_to_platform_fee' => 1,
            'applies_to_merchandise' => 1,
            'applies_to_donation' => 0,
            'active_from' => '2026-01-01',
            'active_until' => null,
            'is_active' => 1,
            'created_at' => $now,
        ], ['label', 'rate_percent', 'is_active']);

        zv_install_upsert($pdo, 'platform_fee_rules', [
            'id' => '41000000000000000000000000000001',
            'organiser_id' => null,
            'event_id' => null,
            'category_id' => null,
            'fee_type' => 'percentage',
            'fixed_minor_amount' => 0,
            'percentage_rate' => 10,
            'min_minor_amount' => null,
            'max_minor_amount' => null,
            'currency' => 'GBP',
            'active_from' => '2026-01-01',
            'active_until' => null,
            'is_active' => 1,
            'created_at' => $now,
        ], ['fee_type', 'percentage_rate', 'currency', 'is_active']);
        zv_install_upsert($pdo, 'platform_fee_rules', [
            'id' => '41000000000000000000000000000002',
            'organiser_id' => null,
            'event_id' => null,
            'category_id' => null,
            'fee_type' => 'fixed',
            'fixed_minor_amount' => 100,
            'percentage_rate' => 0,
            'min_minor_amount' => null,
            'max_minor_amount' => null,
            'currency' => 'GBP',
            'active_from' => '2026-01-01',
            'active_until' => null,
            'is_active' => 1,
            'created_at' => $now,
        ], ['fee_type', 'fixed_minor_amount', 'currency', 'is_active']);

        foreach ([
            ['42000000000000000000000000000001', 'Adult Reserved Seat', 'Adult reserved seating ticket.', 'adult', null, null, 0, 0, 0, 0, 0, 'Standard adult ticket terms apply.', 10000, 500, 1, 10, 10],
            ['42000000000000000000000000000002', 'Child Reserved Seat', 'Child reserved seating ticket. Must be purchased with at least one adult ticket.', 'child', 3, 15, 1, 1, 0, 0, 1, 'Under-16s must attend with an adult.', 5000, 250, 1, 10, 20],
        ] as [$id, $name, $description, $category, $minAge, $maxAge, $requiresAdult, $adultRequired, $isFree, $requiresId, $parentalConsent, $terms, $price, $quantity, $minPerOrder, $maxPerOrder, $sortOrder]) {
            zv_install_upsert($pdo, 'ticket_types', [
                'id' => $id,
                'event_id' => '39000000000000000000000000000001',
                'tax_rule_id' => '40000000000000000000000000000001',
                'name' => $name,
                'description' => $description,
                'ticket_category' => $category,
                'min_age' => $minAge,
                'max_age' => $maxAge,
                'requires_adult' => $requiresAdult,
                'adult_ticket_required' => $adultRequired,
                'is_free' => $isFree,
                'requires_id_check' => $requiresId,
                'requires_parental_consent' => $parentalConsent,
                'terms_text' => $terms,
                'price_minor_amount' => $price,
                'currency' => 'GBP',
                'quantity' => $quantity,
                'min_per_order' => $minPerOrder,
                'max_per_order' => $maxPerOrder,
                'sale_start_at' => null,
                'sale_end_at' => null,
                'visibility' => 'public',
                'status' => 'active',
                'sort_order' => $sortOrder,
                'created_at' => $now,
                'updated_at' => $now,
            ], ['name', 'description', 'ticket_category', 'min_age', 'max_age', 'requires_adult', 'adult_ticket_required', 'is_free', 'requires_id_check', 'requires_parental_consent', 'terms_text', 'price_minor_amount', 'currency', 'quantity', 'min_per_order', 'max_per_order', 'visibility', 'status', 'sort_order', 'updated_at']);
        }

        foreach ([
            ['43000000000000000000000000000001', '42000000000000000000000000000001', 500],
            ['43000000000000000000000000000002', '42000000000000000000000000000002', 250],
        ] as [$id, $ticketTypeId, $available]) {
            zv_install_upsert($pdo, 'ticket_inventory', [
                'id' => $id,
                'ticket_type_id' => $ticketTypeId,
                'available_quantity' => $available,
                'held_quantity' => 0,
                'sold_quantity' => 0,
                'updated_at' => $now,
            ], ['updated_at']);
        }

        foreach ([
            ['45000000000000000000000000000011', 'Samira Patel', 'samira@example.test', 10000, 2000, 12000, 1000, 200, 1200, 10800, 0, 0],
            ['45000000000000000000000000000012', 'David Morgan', 'david@example.test', 15000, 3000, 18000, 1500, 300, 1800, 16200, 0, 0],
            ['45000000000000000000000000000013', 'Leah Chen', 'leah@example.test', 10000, 2000, 12000, 1000, 200, 1200, 10800, 500, 2500],
        ] as [$orderId, $customerName, $customerEmail, $ticketNet, $ticketTax, $ticketGross, $feeNet, $feeTax, $feeGross, $organiserGross, $donation, $merchGross]) {
            zv_install_upsert($pdo, 'orders', [
                'id' => $orderId,
                'user_id' => 'cccccccccccccccccccccccccccccccc',
                'organiser_id' => '30000000000000000000000000000001',
                'event_id' => '39000000000000000000000000000001',
                'customer_name' => $customerName,
                'customer_email' => $customerEmail,
                'customer_phone' => null,
                'status' => 'paid',
                'ticket_net_amount' => $ticketNet,
                'ticket_tax_amount' => $ticketTax,
                'ticket_gross_amount' => $ticketGross,
                'platform_fee_net_amount' => $feeNet,
                'platform_fee_tax_amount' => $feeTax,
                'platform_fee_gross_amount' => $feeGross,
                'organiser_gross_amount' => $organiserGross + $donation + $merchGross,
                'stripe_fee_amount' => 0,
                'donation_amount' => $donation,
                'merchandise_net_amount' => $merchGross,
                'merchandise_tax_amount' => 0,
                'merchandise_gross_amount' => $merchGross,
                'currency' => 'GBP',
                'tax_rule_id' => '40000000000000000000000000000001',
                'fee_rule_id' => '41000000000000000000000000000001',
                'paid_at' => $now,
                'created_at' => $now,
                'updated_at' => $now,
            ], ['status', 'ticket_net_amount', 'ticket_tax_amount', 'ticket_gross_amount', 'platform_fee_net_amount', 'platform_fee_tax_amount', 'platform_fee_gross_amount', 'organiser_gross_amount', 'donation_amount', 'merchandise_net_amount', 'merchandise_tax_amount', 'merchandise_gross_amount', 'paid_at', 'updated_at']);
        }

        foreach ([
            ['45100000000000000000000000000011', '45000000000000000000000000000011', 'ticket', '42000000000000000000000000000001', 'Adult Reserved Seat', 1, 10000, 2000, 12000],
            ['45100000000000000000000000000012', '45000000000000000000000000000012', 'ticket', '42000000000000000000000000000001', 'Adult Reserved Seat', 1, 10000, 2000, 12000],
            ['45100000000000000000000000000013', '45000000000000000000000000000012', 'ticket', '42000000000000000000000000000002', 'Child Reserved Seat', 1, 5000, 1000, 6000],
            ['45100000000000000000000000000014', '45000000000000000000000000000013', 'ticket', '42000000000000000000000000000001', 'Adult Reserved Seat', 1, 10000, 2000, 12000],
            ['45100000000000000000000000000015', '45000000000000000000000000000013', 'merchandise', '44000000000000000000000000000001', 'Event T-Shirt', 1, 2500, 0, 2500],
            ['45100000000000000000000000000016', '45000000000000000000000000000013', 'donation', null, 'Donation', 1, 500, 0, 500],
        ] as [$itemId, $orderId, $itemType, $referenceId, $description, $quantity, $net, $tax, $gross]) {
            zv_install_upsert($pdo, 'order_items', [
                'id' => $itemId,
                'order_id' => $orderId,
                'item_type' => $itemType,
                'reference_id' => $referenceId,
                'description' => $description,
                'quantity' => $quantity,
                'net_minor_amount' => $net,
                'tax_minor_amount' => $tax,
                'gross_minor_amount' => $gross,
                'created_at' => $now,
            ], ['description', 'quantity', 'net_minor_amount', 'tax_minor_amount', 'gross_minor_amount']);
        }

        foreach ([
            ['45200000000000000000000000000011', '45000000000000000000000000000011', '42000000000000000000000000000001', '38000000000000000000000000000008', 'Samira Patel'],
            ['45200000000000000000000000000012', '45000000000000000000000000000012', '42000000000000000000000000000001', '38000000000000000000000000000009', 'David Morgan'],
            ['45200000000000000000000000000013', '45000000000000000000000000000012', '42000000000000000000000000000002', '38000000000000000000000000000014', 'Child Guest'],
        ] as [$ticketId, $orderId, $ticketTypeId, $eventSeatId, $attendeeName]) {
            zv_install_upsert($pdo, 'tickets', [
                'id' => $ticketId,
                'order_id' => $orderId,
                'event_id' => '39000000000000000000000000000001',
                'ticket_type_id' => $ticketTypeId,
                'event_seat_id' => $eventSeatId,
                'attendee_name' => $attendeeName,
                'status' => 'issued',
                'checked_in_at' => null,
                'created_at' => $now,
                'updated_at' => $now,
            ], ['status', 'updated_at']);
        }

        zv_install_upsert($pdo, 'products', [
            'id' => '44000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'event_id' => '39000000000000000000000000000001',
            'name' => 'Event T-Shirt',
            'sku' => 'DEMO-TEE',
            'price_minor_amount' => 2500,
            'currency' => 'GBP',
            'stock_quantity' => 100,
            'status' => 'active',
            'created_at' => $now,
            'updated_at' => $now,
        ], ['name', 'event_id', 'price_minor_amount', 'currency', 'stock_quantity', 'status', 'updated_at']);
        zv_install_insert_ignore($pdo, 'product_event_assignments', [
            'id' => '44100000000000000000000000000001',
            'product_id' => '44000000000000000000000000000001',
            'event_id' => '39000000000000000000000000000001',
            'created_at' => $now,
        ]);

        zv_install_upsert($pdo, 'promo_codes', [
            'id' => '45000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'event_id' => '39000000000000000000000000000001',
            'code' => 'WELCOME10',
            'discount_type' => 'percentage',
            'discount_value' => 10,
            'usage_limit' => 100,
            'per_customer_limit' => 1,
            'min_order_minor_amount' => 0,
            'starts_at' => null,
            'expires_at' => null,
            'is_active' => 1,
            'created_at' => $now,
        ], ['discount_type', 'discount_value', 'usage_limit', 'per_customer_limit', 'min_order_minor_amount', 'is_active']);

        zv_install_upsert($pdo, 'event_donation_settings', [
            'id' => '47000000000000000000000000000001',
            'organiser_id' => '30000000000000000000000000000001',
            'event_id' => '39000000000000000000000000000001',
            'enabled' => 1,
            'donation_only_checkout_allowed' => 0,
            'custom_amount_allowed' => 1,
            'suggested_amounts_json' => json_encode([500, 1000, 2500], JSON_THROW_ON_ERROR),
            'appeal_title' => 'Support this event',
            'appeal_body' => 'Optional donations are shown separately on orders and reports.',
            'tax_rule_id' => null,
            'created_at' => $now,
            'updated_at' => $now,
        ], ['enabled', 'donation_only_checkout_allowed', 'custom_amount_allowed', 'suggested_amounts_json', 'appeal_title', 'appeal_body', 'updated_at']);

        zv_install_upsert($pdo, 'settings', [
            'id' => '46000000000000000000000000000001',
            'setting_key' => 'donation_only_checkout_allowed',
            'setting_value' => json_encode(false, JSON_THROW_ON_ERROR),
            'created_at' => $now,
            'updated_at' => $now,
        ], ['setting_value', 'updated_at']);

        $pdo->commit();
    } catch (Throwable $exception) {
        $pdo->rollBack();
        throw $exception;
    }

    return [
        'users' => 8,
        'organisers' => 1,
        'events' => 1,
        'seats' => 36,
        'ticket_types' => 2,
        'products' => 1,
        'promo_codes' => 1,
    ];
}

function zv_install_runtime_defaults(PDO $pdo, string $root): void
{
    $mvpPath = $root . '/public/mvp.php';
    if (!is_file($mvpPath)) {
        return;
    }

    require_once $mvpPath;
    if (function_exists('mvp_ensure_schema')) {
        mvp_ensure_schema($pdo);
    }
}

$options = zv_install_args($argv ?? []);
if ($options['help']) {
    zv_install_help();
    exit(0);
}

$env = array_replace(zv_install_env($root . '/.env.example'), zv_install_env($root . '/.env'));
$dsn = (string) ($options['dsn'] ?? $env['DB_DSN'] ?? 'mysql:host=127.0.0.1;port=3306;dbname=zavvion_events;charset=utf8mb4');
$parts = zv_install_parse_mysql_dsn($dsn);
foreach (['host', 'port'] as $key) {
    if (isset($options[$key]) && is_string($options[$key]) && $options[$key] !== '') {
        $parts[$key] = $options[$key];
    }
}
$database = (string) ($options['database'] ?? $parts['dbname'] ?? 'zavvion_events');
$user = (string) ($options['user'] ?? $env['DB_USERNAME'] ?? $env['DB_USER'] ?? 'root');
$password = (string) ($options['password'] ?? $env['DB_PASSWORD'] ?? '');
$schemaPath = (string) $options['schema'];
if (!str_contains($schemaPath, ':') && !str_starts_with($schemaPath, '/') && !preg_match('/^[A-Za-z]:\\\\/', $schemaPath)) {
    $schemaPath = $root . '/' . str_replace('\\', '/', $schemaPath);
}

$appEnv = strtolower((string) ($env['APP_ENV'] ?? 'dev'));
$fresh = (bool) $options['fresh'];
$dryRun = (bool) $options['dry-run'];

if ($fresh && !$options['yes']) {
    throw new RuntimeException('Refusing --fresh without --yes. This option drops the target database first.');
}
if ($fresh && in_array($appEnv, ['prod', 'production'], true)) {
    throw new RuntimeException('Refusing --fresh while APP_ENV is production/prod.');
}
if ((bool) $options['seed'] && !$options['allow-demo-seed']) {
    throw new RuntimeException('Refusing to seed demo users/data without --allow-demo-seed. Production installs must create real admin credentials through the approved launch process.');
}
if ((bool) $options['seed'] && !in_array($appEnv, ['local', 'dev', 'development', 'test', 'testing'], true)) {
    throw new RuntimeException('Refusing to seed demo users/data unless APP_ENV is explicitly local/dev/test. Re-run with --no-seed after creating real admin credentials through the approved launch process.');
}

$plan = [
    'database' => $database,
    'host' => $parts['host'] ?? '127.0.0.1',
    'port' => $parts['port'] ?? '3306',
    'schema' => $schemaPath,
    'fresh' => $fresh,
    'seed' => (bool) $options['seed'],
    'runtime_defaults' => (bool) $options['seed'],
];

if ($dryRun) {
    echo json_encode(['status' => 'dry_run', 'plan' => $plan], JSON_PRETTY_PRINT) . PHP_EOL;
    exit(0);
}

try {
    $server = new PDO(zv_install_server_dsn($parts), $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $quotedDatabase = zv_install_quote_identifier($database);
    if ($fresh) {
        $server->exec("DROP DATABASE IF EXISTS {$quotedDatabase}");
    }
    $server->exec("CREATE DATABASE IF NOT EXISTS {$quotedDatabase} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

    $pdo = new PDO(zv_install_database_dsn($parts, $database), $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $statementCount = zv_install_import_schema($pdo, $schemaPath);
    if ((bool) $options['seed']) {
        zv_install_seed_identity($pdo);
        zv_install_runtime_defaults($pdo, $root);
    }
    $seeded = (bool) $options['seed'] ? zv_install_seed($pdo) : [];
    $phpBinary = PHP_BINARY !== '' ? PHP_BINARY : 'php';

    echo json_encode([
        'status' => 'ok',
        'database' => $database,
        'schema_statements' => $statementCount,
        'seeded' => $seeded,
        'demo_credentials' => [
            'admin' => 'admin@zavvion.test / ChangeMe123!',
            'platform_admin' => 'platform.admin@zavvion.test / ChangeMe123!',
            'organiser' => 'organiser@zavvion.test / ChangeMe123!',
            'organiser_admin' => 'organiser.admin@zavvion.test / ChangeMe123!',
            'event_manager' => 'event.manager@zavvion.test / ChangeMe123!',
            'cashier' => 'cashier@zavvion.test / ChangeMe123!',
            'scanner' => 'scanner@zavvion.test / ChangeMe123!',
            'customer' => 'customer@zavvion.test / ChangeMe123!',
        ],
        'next_checks' => [
            $phpBinary . ' bin/check-mvp-smoke --base-url=http://localhost/zavvion-events/public',
            $phpBinary . ' bin/check-local-live-run --base-url=http://localhost/zavvion-events/public',
        ],
    ], JSON_PRETTY_PRINT) . PHP_EOL;
    exit(0);
} catch (Throwable $exception) {
    fwrite(STDERR, json_encode([
        'status' => 'error',
        'message' => $exception->getMessage(),
        'database' => $database,
    ], JSON_PRETTY_PRINT) . PHP_EOL);
    exit(1);
}