namespace Omika.Diagnostics.Tests;

/// <summary>
/// Contrat du schéma PostgreSQL (§15-§17, §28) : vérifie que le fichier SQL déclare
/// les tables, la clé unique <c>event_id</c> (déduplication) et la table des métriques.
/// </summary>
public class DiagnosticSchemaContractTests
{
    [Fact]
    public void Schema_DeclaresEventTable_WithUniqueEventId()
    {
        string sql = ReadSchema();

        Assert.Contains("CREATE TABLE IF NOT EXISTS diagnostic_event", sql);
        Assert.Contains("event_id", sql);
        Assert.Contains("UNIQUE", sql);
        Assert.Contains("ux_diagnostic_event_event_id", sql); // ON CONFLICT (event_id) DO NOTHING
    }

    [Fact]
    public void Schema_DeclaresMetricTable_AndRequiredIndexes()
    {
        string sql = ReadSchema();

        Assert.Contains("CREATE TABLE IF NOT EXISTS telemetry_metric_hourly", sql);
        Assert.Contains("metric_name", sql);
        Assert.Contains("ux_telemetry_metric_hourly", sql);
    }

    [Fact]
    public void ActorKeyMigration_IsIdempotent_AndNonDestructive()
    {
        string sql = ReadMigration("002_actor_key.sql");

        Assert.Contains("ALTER TABLE diagnostic_event", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS actor_key", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS actor_key_version", sql);
        Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_actor_key_timestamp", sql);
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void ClientContextMigration_IsIdempotent_AndDeclaresNullableColumns()
    {
        string sql = ReadMigration("005_diagnostics_client_context.sql");

        Assert.Contains("ALTER TABLE diagnostic_event", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS client_code", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS server_instance", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS environment", sql);
        Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_client_code_timestamp", sql);
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("UPDATE", sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void DeviceLicenseMigration_IsIdempotent_AndDeclaresNullableColumns()
    {
        string sql = ReadMigration("006_diagnostics_device_license.sql");

        Assert.Contains("ALTER TABLE diagnostic_event", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS license_number", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS device_key", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS device_key_version", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS device_manufacturer", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS device_model", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS operating_system", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS operating_system_version", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS app_build", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS screen_width", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS screen_height", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS logical_width", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS logical_height", sql);
        Assert.Contains("ADD COLUMN IF NOT EXISTS display_scale", sql);
        Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_device_key_timestamp", sql);
        Assert.Contains("CREATE INDEX IF NOT EXISTS ix_diagnostic_event_license_number_timestamp", sql);
        // Types : dimensions écran entières, display_scale réel.
        Assert.Contains("screen_width          INTEGER", sql);
        Assert.Contains("display_scale         DOUBLE PRECISION", sql);
        // Non destructif : aucune suppression/réécriture.
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("UPDATE", sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void TasksMigration_IsIdempotent_AndNonDestructive()
    {
        string sql = ReadMigration("012_diagnostics_tasks.sql");

        Assert.Contains("CREATE TABLE IF NOT EXISTS diagnostics_tasks", sql);
        Assert.Contains("group_url_identifier", sql);
        Assert.Contains("ux_diagnostics_tasks_group_url_identifier", sql);
        Assert.Contains("GRANT SELECT ON TABLE diagnostics_tasks TO omika_diagnostics_web", sql);
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP COLUMN", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP INDEX", sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DELETE FROM", sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void RetentionUntreatedMigration_IsIdempotent_AndColumnScoped()
    {
        string sql = ReadMigration("015_diagnostics_retention_untreated.sql");

        // Moindre privilège : SELECT UNIQUEMENT sur group_url_identifier + status (le
        // rapprochement « traité/non traité »), jamais sur title/description/cause ni
        // les usernames. Aucun droit d'écriture accordé au worker.
        Assert.Contains("GRANT SELECT (group_url_identifier, status)", sql);
        Assert.Contains("ON TABLE diagnostics_tasks TO omika_diagnostics_worker", sql);
        Assert.DoesNotContain("GRANT INSERT", sql);
        Assert.DoesNotContain("GRANT UPDATE", sql);
        Assert.DoesNotContain("GRANT DELETE", sql);
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void EventDeleteMigration_GrantsDeleteToWebOnly_AndIsNonDestructive()
    {
        string sql = ReadMigration("016_diagnostics_event_delete.sql");

        // Moindre privilège : DELETE sur diagnostic_event accordé au SEUL rôle Web
        // (suppression d'un groupe d'erreurs depuis /Erreurs). Aucun GRANT accordé au
        // Worker ni à l'API (le Worker a déjà son propre périmètre via la migration 004).
        Assert.Contains("GRANT DELETE ON TABLE diagnostic_event TO omika_diagnostics_web", sql);
        Assert.DoesNotContain("TO omika_diagnostics_worker", sql);
        Assert.DoesNotContain("TO omika_diagnostics_api", sql);
        Assert.DoesNotContain("GRANT INSERT", sql);
        Assert.DoesNotContain("GRANT UPDATE", sql);
        Assert.DoesNotContain("DROP TABLE", sql, StringComparison.OrdinalIgnoreCase);
    }

    private static string ReadSchema()
    {
        string root = FindRepoRoot();
        return File.ReadAllText(Path.Combine(root, "infra", "postgresql", "001_diagnostics_schema.sql"));
    }

    private static string ReadMigration(string fileName)
    {
        string root = FindRepoRoot();
        return File.ReadAllText(Path.Combine(root, "infra", "postgresql", fileName));
    }

    private static string FindRepoRoot()
    {
        DirectoryInfo? dir = new(AppContext.BaseDirectory);
        while (dir is not null)
        {
            if (File.Exists(Path.Combine(dir.FullName, "Omika.slnx")))
            {
                return dir.FullName;
            }

            dir = dir.Parent;
        }

        throw new InvalidOperationException("Racine du dépôt introuvable (Omika.slnx absent).");
    }
}