<?php
/**
 * Ierarhie modele telefon: Brand → Model → Serie.
 *
 * Exemplu:
 *   Apple (brand)
 *     iPhone (model)
 *       13       (serie)
 *       13 Pro   (serie)
 *       14       (serie)
 *   Samsung (brand)
 *     Galaxy S (model)
 *       S24
 *       S25 Ultra
 *
 * FK CASCADE: ștergerea unui brand șterge cascadat modelele și seriile lui.
 * UNIQUE(parent, name): nu poți avea două serii „13 Pro" sub același model.
 */
return function (PDO $db): void {

    $db->exec("
        CREATE TABLE phone_brands (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            name       TEXT    NOT NULL UNIQUE,
            notes      TEXT    NULL,
            created_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    ");
    $db->exec("
        CREATE TRIGGER trg_phone_brands_updated
        AFTER UPDATE ON phone_brands
        FOR EACH ROW
        BEGIN
            UPDATE phone_brands SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END
    ");

    $db->exec("
        CREATE TABLE phone_models (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            brand_id   INTEGER NOT NULL REFERENCES phone_brands(id) ON DELETE CASCADE,
            name       TEXT    NOT NULL,
            notes      TEXT    NULL,
            created_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(brand_id, name)
        )
    ");
    $db->exec("CREATE INDEX idx_phone_models_brand ON phone_models(brand_id)");
    $db->exec("
        CREATE TRIGGER trg_phone_models_updated
        AFTER UPDATE ON phone_models
        FOR EACH ROW
        BEGIN
            UPDATE phone_models SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END
    ");

    $db->exec("
        CREATE TABLE phone_series (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            model_id   INTEGER NOT NULL REFERENCES phone_models(id) ON DELETE CASCADE,
            name       TEXT    NOT NULL,
            notes      TEXT    NULL,
            created_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(model_id, name)
        )
    ");
    $db->exec("CREATE INDEX idx_phone_series_model ON phone_series(model_id)");
    $db->exec("
        CREATE TRIGGER trg_phone_series_updated
        AFTER UPDATE ON phone_series
        FOR EACH ROW
        BEGIN
            UPDATE phone_series SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END
    ");
};
