using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace CareFix.Core;

public sealed record GuardResult(bool Ok, string? Reason, IReadOnlyList<string> Tables);

/// <summary>
/// Parses T-SQL with Microsoft's own parser and accepts exactly one plain SELECT on known dbo tables.
/// The read-only SQL login is the second line of defence; this is the first.
/// </summary>
public sealed class SqlGuard
{
    public GuardResult ValidateSelect(string sql, ISet<string>? knownTables)
    {
        if (string.IsNullOrWhiteSpace(sql)) return Fail("The query is empty.");

        var parser = new TSql160Parser(true);
        using var reader = new StringReader(sql);
        var fragment = parser.Parse(reader, out IList<ParseError> errors);
        if (errors.Count > 0) return Fail($"SQL syntax error at line {errors[0].Line}: {errors[0].Message}");

        if (fragment is not TSqlScript script || script.Batches.Count != 1 || script.Batches[0].Statements.Count != 1)
            return Fail("Send exactly one SELECT statement.");
        if (script.Batches[0].Statements[0] is not SelectStatement select)
            return Fail("Only SELECT statements are allowed.");
        if (select.Into is not null)
            return Fail("SELECT INTO is not allowed.");

        var v = new GuardVisitor();
        fragment.Accept(v);
        if (v.Violation is not null) return Fail(v.Violation);

        var tables = v.Tables.Where(t => !v.CteNames.Contains(t)).Distinct(StringComparer.OrdinalIgnoreCase).ToList();
        if (knownTables is not null)
        {
            var unknown = tables.Where(t => !knownTables.Contains(t)).ToList();
            if (unknown.Count > 0) return Fail("Unknown table(s) for this hospital: " + string.Join(", ", unknown) + ". Use search_schema to find the right names.");
        }
        return new GuardResult(true, null, tables);
    }

    private static GuardResult Fail(string reason) => new(false, reason, []);

    private sealed class GuardVisitor : TSqlFragmentVisitor
    {
        public string? Violation;
        public readonly List<string> Tables = [];
        public readonly HashSet<string> CteNames = new(StringComparer.OrdinalIgnoreCase);

        public override void Visit(CommonTableExpression node) => CteNames.Add(node.ExpressionName.Value);

        public override void Visit(NamedTableReference node)
        {
            var so = node.SchemaObject;
            if (so.ServerIdentifier is not null || so.DatabaseIdentifier is not null)
                Violation ??= "Cross-database and linked-server references are not allowed.";
            var schema = so.SchemaIdentifier?.Value ?? "dbo";
            if (!schema.Equals("dbo", StringComparison.OrdinalIgnoreCase))
                Violation ??= $"Only dbo tables can be queried (found {schema}.{so.BaseIdentifier.Value}).";
            Tables.Add(so.BaseIdentifier.Value);
        }

        public override void Visit(OpenRowsetTableReference node) => Violation ??= "OPENROWSET is not allowed.";
        public override void Visit(OpenQueryTableReference node) => Violation ??= "OPENQUERY is not allowed.";
        public override void Visit(AdHocTableReference node) => Violation ??= "OPENDATASOURCE is not allowed.";
        public override void Visit(OpenXmlTableReference node) => Violation ??= "OPENXML is not allowed.";
        public override void Visit(SchemaObjectFunctionTableReference node) => Violation ??= "Table-valued functions are not allowed.";
    }
}