using Microsoft.Data.Sqlite;
using NewLife.Studio.Core.DTOs;
using NewLife.Log;

namespace NewLife.Studio.Data.Providers.SQLite;

/// <summary>SQLite 元数据读取器</summary>
public class SQLiteMetadataReader
{
    /// <summary>解析 PRAGMA table_info 结果为 ColumnInfo[]</summary>
    public static ColumnInfo[] ParseTableInfo(SqliteDataReader reader)
    {
        var columns = new List<ColumnInfo>();
        while (reader.Read())
        {
            columns.Add(new ColumnInfo
            {
                Ordinal = reader.GetInt32(0),        // cid
                Name = reader.GetString(1),          // name
                DataType = reader.GetString(2),      // type
                IsNullable = !reader.GetBoolean(3),  // notnull
                DefaultValue = reader.IsDBNull(4) ? null : reader.GetString(4), // dflt_value
                IsPrimaryKey = reader.GetBoolean(5)  // pk
            });
        }
        return columns.ToArray();
    }

    /// <summary>解析 PRAGMA index_list 结果</summary>
    public static List<string> ParseIndexList(SqliteConnection connection, string tableName)
    {
        var indexes = new List<string>();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = $"PRAGMA index_list('{tableName}')";
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var name = reader.GetString(1); // name
            indexes.Add(name);
        }
        return indexes;
    }

    /// <summary>解析 PRAGMA foreign_key_list 结果</summary>
    public static List<string> ParseForeignKeyList(SqliteConnection connection, string tableName)
    {
        var fks = new List<string>();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = $"PRAGMA foreign_key_list('{tableName}')";
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var from = reader.GetString(3); // from column
            var table = reader.GetString(2); // table
            var to = reader.GetString(4);    // to column
            fks.Add($"{from} -> {table}({to})");
        }
        return fks;
    }
}