# Blog Database Changes and Testing

Run application commands from the project root on branch `feat/symfony-blog-api`.
Use PHP 7.4 for consistency with the verified dependency installation. The
commands below target `dev`; for a production deployment consistently use
`--env=prod --no-debug` and verify the production database first.

## 1. MySQL Changes

No existing business table is altered. The module adds these 13 tables:

| Table | Purpose |
| --- | --- |
| `blog_admin_users` | Separate Blog administrators/editors and password hashes |
| `blog_admin_tokens` | Hashed, expiring, revocable bearer tokens |
| `blog_posts` | Articles, status, scheduling, SEO, featured images, CTA and versions |
| `blog_categories` | Categories |
| `blog_authors` | Author profiles |
| `blog_tags` | Tags |
| `blog_post_tag` | Post/tag relationships |
| `blog_faqs` | Ordered FAQs |
| `blog_related_posts` | Ordered manual related articles |
| `blog_redirects` | Old-slug redirects and hit counts |
| `blog_revisions` | Imported article snapshots |
| `blog_media` | Uploaded/imported image metadata |
| `blog_cache_jobs` | Website cache-revalidation queue and retry state |

Tables use InnoDB and `utf8mb4_unicode_ci`. The SQL includes unique indexes,
query indexes, and foreign keys. Posts are retained when their author/category
is deleted; post-specific relationships are removed when a post is deleted.
Deleting a post does not delete uploaded files.

Full SQL: [sql/blog-cms.mysql.sql](sql/blog-cms.mysql.sql).
Migration: `DoctrineMigrations\Version20261104000000`.

### Recommended: Doctrine Migration

Back up the selected database. Verify `DATABASE_URL` points to the intended
development/staging database, then preview and run only the new migration:

```bash
php7.4 bin/console doctrine:migrations:execute 'DoctrineMigrations\Version20261104000000' --up --dry-run --env=dev
php7.4 bin/console doctrine:migrations:execute 'DoctrineMigrations\Version20261104000000' --up --no-interaction --env=dev
```

Doctrine also records this version in its migration metadata table. Do not run
all pending migrations or `doctrine:schema:update` for this module.

### Alternative: Direct SQL Import

Use this instead of the migration execution above, not in addition to it:

```bash
mysql -h DB_HOST -u DB_USER -p DB_NAME < docs/sql/blog-cms.mysql.sql
php7.4 bin/console doctrine:migrations:version 'DoctrineMigrations\Version20261104000000' --add --no-interaction --env=dev
```

Run the tracking command only after the entire SQL import succeeds and only
when the console's `DATABASE_URL` targets the same database as the import.
MySQL DDL does not roll back on an error: inspect a failed partial import before
retrying. The script intentionally does not drop or silently overwrite tables.

Verify the additions in the selected database:

```sql
SHOW TABLES LIKE 'blog%';
SHOW CREATE TABLE blog_posts;
SHOW CREATE TABLE blog_admin_tokens;
```

## 2. Environment and Dependencies

No additional encryption dependency is needed. On a fresh deployment follow
[blog-cms.md](blog-cms.md#deployment) to install the existing locked dependencies.

For local development, put these in the untracked `.env.local`, replacing the
preview signing secret placeholder with a random value generated using
`openssl rand -hex 32`. Do not change existing application encryption keys:
the Blog now reuses them through `OpenSslApiCryptor`.

```dotenv
BLOG_PUBLIC_URL=http://localhost/Symfony/Git/1133_wifi_orange_branches/1133_stag/public
BLOG_PREVIEW_TOKEN_SECRET=REPLACE_WITH_RANDOM_SIGNING_SECRET
BLOG_MEDIA_DISK=local
BLOG_MEDIA_PUBLIC_URL=http://localhost/Symfony/Git/1133_wifi_orange_branches/1133_stag/public/uploads
BLOG_SITE_URL=
BLOG_SITE_REVALIDATE_SECRET=
```

Use the actual staging origin instead of this localhost prefix when testing
on staging. Blank site/revalidation settings disable external cache calls for
initial tests. Configure them separately when testing website integration.

Ensure the PHP web user can write `public/uploads/blog/media` and
`var/cache/blog-rate-limits`, and that local uploads are served as static files
without PHP execution. Do not use broad `chmod 777` permissions.

```bash
php7.4 bin/console cache:clear --env=dev
php7.4 bin/console debug:router wsqr_blogPosts --env=dev
php7.4 bin/console blog:create-admin blog-admin@example.com 'Blog Administrator' --env=dev
```

The last command prompts for a 12-72 byte password; it creates only a Blog
account, not a mobile-app user.

## 3. Postman Smoke Test

Import [WifiCalls-Blog-CMS.postman_collection.json](../postman/WifiCalls-Blog-CMS.postman_collection.json).
Set collection values locally, not as shared credentials:

```text
baseUrl = http://localhost/Symfony/Git/1133_wifi_orange_branches/1133_stag/public/api
adminEmail = blog-admin@example.com
adminPassword = the password entered above
```

Do not blindly run the entire collection: it includes imports that require an
export file and examples that permanently delete disposable test articles.
Run the following selected requests in order:

1. **Login**: expect HTTP 200 with `ok: true`, `token`, and `user`. The collection
   saves `adminToken`. **Current admin** must then return the same Blog user.
2. **Create category** and **Create author**: expect HTTP 201 and saved IDs.
3. **Create draft**: expect HTTP 201 and a post ID. List it through the admin
   API; it must not appear through the public API yet.
4. Publish the saved draft using the request below. The public list/detail
   must now include it after decrypting the response.
5. **Set post inactive**: public detail must return encrypted HTTP 404 and the
   list must omit it. **Set post active** must restore visibility.
6. **Upload Base64 image**: expect HTTP 201 with an image URL. Open the URL and
   confirm the image renders. Attach it to a test post. Deleting attached media
   must return HTTP 409 rather than removing the file.
7. Run the App Store CTA example. Public detail must expose `cta.buttonStyle`
   as `APP_STORE`. An App Store CTA using a non-Apple URL must fail with 422
   when publishing.
8. Unpublish the test post: public detail returns 404 again. Permanently delete
   only the disposable post when finished.

### Publish the Test Draft

The reference collection does not contain a standalone publish request; add:

```http
POST {{baseUrl}}/admin/blogs/{{postId}}/publish
Authorization: Bearer {{adminToken}}
```

No JSON body or mobile-app `AES` header is required. Admin responses are plain
JSON; public Blog responses intentionally are not.

### Inspect Encrypted Public Data

Run **Public API / List posts - encrypted**. Expect HTTP 200,
`Content-Type: application/json`, and `X-Blog-Encryption: aes-256-cbc`. The raw
body is Base64 ciphertext, not JSON until decrypted. The ciphertext
is saved to `publicCiphertext` by the collection. Then run **Internal Blog Crypto
/ Decrypt last public API response**. This returns plaintext JSON containing
`posts`, `pagination`, and `categories`.

The existing app AES decryptor can decrypt these responses using the same key
and IV settings. No separate Blog password is needed. Do not treat ciphertext
as broken JSON or disable encryption to inspect it. Encryption failures return 503.

## 4. Additional Cases

| Test | Expected result |
| --- | --- |
| Admin request without a bearer token | 401 |
| Wrong login password | 422 |
| Duplicate post slug | 409 |
| Update with an old `version` | 409 |
| EDITOR deletes a post/category/author or imports JSON | 403 |
| Logged-out token used again | 401 |
| Draft/future scheduled/inactive post through public API | Encrypted 404 |
| Change a published slug | Old slug returns 404 with redirect metadata |
| Invalid image data or unsupported format | 422 |
| Delete media still referenced by content | 409 |
| Delete imported media outside `blog/media/` | 409; existing shared storage untouched |

Create an editor for role tests using `blog:create-admin ... --role=EDITOR`.
For scheduling, create a post with `intent: schedule` and `values.publishedAt`
at least one minute in the future. Before that time it must be hidden. After
that time it becomes visible; run `blog:publish-scheduled --env=dev` to finalize
its stored status. See [blog-cms.md](blog-cms.md) for cron setup.

For imports, select a valid `secondline-blog-export` version 1 JSON file and
run **Import JSON - dry run** first. It must not insert rows. Then run the write
request only after reviewing the report. Existing content is skipped unless
`existing=update` is explicitly selected.

For a private draft preview:

```bash
php7.4 bin/console blog:preview POST_ULID --env=dev
```

The printed URL is signed and expires after one hour. Its response is encrypted.

## 5. Automated Regression Suite

Create a separate empty test database, never your app/staging database:

```sql
CREATE DATABASE blog_test_cms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

Give the test DB user rights only to that disposable database and run:

```bash
BLOG_TEST_DATABASE_URL='mysql://TEST_USER:URL_ENCODED_PASSWORD@127.0.0.1/blog_test_cms' php7.4 tests/Blog/run.php
```

The suite creates its schema itself: do not apply the migration or SQL file to
that database first. It refuses a nonempty database or a name not prefixed
`blog_test_`. Credentials containing special URL characters must be URL-encoded.
For private credentials, set the environment variable through a local secrets
mechanism rather than leaving the real password in shell history.

Expected result: `PASS: 95 Blog checks`. The suite uses real Symfony requests,
MySQL, temporary local images and a local HTTP website stand-in. It verifies
cache delivery/retries, byte-for-byte compatibility with the app AES format,
and the existing encryption/Vonage normalization guards
without calling external providers. The test database remains populated for
inspection, so use another empty test database or recreate only that disposable
database before repeating.

Live S3/CloudFront, real website revalidation, and real phone calls still require
separate staging smoke tests. Do not run destructive tests against live content.