<?php

/**
 * Developer Console — MySQL → PostgreSQL Type Mapping Reference (DX002A)
 *
 * Static documentation page showing the data type mapping strategy
 * between MySQL and PostgreSQL (Supabase). Used as a reference by DX002B
 * when generating Supabase schema DDL from the MySQL schema.
 *
 * No actions. Read-only reference page.
 *
 * @package Inveetaire
 * @ref     DX002A
 */

// MySQL → PostgreSQL type mapping table
$typeMappings = [
    // Category, MySQL Type, PostgreSQL Type, Notes
    ['Primary Keys',   'BIGINT UNSIGNED AUTO_INCREMENT', 'BIGINT GENERATED ALWAYS AS IDENTITY',  'Use IDENTITY instead of SERIAL for standards compliance'],
    ['Primary Keys',   'INT UNSIGNED AUTO_INCREMENT',    'INTEGER GENERATED ALWAYS AS IDENTITY', 'Same pattern for smaller PKs'],
    ['Primary Keys',   'TINYINT UNSIGNED AUTO_INCREMENT','SMALLINT GENERATED ALWAYS AS IDENTITY','TINYINT maps to SMALLINT in PostgreSQL'],
    ['Foreign Keys',   'BIGINT UNSIGNED',                'BIGINT',                               'Remove UNSIGNED — PostgreSQL does not support unsigned integers'],
    ['Foreign Keys',   'INT UNSIGNED',                   'INTEGER',                              'Same — drop UNSIGNED qualifier'],
    ['Foreign Keys',   'TINYINT UNSIGNED',               'SMALLINT',                             'TINYINT UNSIGNED → SMALLINT'],
    ['Auto Increment', 'AUTO_INCREMENT',                 'GENERATED ALWAYS AS IDENTITY',         'PostgreSQL 10+ IDENTITY replaces SERIAL for new tables'],
    ['Auto Increment', 'SERIAL / BIGSERIAL',             'SERIAL / BIGSERIAL',                   'Legacy; prefer IDENTITY. Still valid.'],
    ['Boolean',        'TINYINT(1)',                     'BOOLEAN',                              'MySQL uses TINYINT(1) for booleans. PostgreSQL has native BOOLEAN.'],
    ['Boolean',        'BIT(1)',                         'BOOLEAN',                              'BIT(1) → BOOLEAN'],
    ['DateTime',       'DATETIME',                       'TIMESTAMP WITHOUT TIME ZONE',          'INVEETAIRE schema uses DATETIME — maps directly. No TZ offset stored.'],
    ['DateTime',       'DATE',                           'DATE',                                 'Direct mapping — no change needed'],
    ['DateTime',       'TIME',                           'TIME WITHOUT TIME ZONE',               'TIME maps to TIME WITHOUT TIME ZONE'],
    ['Timestamp',      'TIMESTAMP',                      'TIMESTAMP WITH TIME ZONE',             'MySQL TIMESTAMP is always UTC-stored. PostgreSQL TIMESTAMPTZ is explicit.'],
    ['Timestamp',      'TIMESTAMP DEFAULT CURRENT_TIMESTAMP', 'TIMESTAMPTZ DEFAULT NOW()',       'Use NOW() instead of CURRENT_TIMESTAMP in PostgreSQL'],
    ['JSON',           'JSON',                           'JSONB',                                'Use JSONB (binary JSON) for better indexing and query performance'],
    ['JSON',           'LONGBLOB (JSON workaround)',     'JSONB',                               'Migrate any binary JSON workarounds to native JSONB'],
    ['Text',           'VARCHAR(n)',                     'VARCHAR(n)',                            'Direct mapping — no change needed'],
    ['Text',           'VARCHAR(191)',                   'VARCHAR(191)',                          'Same. Note: MySQL uses this for utf8mb4 index limits.'],
    ['Text',           'TEXT',                           'TEXT',                                 'Direct mapping — no size restriction in PostgreSQL TEXT'],
    ['Text',           'MEDIUMTEXT',                     'TEXT',                                 'PostgreSQL TEXT is unlimited — MEDIUMTEXT and LONGTEXT both map here'],
    ['Text',           'LONGTEXT',                       'TEXT',                                 'Direct mapping — TEXT in PostgreSQL is already unlimited size'],
    ['Text',           'CHAR(n)',                        'CHAR(n)',                               'Direct mapping'],
    ['Enum',           'ENUM(\'a\',\'b\',...)',          'TEXT with CHECK constraint',            'Or create a PostgreSQL ENUM type. CHECK is simpler for migrations.'],
    ['Enum',           'SET(\'a\',\'b\',...)',           'TEXT[] (array)',                        'MySQL SET maps to PostgreSQL text arrays'],
    ['Numeric',        'DECIMAL(p, s)',                  'NUMERIC(p, s)',                        'DECIMAL and NUMERIC are interchangeable in PostgreSQL'],
    ['Numeric',        'FLOAT',                          'REAL',                                 'FLOAT(M) maps to REAL (single precision) in PostgreSQL'],
    ['Numeric',        'DOUBLE',                         'DOUBLE PRECISION',                     'DOUBLE → DOUBLE PRECISION'],
    ['Numeric',        'SMALLINT',                       'SMALLINT',                             'Direct mapping'],
    ['Numeric',        'INT / INTEGER',                  'INTEGER',                              'Direct mapping'],
    ['Numeric',        'BIGINT',                         'BIGINT',                               'Direct mapping'],
    ['Numeric',        'TINYINT',                        'SMALLINT',                             'TINYINT (8-bit) → SMALLINT (16-bit) — closest PostgreSQL equivalent'],
    ['Numeric',        'MEDIUMINT',                      'INTEGER',                              'MEDIUMINT (24-bit) → INTEGER (32-bit)'],
    ['Binary',         'BLOB',                           'BYTEA',                                'Binary data → BYTEA in PostgreSQL'],
    ['Binary',         'MEDIUMBLOB / LONGBLOB',          'BYTEA',                                'PostgreSQL BYTEA is unlimited'],
    ['Other',          'YEAR',                           'SMALLINT',                             'YEAR(4) stores 4-digit year — use SMALLINT or INTEGER in PostgreSQL'],
    ['Other',          'NULL / NOT NULL',                'NULL / NOT NULL',                      'Same semantics in both databases'],
    ['Other',          'DEFAULT value',                  'DEFAULT value',                        'Same syntax — verify function-based defaults (NOW(), etc.)'],
];

// Group by category
$grouped = [];
foreach ($typeMappings as $row) {
    $grouped[$row[0]][] = $row;
}

?>

<!-- Page Header -->
<div style="margin-bottom: 2rem;">
    <div style="font-family: var(--mono); font-size: 0.6875rem; color: var(--dev-text-muted); margin-bottom: 0.5rem;">
        <a href="<?= url('/app/developer') ?>" style="color:var(--dev-accent); text-decoration:none;">developer</a>
        <span style="color:#444c56;"> / </span>
        <span>mapping</span>
    </div>
    <h1 style="font-size: 1.5rem; font-weight: 800; color: var(--dev-text); margin-bottom: 0.25rem;">
        MySQL → PostgreSQL Type Mapping
    </h1>
    <p style="font-size: 0.8125rem; color: var(--dev-text-muted);">
        Data type translation reference for Supabase schema generation.
        Used by <span style="color:var(--dev-purple);">DX002B</span> when generating Supabase DDL from the MySQL schema.
    </p>
</div>

<!-- Strategy Overview -->
<div class="dev-card" style="margin-bottom: 2rem; border-color: rgba(212,162,140,0.3);">
    <div class="dev-card-header">
        <span class="dev-card-title">
            <i class="fa-solid fa-diagram-project" style="color:var(--dev-rose); margin-right:0.375rem;"></i>
            Database Mapping Strategy
        </span>
        <span style="font-family:var(--mono); font-size:0.6rem; font-weight:700; padding:0.2rem 0.5rem; border-radius:4px; background:rgba(212,162,140,0.1); border:1px solid rgba(212,162,140,0.3); color:var(--dev-rose);">DX002A Reference</span>
    </div>
    <div class="dev-card-body" style="display: grid; grid-template-columns: repeat(auto-fill, minmax(220px, 1fr)); gap: 1rem;">
        <?php
        $rules = [
            ['Source of Truth',   'MySQL (InnoDB)',           'fa-database',       'var(--dev-accent)'],
            ['Mirror Target',     'Supabase PostgreSQL',      'fa-cloud',          '#3ecf8e'],
            ['App Queries',       'MySQL only — always',      'fa-bolt',           'var(--dev-warn)'],
            ['Mirror Use Case',   'Recovery, Dev, AI',        'fa-shield-halved',  'var(--dev-purple)'],
            ['Sync Direction',    'MySQL → Supabase (DX002B)','fa-arrow-right',    'var(--dev-rose)'],
            ['Sync Frequency',    'Manual (DX002B)',          'fa-clock',          'var(--dev-text-muted)'],
        ];
        foreach ($rules as [$label, $value, $icon, $color]):
        ?>
            <div style="display:flex; align-items:flex-start; gap:0.75rem;">
                <div style="font-size:1rem; color:<?= $color ?>; flex-shrink:0; margin-top:0.125rem;"><i class="fa-solid <?= $icon ?>"></i></div>
                <div>
                    <div style="font-family:var(--mono); font-size:0.5625rem; font-weight:700; text-transform:uppercase; letter-spacing:0.08em; color:var(--dev-text-muted); margin-bottom:0.125rem;"><?= escape_html($label) ?></div>
                    <div style="font-size:0.8125rem; color:var(--dev-text);"><?= escape_html($value) ?></div>
                </div>
            </div>
        <?php endforeach; ?>
    </div>
</div>

<!-- Type Mapping Tables (by category) -->
<?php foreach ($grouped as $category => $rows): ?>
<div class="dev-section-title" style="margin-bottom: 0.75rem;"><?= escape_html($category) ?></div>
<div class="dev-card" style="margin-bottom: 1.5rem;">
    <div style="overflow-x:auto;">
        <table class="dev-table">
            <thead>
                <tr>
                    <th style="width:30%;">MySQL Type</th>
                    <th style="width:30%;">PostgreSQL Type</th>
                    <th>Migration Notes</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($rows as $row): ?>
                <tr>
                    <td class="mono" style="color:var(--dev-accent); font-size:0.6875rem;"><?= escape_html($row[1]) ?></td>
                    <td class="mono" style="color:#3ecf8e; font-size:0.6875rem;"><?= escape_html($row[2]) ?></td>
                    <td style="font-size:0.75rem; color:var(--dev-text-muted);"><?= escape_html($row[3]) ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>
<?php endforeach; ?>

<!-- Key Differences Summary -->
<div class="dev-section-title" style="margin-bottom: 0.75rem;">Key Differences to Watch</div>
<div class="dev-card" style="margin-bottom: 2rem;">
    <div class="dev-card-body" style="display:grid; grid-template-columns: 1fr 1fr; gap:1rem;">
        <?php
        $warnings = [
            ['UNSIGNED integers',       'MySQL uses UNSIGNED to prevent negatives. PostgreSQL has no UNSIGNED — remove on migration. Foreign keys that reference UNSIGNED PKs need matching types.'],
            ['TINYINT(1) booleans',      'MySQL uses TINYINT(1) for boolean. PostgreSQL has a native BOOLEAN type. Always migrate TINYINT(1) to BOOLEAN.'],
            ['AUTO_INCREMENT',           'Replace with GENERATED ALWAYS AS IDENTITY or SERIAL. Identity columns are the modern standard in PostgreSQL 10+.'],
            ['ENUM types',               'MySQL ENUM is stored as integers internally. PostgreSQL supports native ENUM but CHECK constraints on TEXT are easier to ALTER later.'],
            ['JSON vs JSONB',            'Always use JSONB in PostgreSQL, not JSON. JSONB is binary-indexed and supports GIN indexes for fast JSON queries.'],
            ['DATETIME vs TIMESTAMP',    'MySQL DATETIME has no timezone; PostgreSQL TIMESTAMP WITHOUT TIME ZONE matches. Use TIMESTAMPTZ (WITH TIME ZONE) for audit columns.'],
            ['utf8mb4 vs UTF-8',         'MySQL utf8mb4 is true UTF-8. PostgreSQL uses UTF8 natively. No conversion needed — just update the charset declaration.'],
            ['Information Schema',       'Both databases have information_schema but column names differ. Queries must be rewritten for each DB in SchemaReaderService.'],
        ];
        foreach ($warnings as [$title, $desc]):
        ?>
            <div style="padding:0.875rem; background:var(--dev-surface2); border:1px solid var(--dev-border2); border-radius:6px;">
                <div style="font-family:var(--mono); font-size:0.6875rem; font-weight:700; color:var(--dev-warn); margin-bottom:0.375rem;">
                    <i class="fa-solid fa-triangle-exclamation" style="margin-right:0.25rem;"></i>
                    <?= escape_html($title) ?>
                </div>
                <div style="font-size:0.75rem; color:var(--dev-text-muted); line-height:1.5;"><?= escape_html($desc) ?></div>
            </div>
        <?php endforeach; ?>
    </div>
</div>

<!-- INVEETAIRE-Specific Notes -->
<div class="dev-section-title" style="margin-bottom: 0.75rem;">INVEETAIRE Schema-Specific Notes</div>
<div class="dev-card">
    <div class="dev-card-body">
        <p style="font-size:0.8125rem; color:var(--dev-text-muted); line-height:1.6; margin-bottom:1rem;">
            The following patterns are specific to the INVEETAIRE MySQL schema and require special handling during DX002B migration:
        </p>
        <div style="display:grid; grid-template-columns: repeat(auto-fill, minmax(280px, 1fr)); gap:0.75rem;">
            <?php
            $inveetaireNotes = [
                ['Soft Deletes',     'is_deleted TINYINT(1) + deleted_at DATETIME',  'Migrate to BOOLEAN + TIMESTAMP WITHOUT TIME ZONE'],
                ['Status Enums',     'VARCHAR(20) status columns (e.g. workspace_status)', 'Keep as VARCHAR(20) or migrate to PostgreSQL ENUM'],
                ['JSON Config',      'section_config JSON, angpao_label_fields JSON', 'Migrate to JSONB for indexability'],
                ['Workspace ID',     'BIGINT UNSIGNED workspace_id on all tables',    'Drop UNSIGNED — use BIGINT FK references'],
                ['Circular FKs',     'workspaces ↔ users circular reference',         'Use DEFERRABLE INITIALLY DEFERRED constraints in PostgreSQL'],
                ['Audit Soft-refs',  'audit_logs uses no FK constraints (soft refs)', 'No FK migration needed — keep as plain BIGINT columns'],
            ];
            foreach ($inveetaireNotes as [$label, $mysql, $pg]):
            ?>
                <div style="padding:0.875rem; background:var(--dev-surface2); border:1px solid var(--dev-border2); border-radius:6px;">
                    <div style="font-family:var(--mono); font-size:0.6875rem; font-weight:700; color:var(--dev-accent); margin-bottom:0.5rem;"><?= escape_html($label) ?></div>
                    <div style="font-size:0.6875rem; color:var(--dev-text-muted); margin-bottom:0.25rem;"><span style="color:var(--dev-accent);">MySQL:</span> <?= escape_html($mysql) ?></div>
                    <div style="font-size:0.6875rem; color:var(--dev-text-muted);"><span style="color:#3ecf8e;">PG:</span> <?= escape_html($pg) ?></div>
                </div>
            <?php endforeach; ?>
        </div>
    </div>
</div>
