using Microsoft.Data.Sqlite;
using ytLive.Models;

namespace ytLive.Services;

public partial class LayoutStore : IDisposable
{
    public List<Scene> Load()
    {
        Webcam = null;
        Socials = null;
        Music = null;
        var scenes = new List<Scene>();
        var sourcesByScene = new Dictionary<string, List<(Source Source, int Sort)>>();
        var configsByScene = new Dictionary<string, List<(WebcamSceneConfig Config, int Sort)>>();

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, Name, IsHidden, IsChatScene, HasBackground, HasSocialBar FROM Scene ORDER BY SortOrder";
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                scenes.Add(new Scene
                {
                    Id = reader.GetString(0),
                    Name = reader.GetString(1),
                    IsHidden = reader.GetInt32(2) != 0,
                    IsChatScene = reader.GetInt32(3) != 0,
                    HasBackground = reader.GetInt32(4) != 0,
                    HasSocialBar = reader.FieldCount > 5 && !reader.IsDBNull(5) && reader.GetInt32(5) != 0,
                });
            }
        }

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, DeviceId, Name FROM Webcam;";
            using var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Webcam = new Webcam
                {
                    Id = reader.GetString(0),
                    DeviceId = reader.GetString(1),
                    Name = reader.GetString(2),
                };
            }
        }

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Id, BarPosition, BarEnabled FROM Socials LIMIT 1;";
            using var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Socials = new SocialsConfig
                {
                    BarPosition = Enum.TryParse<SocialBarPosition>(reader.GetString(1), out var pos) ? pos : SocialBarPosition.Bottom,
                    BarEnabled = reader.FieldCount > 2 && !reader.IsDBNull(2) && reader.GetInt32(2) != 0,
                };
                var socialsId = reader.GetString(0);

                using var entryCmd = _connection.CreateCommand();
                entryCmd.CommandText = "SELECT Service, Handle, ProfileUrl, Software FROM SocialEntry WHERE SocialsId = $id ORDER BY SortOrder;";
                entryCmd.Parameters.AddWithValue("$id", socialsId);
                using var entryReader = entryCmd.ExecuteReader();
                while (entryReader.Read())
                {
                    Socials.Entries.Add(new SocialEntry
                    {
                        Service = Enum.TryParse<SocialService>(entryReader.GetString(0), out var svc) ? svc : SocialService.Link,
                        Handle = entryReader.GetString(1),
                        ProfileUrl = entryReader.GetString(2),
                        FediverseSoftware = entryReader.FieldCount > 3 && !entryReader.IsDBNull(3) ? entryReader.GetString(3) : null,
                    });
                }
            }
        }

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = "SELECT TrackPath, IsEnabled FROM Music LIMIT 1;";
            using var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Music = new Music
                {
                    TrackPath = reader.GetString(0),
                    IsEnabled = reader.GetInt32(1) != 0,
                };
            }
        }

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = """
                SELECT Id, SceneId, AssetId, Type, Name, IsEnabled,
                       X, Y, Width, Height, Opacity, MonitorIndex, ClipShape, IsMirrored,
                       IsBackground, CaptureKey, ShowDesktop,
                       ChatFontSize, ChatFontColor, ChatBackgroundColor, ChatMaxMessages,
                       ChatShowTimestamps, ChatShowBadges, ChatMemberColor, ChatSuperChatColor,
                       WebUri, MediaPath, MediaIsLooping, MediaVolume, MediaPlaybackState,
                       AlertVideoAssetId, AlertVideoPath, AlertUseDefaultVideo, AlertShowTicker, AlertVideoVolume, SortOrder,
                       AlertDisplayMethod
                FROM Source ORDER BY SortOrder
                """;
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var sceneId = reader.GetString(1);
                var type = Enum.Parse<SourceType>(reader.GetString(3));
                var source = new Source
                {
                    Id = reader.GetString(0),
                    AssetId = reader.IsDBNull(2) ? null : reader.GetString(2),
                    Type = type,
                    Name = reader.GetString(4),
                    IsEnabled = reader.GetInt32(5) != 0,
                    X = reader.GetDouble(6),
                    Y = reader.GetDouble(7),
                    Width = reader.GetDouble(8),
                    Height = reader.GetDouble(9),
                    Opacity = reader.GetDouble(10),
                    MonitorIndex = reader.IsDBNull(11) ? null : reader.GetInt32(11),
                    ClipShape = Enum.TryParse<ClipShape>(reader.GetString(12), out var clip) ? clip : ClipShape.Traditional,
                    IsMirrored = reader.GetInt32(13) != 0,
                    // Rows saved before the Type setter derived the flag carry
                    // IsBackground=0; derive from Type, keeping the persisted bit
                    // for legacy DisplayCapture backdrops.
                    IsBackground = type == SourceType.Background || reader.GetInt32(14) != 0,
                    CaptureKey = reader.IsDBNull(15) ? null : reader.GetString(15),
                    ShowDesktop = reader.GetInt32(16) != 0,
                    ChatFontSize = reader.IsDBNull(17) ? 16 : reader.GetDouble(17),
                    ChatFontColor = reader.IsDBNull(18) ? "#FFFFFF" : reader.GetString(18),
                    ChatBackgroundColor = reader.IsDBNull(19) ? "#80000000" : reader.GetString(19),
                    ChatMaxMessages = reader.IsDBNull(20) ? 5 : reader.GetInt32(20),
                    ChatShowTimestamps = reader.IsDBNull(21) ? true : reader.GetInt32(21) != 0,
                    ChatShowBadges = reader.IsDBNull(22) ? true : reader.GetInt32(22) != 0,
                    ChatMemberColor = reader.IsDBNull(23) ? "#e94560" : reader.GetString(23),
                    ChatSuperChatColor = reader.IsDBNull(24) ? "#eab308" : reader.GetString(24),
                    WebUri = reader.IsDBNull(25) ? null : reader.GetString(25),
                    MediaPath = reader.IsDBNull(26) ? null : reader.GetString(26),
                    MediaIsLooping = reader.IsDBNull(27) ? false : reader.GetInt32(27) != 0,
                    MediaVolume = reader.IsDBNull(28) ? 1.0 : reader.GetDouble(28),
                    MediaPlaybackState = reader.IsDBNull(29)
                        ? MediaPlaybackState.Stopped
                        : Enum.TryParse<MediaPlaybackState>(reader.GetString(29), out var ps)
                            ? ps
                            : MediaPlaybackState.Stopped,
                    AlertVideoAssetId = reader.IsDBNull(30) ? null : reader.GetString(30),
                    AlertVideoPath = reader.IsDBNull(31) ? null : reader.GetString(31),
                    AlertUseDefaultVideo = reader.IsDBNull(32) ? true : reader.GetInt32(32) != 0,
                    AlertShowTicker = reader.IsDBNull(33) ? true : reader.GetInt32(33) != 0,
                    AlertVideoVolume = reader.IsDBNull(34) ? 1.0 : reader.GetDouble(34),
                    AlertDisplayMethod = reader.IsDBNull(36) ? AlertDisplayMethod.TickerScroll : (AlertDisplayMethod)reader.GetInt32(36),
                };
                if (!sourcesByScene.TryGetValue(sceneId, out var list))
                    sourcesByScene[sceneId] = list = new List<(Source, int)>();
                list.Add((source, reader.GetInt32(35)));
            }
        }

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = """
                SELECT SceneId, WebcamId, IsVisible, X, Y, Width, Height, Opacity,
                       ClipShape, IsMirrored, BorderColor, BorderOpacity, BorderWidth, BorderAnimation,
                       RectWidth, RectHeight, SortOrder
                FROM WebcamSceneConfig ORDER BY SortOrder
                """;
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var sceneId = reader.GetString(0);
                var config = new WebcamSceneConfig
                {
                    WebcamId = reader.GetString(1),
                    Name = Webcam?.Name ?? "Webcam",
                    IsVisible = reader.GetInt32(2) != 0,
                    X = reader.GetDouble(3),
                    Y = reader.GetDouble(4),
                    Width = reader.GetDouble(5),
                    Height = reader.GetDouble(6),
                    Opacity = reader.GetDouble(7),
                    ClipShape = Enum.TryParse<ClipShape>(reader.GetString(8), out var clip) ? clip : ClipShape.Traditional,
                    IsMirrored = reader.GetInt32(9) != 0,
                    BorderColor = reader.IsDBNull(10) ? string.Empty : reader.GetString(10),
                    BorderOpacity = reader.IsDBNull(11) ? 1.0 : reader.GetDouble(11),
                    BorderWidth = reader.IsDBNull(12) ? 0 : reader.GetInt32(12),
                    BorderAnimation = Enum.TryParse<BorderAnimation>(reader.GetString(13), out var anim) ? anim : BorderAnimation.None,
                    RectWidth = reader.IsDBNull(14) ? null : reader.GetDouble(14),
                    RectHeight = reader.IsDBNull(15) ? null : reader.GetDouble(15),
                };
                if (!configsByScene.TryGetValue(sceneId, out var list))
                    configsByScene[sceneId] = list = new List<(WebcamSceneConfig, int)>();
                list.Add((config, reader.GetInt32(16)));
            }
        }

        foreach (var scene in scenes)
        {
            // Source and WebcamSceneConfig rows share ONE z-order space per scene
            // (both SortOrders = the element's index within scene.Elements). Merge
            // the two tables' rows by that shared z rather than appending all
            // sources before all configs — a webcam slotted between sources must
            // reload in place. Legacy rows (written before the unified space) may
            // tie at 0: break ties sources-first, so old data reads back stacked
            // the same way it did pre-unification.
            var ordered = new List<(int Sort, SceneElement Element)>();
            if (sourcesByScene.TryGetValue(scene.Id, out var sources))
                foreach (var (source, sort) in sources)
                    ordered.Add((sort, source));
            if (configsByScene.TryGetValue(scene.Id, out var configs))
                foreach (var (config, sort) in configs)
                    ordered.Add((sort, config));

            foreach (var (_, element) in ordered.OrderBy(e => e.Sort).ThenBy(e => e.Element is Source ? 0 : 1))
                scene.Elements.Add(element);

            var bg = scene.Elements.OfType<Source>().FirstOrDefault(s => s.IsBackground);
            if (bg != null)
            {
                // Load from source-keyed settings (new) or scene-keyed (legacy migration)
                var path = GetSetting($"BackgroundPath_{bg.Id}");
                if (string.IsNullOrWhiteSpace(path))
                    path = GetSetting($"BackgroundPath_{scene.Id}");
                if (!string.IsNullOrWhiteSpace(path))
                    bg.CustomBackgroundPath = path;

                var useDefault = GetSetting($"BackgroundUseDefault_{bg.Id}");
                if (string.IsNullOrWhiteSpace(useDefault))
                    useDefault = GetSetting($"BackgroundUseDefault_{scene.Id}");
                if (useDefault == "0")
                    bg.UseDefaultBackground = false;
            }
        }

        return scenes;
    }
}