# ============================================================
# Probe LECTURE SEULE — Audit physique de OMIKA_TEST
# Aucune écriture. Aucune modification de configuration.
# Connexion OLE DB PCSOFT.HFSQL (ADODB COM) sur le même serveur
# que BATIK_TEST mais catalogue/base = OMIKA_TEST.
# ============================================================
$ErrorActionPreference = "Stop"

$server = "127.0.0.1"
$port = 4900
$database = "OMIKA_TEST"
$user = "susana"
$password = "123"

# --- Schéma Omika attendu (source : docs/schema-compatibilite-batik-omika.json,
#     presence = "Commun" ou "Omika uniquement") ---
$expectedOmika = @(
    "Doc_Aide","Document","DocumentNote","MailCat","MailModele","MessageExterne",
    "MessageInterne","ModeleRapport","ModeleRapportMob","MotClef","RubriquesDocument",
    "Action","CheckList","CheckList_Collab","CompteRendu","Dossier","EtatIntervention",
    "Intervention","Intervention_ChampLibre","Intervention_Personne","BddExterne",
    "Client_BddExterne","ElementModifie","ElementSupprime","Intervention_BddExterne",
    "Mobile","MobLog","MobReception","Collaborateur","Equipe","Equipe_Collaborateur",
    "Societe","Societe_Collaborateur","Compteur","Etats","ParametreApplication",
    "ParametreMetier","ParametreMetierEntete","ParametreSysteme","ParametreUtilisateur",
    "Planning_RDV","Planning_RDV_Personne","RDV_Collaborateur","RDV_Contact","RDV_Planning",
    "SuiviDesTemps","Adresse","Client","Client_Adresse","Client_Lieu","Lieu","Lieu_Personne",
    "Personne","Telephone","AccesEtDroit","CategorieAcces","Role","Utilisateur"
)

# --- Tables importantes pour le snapshot (noms physiques Omika) ---
$importantTables = @(
    "Client",            # Clients
    "Collaborateur",     # Collaborateurs
    "Personne",          # Personnes / Contacts
    "Lieu",              # Sites / Lieux
    "Dossier",           # Dossiers
    "Intervention",      # Interventions
    "RDV_Planning",      # RendezVous
    "SuiviDesTemps",     # SuiviTemps
    "Document",          # Documents
    "ParametreMetier"    # ParametreMetier
)

Write-Host "========== AUDIT OMIKA_TEST (READ-ONLY) =========="
Write-Host "Base     : $database"
Write-Host "Serveur  : ${server}:${port}"
Write-Host ""

$connStr = "Provider=PCSoft.HFSQL;Data Source=${server};Port=${port};Initial Catalog=${database};User ID=${user};Password=${password};"

$cn = $null
try {
    $cn = New-Object -ComObject ADODB.Connection
    $cn.ConnectionTimeout = 15
    $cn.Open($connStr)
    Write-Host "CONNEXION : OUVERTE"
    Write-Host ""

    # ---- 1. Lister les tables physiques via OpenSchema (adSchemaTables = 20) ----
    $tables = New-Object System.Collections.Generic.List[string]
    try {
        $rs = $cn.OpenSchema(20)   # adSchemaTables
        while (-not $rs.EOF) {
            $name = $rs.Fields.Item("TABLE_NAME").Value
            if ($name) { $tables.Add([string]$name) }
            $rs.MoveNext()
        }
        $rs.Close()
    } catch {
        Write-Host "  (OpenSchema Tables indisponible: $($_.Exception.Message))"
    }

    $actualSet = New-Object 'System.Collections.Generic.HashSet[string]' ([System.StringComparer]::OrdinalIgnoreCase)
    foreach ($t in $tables) { [void]$actualSet.Add($t) }

    Write-Host "NOMBRE TABLES OMIKA_TEST REELLES : $($actualSet.Count)"
    Write-Host "NOMBRE TABLES OMIKA ATTENDUES    : $($expectedOmika.Count)"
    Write-Host ""

    # ---- 2. Tables manquantes (EXPECTED - ACTUAL) ----
    $missing = @()
    foreach ($e in $expectedOmika) {
        if (-not $actualSet.Contains($e)) { $missing += $e }
    }
    Write-Host "--- TABLES MANQUANTES (EXPECTED_OMIKA - ACTUAL_OMIKA) ---"
    if ($missing.Count -eq 0) { Write-Host "  (aucune)" } else { $missing | ForEach-Object { Write-Host "  - $_" } }
    Write-Host ""

    # ---- 3. Tables supplémentaires (ACTUAL - EXPECTED) ----
    $extra = @()
    foreach ($a in $actualSet) {
        if ($expectedOmika -notcontains $a) { $extra += $a }
    }
    Write-Host "--- TABLES SUPPLEMENTAIRES (ACTUAL_OMIKA - EXPECTED_OMIKA) ---"
    if ($extra.Count -eq 0) { Write-Host "  (aucune)" } else { $extra | Sort-Object | ForEach-Object { Write-Host "  - $_" } }
    Write-Host ""

    # ---- 4. Détection de schéma ----
    Write-Host "--- DETECTION DE SCHEMA ---"
    $criticalOmika = @("Client","Collaborateur","Personne","Lieu","Dossier","Intervention","RDV_Planning","SuiviDesTemps","Document","ParametreMetier")
    $criticalMissing = ($criticalOmika | Where-Object { -not $actualSet.Contains($_) }).Count
    Write-Host "  Tables critiques Omika manquantes : $criticalMissing / $($criticalOmika.Count)"
    if ($missing.Count -eq 0 -and $criticalMissing -eq 0) {
        Write-Host "  SCHEMA DETECTE : OMIKA (complet)"
    } elseif ($criticalMissing -gt 0) {
        Write-Host "  SCHEMA DETECTE : OMIKA_INCOMPLET"
    } else {
        Write-Host "  SCHEMA DETECTE : OMIKA (avec écarts non critiques)"
    }
    Write-Host ""

    # ---- 5. Comptage des tables importantes ----
    Write-Host "--- COUNTS TABLES IMPORTANTES (SELECT COUNT(*)) ---"
    foreach ($table in $importantTables) {
        if ($actualSet.Contains($table)) {
            try {
                $rs2 = New-Object -ComObject ADODB.Recordset
                $rs2.Open("SELECT COUNT(*) AS C FROM $table", $cn, 1, 1)
                $count = $rs2.Fields.Item("C").Value
                $rs2.Close()
                Write-Host ("  {0,-20} : PRESENT  count={1}" -f $table, $count)
            } catch {
                Write-Host ("  {0,-20} : PRESENT  count=ERREUR ({1})" -f $table, $_.Exception.Message)
            }
        } else {
            Write-Host ("  {0,-20} : ABSENT" -f $table)
        }
    }
    Write-Host ""

    # ---- 6. ParametreMetier : TypeTemps ----
    # Test direct SELECT même si la table n'apparaît pas dans le listing
    # OpenSchema : distingue "table absente" de "table protégée (FLS)".
    Write-Host "--- PARAMETREMETIER (TypeTemps) ---"
    $pmDirect = $null
    try {
        $rs3 = New-Object -ComObject ADODB.Recordset
        $rs3.Open("SELECT COUNT(*) AS C FROM ParametreMetier", $cn, 1, 1)
        $pmDirect = $rs3.Fields.Item("C").Value
        $rs3.Close()
        Write-Host "  ParametreMetier : PRESENT (SELECT direct)  count=$pmDirect"
        try {
            $rs4 = New-Object -ComObject ADODB.Recordset
            $rs4.Open("SELECT COUNT(*) AS C FROM ParametreMetier WHERE PA_Categorie = 'TypeTemps'", $cn, 1, 1)
            $ttCount = $rs4.Fields.Item("C").Value
            $rs4.Close()
            Write-Host "  TypeTemps (PA_Categorie='TypeTemps') : count=$ttCount"
        } catch {
            Write-Host "  TypeTemps : non déterminé (colonne PA_Categorie absente ou autre)"
        }
    } catch {
        $err = $_.Exception.Message
        if ($err -match "introuvable|not found|inexistante|does not exist|28000001") {
            Write-Host "  ParametreMetier : ABSENT (table introuvable)"
        } else {
            Write-Host "  ParametreMetier : PRESENT mais PROTEGE (SELECT direct échoue: $err)"
        }
    }


    $cn.Close()
    Write-Host ""
    Write-Host "RESULTAT : OK (lecture seule)"
    exit 0
}
catch {
    Write-Host "CONNEXION : ECHEC"
    Write-Host "Erreur    : $($_.Exception.Message)"
    Write-Host "RESULTAT  : KO"
    exit 3
}