using System.Text.Json;
using DotNet.Testcontainers.Builders;
using Microsoft.Extensions.Configuration;
using Moq;
using SqlAgent.Service.Enums;
using SqlAgent.Service.Interfaces;
using SqlAgent.Service.Models;
using SqlAgent.Service.Services;
using SqlAgent.Service.Strategies;
using Testcontainers.Oracle;
using Xunit;

namespace SqlAgent.Test.Strategies;

public class OracleFixture : IDbFixture
{
    public OracleContainer Container { get; }
    public string ConnectionString => Container.GetConnectionString();

    public OracleFixture()
    {
        Container = new OracleBuilder("gvenzl/oracle-xe:21.3.0-slim-faststart")
            .WithPassword("TestPass123!")
            .Build();
    }

    public async ValueTask InitializeAsync()
    {
        await Container.StartAsync();

        var strategy = new OracleStrategy(new QueryValueParserService(), new Mock<IConfiguration>().Object);
        using var conn = strategy.CreateConnection(ConnectionString);
        await conn.OpenAsync();
        using var cmd = conn.CreateCommand();
        // Oracle requires specific syntax for table creation
        cmd.CommandText = @"
            CREATE TABLE HS_USERS (
                ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                NAME VARCHAR2(100),
                AGE NUMBER,
                ACTIVE NUMBER(1),
                CREATED_DATE TIMESTAMP
            )
        ";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = "INSERT INTO HS_USERS (NAME, AGE, ACTIVE, CREATED_DATE) VALUES ('Alice', 30, 1, TIMESTAMP '2023-01-01 10:00:00')";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = "INSERT INTO HS_USERS (NAME, AGE, ACTIVE, CREATED_DATE) VALUES ('Bob', 25, 1, TIMESTAMP '2023-02-01 10:00:00')";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = "INSERT INTO HS_USERS (NAME, AGE, ACTIVE, CREATED_DATE) VALUES ('Charlie', 35, 0, TIMESTAMP '2023-03-01 10:00:00')";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = @"
            CREATE TABLE HS_ORDERS (
                ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                USER_ID NUMBER,
                AMOUNT NUMBER(10,2),
                ORDER_DATE DATE
            )
        ";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = "INSERT INTO HS_ORDERS (USER_ID, AMOUNT, ORDER_DATE) VALUES (1, 150.0, DATE '2023-01-10')";
        await cmd.ExecuteNonQueryAsync();
        cmd.CommandText = "INSERT INTO HS_ORDERS (USER_ID, AMOUNT, ORDER_DATE) VALUES (1, 200.0, DATE '2023-02-15')";
        await cmd.ExecuteNonQueryAsync();
        cmd.CommandText = "INSERT INTO HS_ORDERS (USER_ID, AMOUNT, ORDER_DATE) VALUES (2, 50.0, DATE '2023-03-20')";
        await cmd.ExecuteNonQueryAsync();

        cmd.CommandText = "COMMIT";
        await cmd.ExecuteNonQueryAsync();
    }

    public async ValueTask DisposeAsync() => await Container.DisposeAsync();
}


public class OracleStrategyTests(OracleFixture fixture) : BaseStrategyTests<OracleStrategy, OracleFixture>(fixture)
{
    protected override OracleStrategy CreateStrategy(IQueryValueParserService parser, IConfiguration configuration)
        => new(parser, configuration);

    protected override string TestTableName => "HS_USERS";
    protected override string TestOrdersTableName => "HS_ORDERS";
    protected override string TestSchemaName => new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(Fixture.ConnectionString).UserID.ToUpper();
    protected override string TestOrdersUserIdColumn => "USER_ID";

    protected override string TableNotFoundErrorCode => "ORA-00942";
    protected override string ColumnNotFoundErrorCode => "ORA-00904";

    [Fact]
    public override async Task GetColumnsAsync_ShouldReturnColumnTypes()
    {
        await base.GetColumnsAsync_ShouldReturnColumnTypes();
        var columns = await Strategy.GetColumnsAsync(Fixture.ConnectionString, TestSchemaName, TestTableName, TestContext.Current.CancellationToken);
        Assert.Contains(columns, c => c.Column == "ID" && c.Type.Contains("NUMBER", StringComparison.OrdinalIgnoreCase));
        Assert.Contains(columns, c => c.Column == "NAME" && c.Type.Contains("VARCHAR", StringComparison.OrdinalIgnoreCase));
    }

    protected override DmlDefinition CreateInsertDml() => new()
    {
        Operation = DmlOperation.Insert,
        TableName = TestTableName,
        Values = [
            new NameValuePair { FieldName = "NAME", Value = "David" },
            new NameValuePair { FieldName = "AGE", Value = 40 },
            new NameValuePair { FieldName = "ACTIVE", Value = 1 }
        ]
    };

    [Fact]
    public void BuildConnectionString_ShouldGenerateValidOracleFormat()
    {
        var model = new BuildDbConnectionModel
        {
            Provider = "Oracle",
            Host = "localhost",
            Port = "1521",
            Database = "xe",
            Username = "user",
            Password = "pw"
        };
        var connStr = Strategy.BuildConnectionString(model);

        var builder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder(connStr);
        Assert.Contains("localhost:1521/xe", builder.DataSource);
        Assert.Equal("user", builder.UserID);
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportFullQueryDefinitionStructure()
    {
        // Oracle's compiler wraps identifiers in double quotes, so all column refs must be UPPERCASE.
        // We avoid FromQuery wrapping and CTEs due to Oracle's alias syntax quirks.
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                Alias = "U",
                Distinct = true,
                Joins =
                [
                    new JoinCondition
                    {
                        Table = TestOrdersTableName,
                        Alias = "O",
                        Type = JoinType.Left,
                        OnConditions =
                        [
                            new ColumnCompareWhereCondition
                            {
                                LeftFieldName = "U.ID",
                                Operator = "=",
                                RightFieldName = $"O.{TestOrdersUserIdColumn}"
                            }
                        ]
                    }
                ],
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "U.ID", Alias = "user_id" },
                    new FieldSelectCondition { FieldName = "U.NAME", Alias = "username" },
                    new FunctionSelectCondition
                    {
                        FunctionName = "COUNT",
                        Arguments = [new FieldSelectCondition { FieldName = "O.ID" }],
                        Alias = "order_count"
                    },
                    new OperationSelectCondition
                    {
                        Left = new FieldSelectCondition { FieldName = "U.AGE" },
                        Operator = ArithmeticOperator.Add,
                        Right = new ConstantSelectCondition { Constant = 0 },
                        Alias = "age_check"
                    },
                    new ConstantSelectCondition { Constant = "active_user", Alias = "user_type" },
                    new CaseWhenSelectCondition
                    {
                        CaseWhen =
                        [
                            new CaseWhenClause
                            {
                                Condition = new BasicWhereCondition
                                {
                                    FieldName = "U.AGE",
                                    Operator = ">",
                                    Value = 30
                                },
                                Value = "senior"
                            }
                        ],
                        ElseValue = "junior",
                        Alias = "age_group"
                    }
                ],
                WhereColumnsAndValues =
                [
                    new GroupWhereCondition
                    {
                        Groups =
                        [
                            new BasicWhereCondition { FieldName = "O.AMOUNT", Operator = ">", Value = 0 },
                            new BasicWhereCondition { FieldName = "O.ID", Operator = "isnull", Value = null, IsOr = true }
                        ]
                    }
                ],
                GroupByConditions =
                [
                    new FieldGroupByCondition { FieldName = "U.ID" },
                    new FieldGroupByCondition { FieldName = "U.NAME" },
                    new FieldGroupByCondition { FieldName = "U.AGE" }
                ],
                HavingConditions =
                [
                    new FunctionHavingCondition
                    {
                        LeftFunction = new SqlFunctionCondition
                        {
                            FunctionName = "COUNT",
                            Arguments = [new FieldSelectCondition { FieldName = "O.ID" }]
                        },
                        Operator = ">=",
                        Value = 0
                    }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "order_count", Direction = SortDirection.Desc }
                ],
                Limit = 5
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.NotEmpty(rows);
        Assert.True(rows.Count <= 5);

        foreach (var row in rows)
        {
            Assert.True(row.TryGetProperty("user_id", out _));
            Assert.True(row.TryGetProperty("username", out _));
            Assert.True(row.TryGetProperty("order_count", out _));
            Assert.True(row.TryGetProperty("age_check", out _));
            Assert.True(row.TryGetProperty("user_type", out _));
            Assert.True(row.TryGetProperty("age_group", out _));
        }
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportSubQueryWhereCondition()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "NAME", Alias = "uname" }
                ],
                WhereColumnsAndValues =
                [
                    new SubQueryWhereCondition
                    {
                        FieldName = "ID",
                        Operator = "IN",
                        SubQuery = new QueryDefinition
                        {
                            TableName = TestOrdersTableName,
                            SelectColumns =
                            [
                                new FieldSelectCondition { FieldName = TestOrdersUserIdColumn }
                            ],
                            WhereColumnsAndValues =
                            [
                                new BasicWhereCondition { FieldName = "AMOUNT", Operator = ">", Value = 100 }
                            ]
                        }
                    }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "NAME", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.NotEmpty(rows);
        Assert.Single(rows);
        Assert.Equal("Alice", rows[0].GetProperty("uname").GetString());
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportSubQuerySelectCondition()
    {
        // Oracle UPPERCASE aliases for quoted identifier matching
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                Alias = "U",
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "U.NAME", Alias = "uname" },
                    new SubQuerySelectCondition
                    {
                        TableName = TestOrdersTableName,
                        SelectColumns =
                        [
                            new FunctionSelectCondition
                            {
                                FunctionName = "COUNT",
                                Arguments = [new FieldSelectCondition { FieldName = "ID" }]
                            }
                        ],
                        WhereColumnsAndValues =
                        [
                            new ColumnCompareWhereCondition
                            {
                                LeftFieldName = TestOrdersUserIdColumn,
                                Operator = "=",
                                RightFieldName = "U.ID"
                            }
                        ],
                        Alias = "order_count"
                    }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "U.NAME", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.NotEmpty(rows);
        Assert.True(rows[0].TryGetProperty("order_count", out _));
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportWhereNotIsOrIsNot()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "NAME", Alias = "uname" }
                ],
                WhereColumnsAndValues =
                [
                    new BasicWhereCondition { FieldName = "NAME", Operator = "=", Value = "Alice" },
                    new BasicWhereCondition { FieldName = "NAME", Operator = "=", Value = "Bob", IsOr = true },
                    new BasicWhereCondition { FieldName = "NAME", Operator = "LIKE", Value = "C%", IsNot = true }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "NAME", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.NotEmpty(rows);
        Assert.Equal(2, rows.Count);
        Assert.Equal("Alice", rows[0].GetProperty("uname").GetString());
        Assert.Equal("Bob", rows[1].GetProperty("uname").GetString());
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportExistsSubQueryWhereCondition()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "NAME", Alias = "uname" }
                ],
                WhereColumnsAndValues =
                [
                    new SubQueryWhereCondition
                    {
                        Operator = "EXISTS",
                        SubQuery = new QueryDefinition
                        {
                            TableName = TestOrdersTableName,
                            WhereColumnsAndValues =
                            [
                                new ColumnCompareWhereCondition
                                {
                                    LeftFieldName = TestOrdersUserIdColumn,
                                    Operator = "=",
                                    RightFieldName = $"{TestTableName}.ID"
                                }
                            ]
                        }
                    }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "NAME", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.Equal(2, rows.Count);
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportBetweenInHaving()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "NAME", Alias = "uname" },
                    new FieldSelectCondition { FieldName = "AGE", Alias = "age" }
                ],
                WhereColumnsAndValues =
                [
                    new BasicWhereCondition { FieldName = "AGE", Operator = "between", Value = new object[] { 25, 35 } }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "NAME", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.Equal(3, rows.Count);
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportGroupHavingCondition()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestOrdersTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = TestOrdersUserIdColumn, Alias = "uid" },
                    new FunctionSelectCondition
                    {
                        FunctionName = "COUNT",
                        Arguments = [new FieldSelectCondition { FieldName = "ID" }],
                        Alias = "cnt"
                    }
                ],
                GroupByConditions =
                [
                    new FieldGroupByCondition { FieldName = TestOrdersUserIdColumn }
                ],
                HavingConditions =
                [
                    new GroupHavingCondition
                    {
                        Groups =
                        [
                            new FunctionHavingCondition
                            {
                                LeftFunction = new SqlFunctionCondition
                                {
                                    FunctionName = "COUNT",
                                    Arguments = [new FieldSelectCondition { FieldName = "ID" }]
                                },
                                Operator = ">=",
                                Value = 1
                            }
                        ]
                    }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "uid", Direction = SortDirection.Asc }
                ]
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.NotEmpty(rows);
        Assert.Equal(2, rows.Count);
    }

    [Fact]
    public override async Task ExecuteQueryAsync_ShouldSupportOffsetWithoutLimit()
    {
        var json = await Strategy.ExecuteQueryAsync(
            new QueryDefinition
            {
                TableName = TestTableName,
                SelectColumns =
                [
                    new FieldSelectCondition { FieldName = "NAME", Alias = "uname" }
                ],
                OrderByColumns =
                [
                    new FieldOrderByCondition { FieldName = "NAME", Direction = SortDirection.Asc }
                ],
                Offset = 1
            },
            Fixture.ConnectionString,
            cancellationToken: TestContext.Current.CancellationToken);

        var rows = JsonSerializer.Deserialize<List<JsonElement>>(json);
        Assert.NotNull(rows);
        Assert.Equal(2, rows.Count);
        Assert.Equal("Bob", rows[0].GetProperty("uname").GetString());
    }
}
