<?php
/**
 * Migrația inițială — schema completă pentru SQLite.
 * SQLite differences vs MySQL:
 *  - INTEGER PRIMARY KEY AUTOINCREMENT (în loc de AUTO_INCREMENT)
 *  - TEXT cu CHECK în loc de ENUM
 *  - Nu există ON UPDATE CURRENT_TIMESTAMP → folosim TRIGGER
 */
return function (PDO $db): void {

    // ----------- emag_accounts -----------
    $db->exec("
        CREATE TABLE emag_accounts (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            account_name TEXT    NOT NULL,
            username     TEXT    NOT NULL UNIQUE,
            api_key      TEXT    NOT NULL,
            is_active    INTEGER NOT NULL DEFAULT 1,
            last_sync_at TEXT    NULL,
            created_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    ");
    $db->exec("CREATE INDEX idx_accounts_active ON emag_accounts(is_active)");

    $db->exec("
        CREATE TRIGGER trg_emag_accounts_updated
        AFTER UPDATE ON emag_accounts
        FOR EACH ROW
        BEGIN
            UPDATE emag_accounts SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END
    ");

    // ----------- products -----------
    $db->exec("
        CREATE TABLE products (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            emag_account_id  INTEGER NULL REFERENCES emag_accounts(id) ON DELETE SET NULL ON UPDATE CASCADE,
            ean              TEXT    NULL,
            product_name     TEXT    NOT NULL,
            pnk              TEXT    NULL,
            cod_oblio        TEXT    NULL,
            emag_id          TEXT    NULL,
            emag_part_number TEXT    NULL,
            category         TEXT    NULL,
            status           TEXT    NOT NULL DEFAULT 'draft'
                              CHECK(status IN ('draft','active','synced')),
            notes            TEXT    NULL,
            created_at       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    ");
    $db->exec("CREATE INDEX idx_products_ean     ON products(ean)");
    $db->exec("CREATE INDEX idx_products_pnk     ON products(pnk)");
    $db->exec("CREATE INDEX idx_products_emag_id ON products(emag_id)");
    $db->exec("CREATE INDEX idx_products_status  ON products(status)");
    $db->exec("CREATE INDEX idx_products_account ON products(emag_account_id)");

    $db->exec("
        CREATE TRIGGER trg_products_updated
        AFTER UPDATE ON products
        FOR EACH ROW
        BEGIN
            UPDATE products SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END
    ");

    // ----------- sync_logs -----------
    $db->exec("
        CREATE TABLE sync_logs (
            id                INTEGER PRIMARY KEY AUTOINCREMENT,
            emag_account_id   INTEGER NULL REFERENCES emag_accounts(id) ON DELETE SET NULL ON UPDATE CASCADE,
            products_new      INTEGER NOT NULL DEFAULT 0,
            products_updated  INTEGER NOT NULL DEFAULT 0,
            products_errors   INTEGER NOT NULL DEFAULT 0,
            status            TEXT    NOT NULL DEFAULT 'success'
                               CHECK(status IN ('success','partial','failed')),
            message           TEXT    NULL,
            created_at        TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
    ");
    $db->exec("CREATE INDEX idx_synclogs_account_date ON sync_logs(emag_account_id, created_at)");
};
