#!/usr/bin/env bash

set -euo pipefail
set -x

DIR="default-collations.d"
if [[ -d "${DIR}" ]]; then
	echo >&2 "${DIR} already exists."
	exit 1;
fi

mkdir "${DIR}"
cd "${DIR}"
ddev config --project-type=php --database=mysql:8.0
cat > foo_table.sql <<EOF
-- Create Foo table with one string field
CREATE TABLE Foo (
    foo VARCHAR(255)
) CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Insert sample rows with random values
INSERT INTO Foo (foo) VALUES
    ('apple'),
    ('banana');
EOF
cat > index.php <<EOF
<?php

// Connect to database using DDEV defaults
try {
    \$pdo = new PDO(
        'mysql:host=db;dbname=db',
        'db',
        'db',
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
} catch (PDOException \$e) {
    die("Connection failed: " . \$e->getMessage());
}

// Create temporary table
\$pdo->exec("
    CREATE TEMPORARY TABLE api_data (
        foo VARCHAR(255),
        bar VARCHAR(255)
    )
");

// Insert two rows
\$pdo->exec("
    INSERT INTO api_data (foo, bar) VALUES
    ('apple', 'some random value'),
    ('passionfruit', 'another random value')
");

// Join api_data with Foo table and count results
\$stmt = \$pdo->query("
    SELECT COUNT(*) as count
    FROM api_data
    JOIN Foo ON api_data.foo = Foo.foo
");

\$result = \$stmt->fetch(PDO::FETCH_ASSOC);

echo "Number of results: " . \$result['count'] . "\n";
EOF
ddev start
ddev import-db --file=foo_table.sql
ddev launch

ddev config --database=mysql:8.0

cat > .ddev/mysql/collation-server.cnf <<EOF
[mysqld]
collation-server = utf8mb4_0900_ai_ci
EOF

ddev restart
ddev import-db --file=foo_table.sql
ddev launch