# Editable page sections and email templates

## Scope and administration

RollPoints adds the separate `mail_rollpoints_joined` confirmation template.
Enrollment and its PDF-bearing queue record are atomic; see
[RollPoints acceptance and delivery](rollpoints.md#acceptance-and-delivery).
This does not change `mail_terms_accepted` or the main terms acceptance.
Its administrator-facing description and placeholder explanations are Polish.
Migration `20260914110300_translate_rollpoints_email_metadata.sql` corrects
these metadata only and preserves any edited email body and placeholder names.

The `static_text` table supplies two kinds of editable HTML:

- `page`: sections displayed within public pages;
- `mail`: bodies of system email messages.

The management interface separates these two groups. Each entry identifies its
purpose and location or sending condition. The editor changes only the HTML
body. Its name, code, type, description and supported-tag instructions are
maintained with the integration code and database migrations, and displayed as
read-only context. Changing a description or the tag instructions does not
change when a message is sent or which values the application substitutes.
Saving requires administrator access and a CSRF token scoped to the code.
Validation errors retain the submitted body. The editor synchronizes both
visual edits and HTML code-view edits before the native form submission.

The public FAQ is maintained in `application/view/home/faq.php`. It is not an
editable `static_text` page. This change does not change any customer operation
or the public FAQ content.

## Data and lookup contract

Migration `20260913120000_classify_editable_texts.sql` adds:

- `code`: a nullable `varchar(100)` with ASCII binary collation and a unique
  index; code lookups are case-sensitive;
- `type`: a nullable `varchar(10)`, with supported values `page` and `mail`;
- `tags`: nullable plain-text instructions for placeholders supported by a mail
  template.

Codes are stable integration identifiers such as `page_calendar_infosection`
and `mail_pass_expired`. Application callers use `StaticText::loadByCode()`,
`getTextByCode()` or `getSubstitutedText()` with a string code. Numeric IDs remain
database identities only; management editor URLs also use codes. Numeric IDs
do not select page content or an outgoing message template in application code.
The former voucher lookup by the display name `Email z voucherem` also uses a
code, `mail_gift_voucher`.

`loadByCode()` only selects active records and clears a previous loaded record
before attempting another lookup. Required content retrieval raises an error
when its code is missing or inactive, instead of returning another template or
silently generating an empty message. The editor permits an empty page section
but rejects an empty mail body. Unknown or unclassified records are not offered
as page sections or email templates.

The migration assigns codes, types, descriptions and tag instructions to the
known legacy records. It preserves every body, display name, numeric ID and
activation flag. Numeric IDs 1–21 were the previous application integration
contract. The voucher is matched by its previous name because its generated ID
can differ between environments. Each metadata update runs only while `code`
is `NULL`, so replaying the migration after a partial failure does not overwrite
metadata from an already completed update or later release. Conditional DDL
uses `information_schema` and prepared statements, following the existing
cross-version migration pattern.

## Usage inventory

### Original editable-text audit

The audit covered current working application files and the local database on
2026-09-13. The database contained 22 records: 20 used records, comprising three
page sections and 17 mail templates, and two unused records listed below.
The voucher had local ID 22; that number is not a portable lookup key.
The additive migration was applied locally through the official runner on
MySQL `8.4.10-10`, after creation and checksum verification of its database
backup. No test-next or production database was changed during that initial
local audit.
The manual cleanup was then applied locally with an additional export of the
two retired rows; at that point the local database contained the 20 used records.
Replaying the migration preserved all 22 complete records before cleanup.
An authenticated browser check confirmed saving HTML-mode changes; its test
marker was removed and the original body checksum restored afterwards.

| Legacy ID | Code | Caller | Current use |
| --- | --- | --- | --- |
| 1 | `page_calendar_infosection` | `KalendarzController::sezon()` | Announcement above the filters at `/kalendarz/` and `/kalendarz/sezon/`; closing it is remembered in a browser cookie for 30 days. |
| 2 | `page_group_preparation` | `KalendarzController::pokaz()` | The collapsible preparation section in each group detail page, `/kalendarz/pokaz/{id}`. |
| 21 | `page_shop_content` | `HomeController::sklep_rollschool()` | Body beneath the title at `/home/sklep_rollschool/`. |
| 3 | `mail_pass_expiring_7_days` | `Scheduled_tasksController::notifications_flex_passes()` | Paid Flex pass with remaining entries expires in seven days. |
| 4 | `mail_pass_expiring_3_days` | `Scheduled_tasksController::notifications_flex_passes()` | Paid Flex pass with remaining entries expires in three days. |
| 5 | `mail_pass_expired` | `Scheduled_tasksController::notifications_flex_passes()` | Paid Flex pass expired within the last day with entries remaining. |
| 6 | `mail_outstanding_balance` | `User::buildOutstandingBalanceNotification()` | Account and children balance reminder, used by the scheduled task and the administrator's debt-message form. |
| 8 | `mail_pass_flex_activated` | `Pass::activate()` | Activation of an `om`, `4h` or `8h` pass from payment or administration. |
| 9 | `mail_pass_used_up` | `Pass::usePass()` | The last remaining entry is consumed from a `4h`, `8h` or `4he` pass. |
| 10 | `mail_pass_classic_activated` | `Pass::activate()` | Activation of a Classic (`4he`) pass linked to a group. |
| 12 | `mail_account_confirmation` | `User::requestChannelActivation()` | Account email confirmation, including an administrator's resend. |
| 13 | `mail_occurrence_enrollment` | `Event::EnrollOccurenceUser()` | Single-session enrollment, including an administrator's enrollment or transfer. |
| 14 | `mail_group_enrollment` | `Event::EnrollUser()` | Recurring group enrollment; skipped when notifications are suppressed or the group is a special event. |
| 15 | `mail_occurrence_cancellation` | `Event::cancelOccurenceUser()` | Withdrawal from one session, including administrator transfers; suppressed during full group withdrawal. |
| 16 | `mail_group_cancellation` | `Event::cancelUser()` | Full group withdrawal by the participant, administrator or automatic absence handling. |
| 17 | `mail_unpaid_absence` | `EventOccurence::validateAttendanceList()` | An absent, unpaid participant when attendance is approved, excluding sports-section groups. |
| 18 | `mail_course_enrollment` | `UserCourse::Activate()` | Paid course enrollment activation through payment processing. |
| 19 | `mail_upcoming_class_payment` | `EventOccurence::chargeUsers()` | Reminder for an upcoming session without an assigned payment, unless processing is silent. |
| 20 | `mail_terms_accepted` | `User::acceptTerms()` | Confirmation of a newly accepted terms version, with PDF attached, sent to the account owner or parent once per version. |
| Local 22; legacy name `Email z voucherem` | `mail_gift_voucher` | `GiftVoucher::deliverIssuedVoucher()` | Paid, issued gift voucher delivered to the purchaser with a PDF attachment. |

The enrollment, cancellation, payment and attendance methods are reached from
the current public/account and management routes. Both scheduled Flex and
balance-notification PHP launchers are present in `application/scheduled_tasks/`.
The audit establishes code integration; it does not verify production data,
server cron installation, actual execution frequency or recent deliveries.
It does not remove conditional templates merely because their sending
condition was not observed in the local snapshot.

### Individual-lesson confirmation added after the audit

Migration `20260913180000_add_individual_lesson_confirmation.sql` adds the
following template after the original 22-record audit. After this migration,
the supported inventory comprises three page sections and 18 mail templates,
for a total of 21 used records. The original audit counts above describe the
earlier snapshot and do not include this addition.

| Code | Caller | Current use |
| --- | --- | --- |
| `mail_individual_lesson_scheduled` | `SoloEvent::saveSoloEvent()` | A newly scheduled individual lesson for a participant with an account. The parent receives a child's confirmation. Editing a lesson or scheduling for a guest does not send this message. |

The new lesson and its email queue record are saved in one transaction using
the same PDO connection. Failure to build or queue the required confirmation
rolls back creation for a safe retry; it does not leave a lesson without a queue
record. Existing lessons do not receive retroactive messages. The template is
inserted only if its code is missing, so replaying the migration preserves
administrator edits. The earlier classification migration remains unchanged.
See [Individual lessons](individual-lessons.md#scheduling-confirmation-email)
for recipient, pricing, location and account-link rules and verification.

### Unused legacy entries

| ID | Exact legacy name | Evidence |
| --- | --- | --- |
| 7 | `f_a_q` | The old `HomeController::faq()` read was unused: `application/view/home/faq.php` renders its own maintained HTML and never reads the loaded template variable. The redundant read is removed. |
| 11 | `email: karnet classic 4 wygasł` | No lookup, substitution or indirect consumer in current application code. Classic activation uses ID 10 and exhaustion uses ID 9. |

The additive migration leaves these two records with `NULL` code and type,
which keeps them out of the classified editor. Their permanent removal is a
separate manual operation after deployment, described below. Any additional
unclassified record found on another environment needs its own usage review;
this maintenance step does not delete it.

## Placeholder instructions

The `tags` field is plain-text guidance shown in the mail editor, separately
from the description. Page sections have no tag instructions and no placeholder
substitution. Insert mail tokens exactly as written, including their brackets
and spelling. The existing `occurence` spelling in
`[[nearest_occurence_detail]]` is part of the template contract and is preserved.
Mail subjects, recipient selection, attachments and sending conditions are
managed in code, outside the editable body.

`getSubstitutedText()` replaces the caller's explicit search/replacement pairs.
Gift vouchers retain their explicit replacement list and escaping. The
documented tokens are supported options, not a requirement that every token
appear in the body. Unknown tokens are not automatically interpreted, and
editing the instructions cannot add a new supported token.

| Template code(s) | Supported tokens |
| --- | --- |
| `mail_pass_expiring_7_days`, `mail_pass_expiring_3_days`, `mail_pass_expired`, `mail_pass_flex_activated` | `[[id_pass]]`, `[[valid_to]]` |
| `mail_outstanding_balance` | `[[balance]]`, `[[list_of_users]]` |
| `mail_pass_used_up` | `[[id_pass]]`, `[[list_of_occurrences]]`, `[[user]]` |
| `mail_pass_classic_activated` | `[[id_pass]]`, `[[event_data]]`, `[[begin_date]]` |
| `mail_account_confirmation` | `[[user]]`, `[[link]]` |
| `mail_occurrence_enrollment` | `[[event_detail]]`, `[[user]]`, `[[url_moje_zajecia]]` |
| `mail_group_enrollment` | `[[event_detail]]`, `[[user]]`, `[[nearest_occurence_detail]]`, `[[all_dates]]`, `[[last_date]]`, `[[url_moje_zajecia]]` |
| `mail_occurrence_cancellation`, `mail_group_cancellation` | `[[event_detail]]`, `[[user]]`, `[[timestamp]]` |
| `mail_unpaid_absence`, `mail_upcoming_class_payment` | `[[event_data]]` |
| `mail_course_enrollment` | `[[course_detail]]`, `[[user]]`, `[[begin_date]]`, `[[end_date]]`, `[[url_moje_kursy]]`, `[[url_course]]`, `[[hour_duration]]` |
| `mail_terms_accepted` | `[[user]]` |
| `mail_gift_voucher` | `{voucher_number}`, `{recipient_name}`, `{lesson_type}`, `{hours}`, `{valid_to}`, `{website}` |
| `mail_individual_lesson_scheduled` | `[[user]]`, `[[lesson_type]]`, `[[instructor]]`, `[[location]]`, `[[event_datetime]]`, `[[participant_count]]`, `[[advance_price]]`, `[[later_price]]`, `[[advance_payment_hours]]`, `[[advance_payment_deadline]]`, `[[payment_timing_notice]]`, `[[url_moje_zajecia_indywidualne]]` |

The migration stores Polish explanations for each supported token. Link tokens
already contain complete HTML links and should be inserted as text in the
message body without an additional hyperlink wrapper. Group/session/course
descriptions and occurrence lists may contain HTML and line breaks.
The balance reminder appends its balance/payment and terms links in code.
Voucher placeholders use braces rather than the double square brackets used
by the older mail templates; this change preserves both formats and existing
message bodies.

Individual-lesson price tags contain formatted aggregate amounts without a
currency suffix; the template appends `zł`. Its optional
`[[payment_timing_notice]]` is a complete HTML paragraph with the actual later
price, or an empty string before/at the deadline. It contains no nested tokens.
The location tag never includes the instructor's internal comment. The account
link is a complete HTML link to the adult's lessons or the child's tab and
requires normal account authentication.

## Deployment, verification and cleanup

1. Deploy the selected commit through the existing workflow. The server checks
   out that code first, then `after-deployment-pull.sh` runs the database backup
   and pending migrations before its HTTP verification. Classification runs
   first (`20260913120000_classify_editable_texts.sql`), followed by the new
   individual-lesson template
   (`20260913180000_add_individual_lesson_confirmation.sql`).
   Named lookups require the new columns, so affected requests can fail during
   that interval; this workflow does not provide an atomic code/schema switch.
   On an environment that already has the migration, verify its history entry
   instead of editing the applied file.
2. Wait for the migration and deployment verification to complete before
   checking the classified editor. First validate on test-next, then follow the
   separately authorized production deployment procedure. This implementation
   and local audit do not authorize or constitute a remote deployment.
3. Confirm three page entries and 18 mail entries with nonempty descriptions,
   unique expected codes, empty page tag fields and complete mail tag
   instructions. The two known obsolete entries remain unclassified before
   cleanup. Compare bodies, names and activation flags against the backup;
   assigning metadata must not rewrite them.
4. Open both editor groups and representative page/mail entries. Verify that
   only the body can be saved, mail tag help is separate from the description,
   an empty page can be saved and an empty mail cannot. Verify invalid IDs,
   invalid CSRF and unauthorized updates through the normal error flow.
5. Check the calendar announcement, group preparation section and shop page.
   Render representative mail templates with sample replacement data, including
   all six recurring-enrollment tokens, the seven course tokens, all voucher
   tokens and the 12 individual-lesson tokens. Verify no required token remains
   in the sample output, without
   sending test messages to real recipients. Reusing the model for a missing
   code must not return a previously loaded template.
6. In local verification, rerun the migration and verify it is harmless:
   columns/index already exist, assigned metadata stays unchanged and bodies,
   names and activation flags stay unchanged. Check this before deploying to
   test-next or production.

No new environment setting, server file copy or generated asset is required.
Existing page SEO and public routes are unchanged.

After deployment verification, removal of the two unused entries uses the
separate, tracked file
`database/maintenance/20260913_remove_unused_static_texts.sql`. It is deliberately
outside `database/migrations/`; the automatic runner never executes this
deletion. The requested removal is limited to these two reviewed records.

Before running the maintenance file on each target, create a fresh database
backup (for example, `php scripts/database-migrations.php backup
test-next.rollschool.pl` on the test deployment) and preserve an export of the
two exact rows for a targeted restore. Read the matching records and verify
their identities and empty classification:

```sql
SELECT id, name, code, type
FROM static_text
WHERE id IN (7, 11);
```

Run the maintenance SQL using the target's configured database connection after
the new code is live. It requires both the exact numeric ID and exact binary
name match, plus `NULL` code and type. Expect two affected rows on the audited
snapshot, or zero after an already completed cleanup. Any other result needs
inspection; do not widen the predicates or delete other unclassified records.
Check that neither reviewed row remains and the 20 original templates, the new
individual-lesson confirmation and the FAQ still work.

Before manual cleanup, the additive migration supports rolling back the code:
legacy IDs, bodies and names are still present. After manual cleanup, restore
the two exported rows before rolling back to code that still performs the
redundant FAQ lookup. Do not remove the additive columns or index during a code
rollback. To undo cleanup itself, restore only the two exported rows with their
original IDs and data after checking for ID conflicts.