# Learning-language schema update

## Spanish default lists

After importing Spanish verbs and frequencies, import
`2026_11_02_seed_spanish_default_lists.sql` on **taxiboucmydev**. It creates or
refreshes the three official Spanish lists with exactly 25, 25 and 50 verb
memberships, using existing tables and generated IDs. Local/remote copies are
available as `verbs-data/import-spanish-local/sql/{local,remote}/07_spanish_default_lists.sql`.
See the [short HTML guide](../../../../verbs-data/import-spanish-local/Spanish_default_lists.html)
for import steps, rerun behavior and the database changes. The Spanish backend
resolves these saved lists through `language_code`, `official` and `name`.

## Current backend user preference

The architecture files now include `users.verb_language CHAR(2) NOT NULL DEFAULT
'fr'`, used by the French/Spanish backend. Reimport the rerunnable
`2026_10_23_resume_learning_languages.sql`, or its generated copy
`verbs-data/import-spanish-local/sql/remote/01_architecture.sql` for OVH
(`sql/local/01_architecture.sql` for MAMP). No separate preference SQL import is
needed. Existing `verb_language` values are preserved. `users.language` is
untouched. The Laravel migration skips the preference column if SQL already
created it.

## Remove the verb equivalent note column

For a database that already has `verb_equivalents`, import this small file in
phpMyAdmin on **taxiboucmydev**:

`/Users/jo/conjugador/verbs-data/spanish-import/2026_10_24_remove_verb_equivalents_meaning_note.sql`

It drops `verb_equivalents.meaning_note` if present and skips it otherwise.
Existing pairs, IDs, timestamps, indexes and foreign keys remain in place.
Both architecture files now create the bridge without this column. The recovery
file also removes the column from an existing bridge when present.
The old Python bridge preview/export files still require this column. Use the
Laravel command `php artisan verbs:link-equivalents` instead; it supports the
revised schema. See `../../docs/verb-equivalents.md` for instructions.

## Spanish CSV import

After the learning-language migration, import
`/Users/jo/conjugador/verbs-data/spanish-import/2026_10_23_add_spanish_import_support.sql`
in phpMyAdmin on `taxiboucmydev`. This Spanish architecture file now lives
alongside the importer and CSV, rather than in this directory.
Then use the Python importer and setup guide in
`/Users/jo/conjugador/verbs-data/spanish-import/README.md`.
The support SQL adds tense comparison/source metadata tables and nullable
tense mood ownership, and widens English glosses to TEXT without changing
French reference IDs or person rows. It does not import verbs itself.

## Recovering a partial import / error #1060

If phpMyAdmin reports that `language_code` already exists, import
**`2026_10_23_resume_learning_languages.sql`** into **taxiboucmydev** instead of
rerunning the original file. This recovery file checks each column and index
individually and adds only missing objects. It preserves an existing
`verb_equivalents` table and its links. It can be rerun after a partial import of
the original migration. It also removes the deprecated `meaning_note` column;
other existing definitions and values are not overwritten.

Checks of `information_schema` in the recovery file are read-only and restricted
to metadata about `taxiboucmydev`. All schema changes explicitly target
`taxiboucmydev`. The earlier #1044 error on ALTER indicated a wrong target
database, not a reason to grant write access to system metadata.

Existing columns and indexes are skipped by name. The final column report must
show five PASS results, including default `fr`. REVIEW means an existing column
definition differs from the requested one. Inspect the final table/index
definitions as well if these objects were created outside the original script;
the recovery file does not reconcile arbitrary conflicting schemas.

This recovery file was statically reviewed against the original SQL. It has not
been executed against MySQL; no database connection is available locally.
It uses [MySQL prepared statements](https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html)
and [read-only metadata](https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html),
without stored routines or additional administrative privileges.

## Fresh import

Import `2026_10_23_add_learning_languages.sql` once into `taxiboucmydev`
using MySQL 8.0.16 or newer. The script assumes the existing French-only schema
and signed `INT` verb IDs from `tests/Fixtures/production-schema-2026-09-09.sql`.
The file begins with `USE` for the user-confirmed development database
`taxiboucmydev`. For a CLI import, replace the connection arguments:

```sh
mysql -h DEV_HOST -u DEV_USER -p taxiboucmydev < database/manual/2026_10_23_add_learning_languages.sql
```

## phpMyAdmin on OVH / MySQL 8.0

1. Click **taxiboucmydev** in the left sidebar. Confirm its tables
   include `verbs`, `conjugation`, `tense`, and `users`.
2. Open **SQL** and run this separately before importing:

   ```sql
   SELECT DATABASE() AS selected_database, VERSION() AS server_version;
   SHOW TABLES LIKE 'verbs';
   ```

   Confirm the result names your development database and finds `verbs`.
   Do not proceed if the database is NULL or a system schema such as
   `information_schema`.
3. Still inside that database, open **Import**, select the SQL file, and execute.
   If you paste the file into the SQL tab instead, keep the same database selected.

The file already explicitly selects your database before any schema changes:

```sql
USE `taxiboucmydev`;
```

No database-name replacement is needed. Stop on any SQL error, including a
failed `USE` statement.

An error such as `#1044 ... Base 'information_schema'` on the first `ALTER TABLE`
indicates that statement is targeting the wrong database. The failed statement
does not alter your development `verbs` table. Before retrying after any partial
import, inspect the Structure tab for the new columns/indexes and the
`verb_equivalents` table, and run only the missing changes. The verification
queries now use `SHOW FULL COLUMNS` in the selected database instead of directly
querying `information_schema`.

Do not use `--force`. MySQL DDL commits implicitly, so a failed import can leave
some changes applied. Inspect the schema before resuming; the script is not
idempotent and deliberately does not silently skip conflicting objects. It does
not connect to a server itself or change Laravel's migration history.

The update adds:

- `language_code`, default `fr`, on `verbs`, `tense`, `tense_mode`, and `list`.
  Existing rows receive `fr` and retain their IDs.
- `users.verb_language`, default `fr`, as the single learning preference,
  separate from the original interface/translation column `users.language`.
- Language/list/search and verb/tense/person indexes.
- `verb_equivalents`, with signed verb foreign keys, a unique pair, timestamps,
  and a check that the smaller ID comes first. Multiple equivalents per language
  are supported through separate pairs. A link does not imply other links.

No verbs, equivalents, or grammar entries are seeded. Existing text translation
columns remain usable. Conjugations, phrases, and verb-based progress inherit
language through their existing references. Grammatical person slots remain
shared; pronoun labels and order need language-aware application configuration.

## Working with equivalent verbs

These examples are intentionally separate from the migration. Replace the IDs
with real records; 101 and 102 are examples only. On insert, validate different
languages and normalize the pair. This example does both, and inserts zero rows
if either verb is missing, identical, or in the same language. Repeating a pair
raises a duplicate-key error rather than creating a second copy.

```sql
SET @first_verb_id = 101;
SET @second_verb_id = 102;

INSERT INTO `verb_equivalents`
    (`verb_id`, `equivalent_verb_id`, `created_at`, `updated_at`)
SELECT LEAST(a.id, b.id), GREATEST(a.id, b.id), NOW(), NOW()
FROM `verbs` AS a
JOIN `verbs` AS b ON b.id = @second_verb_id
WHERE a.id = @first_verb_id
  AND a.language_code <> b.language_code;
```

Look up directly linked verbs from either side:

```sql
SET @current_verb_id = 101;

SELECT v.id, v.infinitive, v.language_code
FROM `verb_equivalents` AS e
JOIN `verbs` AS v ON v.id = e.equivalent_verb_id
WHERE e.verb_id = @current_verb_id
UNION ALL
SELECT v.id, v.infinitive, v.language_code
FROM `verb_equivalents` AS e
JOIN `verbs` AS v ON v.id = e.verb_id
WHERE e.equivalent_verb_id = @current_verb_id;
```

Remove a verb's equivalent links before deleting that verb. Foreign keys use
the default restrictive behavior, with no cascading actions.

## Application follow-up

Before importing Spanish or German content, update the API and app to filter
by learning language, validate matching languages for list membership and
conjugation/tense/mood references, validate cross-language equivalents on writes,
and keep caches/settings and displayed progress scoped to the learning language.
Missing learning-language selections from older clients should resolve to `fr`.
The database does not enforce these cross-table language relationships.

French tense IDs, auxiliary rules, pronouns, speech voices, and lesson content
also require language-aware handling. This SQL alone does not enable language
switching in the app. In particular, the existing translation service treats a
French interface as English translations; French glosses for new Spanish/German
verbs need a later translation-content change.

## Validation

The file was checked against the checked-in schema fixture and MySQL's documented
DDL rules. It has not been executed on MySQL: the local Docker daemon was
unavailable. The final queries inspect the new columns, defaults, table
constraints, and key indexes after import. MySQL 8.0.16+ is required because older
versions do not enforce the pair-order `CHECK` constraint.

References:

- [MySQL CHECK constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html)
- [MySQL foreign keys](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html)
- [MySQL USE statement](https://dev.mysql.com/doc/refman/8.0/en/use.html)