
using Newtonsoft.Json.Linq;
using System.Data.SQLite;
using System.Diagnostics;
using System.Security.Policy;
using System.Text.RegularExpressions;

namespace DragonVideoPlayer
{

    public class sql_class
    {
        private DragDropForm _Form1;


        private Drag_form_state _drag_form_state;
        private object loop_lock = new object();

        public sql_class(DragDropForm Window, Drag_form_state drag_form_state)
        {
            _Form1 = Window;
            _drag_form_state = drag_form_state;
        }
        public static List<string> sql_kensaku(string keyword, int db_sort, int db_sort_desc, int limit, int offset)
        {
            var list = new List<string>();
            if (!System.IO.File.Exists("path.db")) return list;

            if (string.IsNullOrEmpty(keyword))
                return list;

            string order = db_sort_desc == 1 ? "DESC" : "ASC";

            string col = db_sort switch
            {
                1 => "created",
                2 => "count",
                3 => "path",
                4 => "rowid",
                5 => "size",
                _ => "modified"
            };

            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var cmd = cn.CreateCommand())
                    {
                        cmd.CommandText =
                            $"SELECT path FROM path " +
                            $"WHERE path LIKE @keyword " +
                            $"ORDER BY {col} {order} " +
                            $"LIMIT @limit OFFSET @offset";

                        cmd.Parameters.AddWithValue("@keyword", "%" + keyword + "%");
                        cmd.Parameters.AddWithValue("@limit", limit);
                        cmd.Parameters.AddWithValue("@offset", offset);

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }

            return list;
        }
        public static List<string> sql_all_db(int db_sort, int db_sort_desc, int limit, int offset)
        {
            List<string> return_lists = new List<string>();
            if (!System.IO.File.Exists("path.db")) return return_lists;

            string DESC = "ASC";
            if (db_sort_desc == 1)
            {
                DESC = "DESC";
            }

            string col = "modified";
            if (db_sort == 1) col = "created";
            if (db_sort == 2) col = "count";
            if (db_sort == 3) col = "path";
            if (db_sort == 4) col = "rowid";
            if (db_sort == 5) col = "size";

            if (System.IO.File.Exists("path.db"))
            {
                try
                {
                    using (var cn = new SQLiteConnection("Data Source=path.db"))
                    {
                        cn.Open();

                        using (var cmd = cn.CreateCommand())
                        {
                            // ★ limit / offset 追加
                            cmd.CommandText = "SELECT * FROM path ORDER BY " + col + " " + DESC +
                                              " LIMIT @limit OFFSET @offset";

                            // ★ パラメータ化（安全＆バグ防止）
                            cmd.Parameters.AddWithValue("@limit", limit);
                            cmd.Parameters.AddWithValue("@offset", offset);

                            Debug.WriteLine("cmd.CommandText: " + cmd.CommandText);

                            using (SQLiteDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    return_lists.Add((string)reader["path"]);
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    Debug.WriteLine(ex.Message + ex.StackTrace);
                }
            }
            return return_lists;

        }
        public static List<String> sql_all_db(int db_sort, int db_sort_desc)
        {
            List<String> return_lists = new List<String>();
            if (!System.IO.File.Exists("path.db")) return return_lists;
            string DESC = "ASC";
            if (db_sort_desc == 1)
            {
                DESC = "DESC";
            }
            string col = "modified";
            if (db_sort == 1)
            {
                col = "created";
            }


            if (db_sort == 2)
            {
                col = "count";
            }
            if (db_sort == 3)
            {
                col = "path";
            }
            if (db_sort == 4)
            {
                col = "rowid";
            }

            if (db_sort == 5)
            {
                col = "size";
            }
            if (System.IO.File.Exists("path.db"))
            {
                string Value = "";
                try
                {
                    using (var cn = new SQLiteConnection("Data Source=path.db"))
                    {
                        cn.Open();


                        SQLiteCommand cmd = cn.CreateCommand();
                        cmd.CommandText = "SELECT * FROM path order by " + col + " " + DESC;
                        Debug.WriteLine("cmd.CommandText" + cmd.CommandText);
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {

                                Value = (string)reader["path"];

                                return_lists.Add(Value);



                            }
                        }
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    Debug.WriteLine(ex.Message + ex.StackTrace);

                }



            }
            return return_lists;
        }

        public static List<String> sql_ranking(int ranking_su)
        {

            var list = new List<string>();
            if (!System.IO.File.Exists("path.db")) return list;
            if (ranking_su <= 0) return list;

            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var cmd = cn.CreateCommand())
                    {
                        cmd.CommandText =
                            "SELECT path FROM path ORDER BY count DESC LIMIT @limit";
                        cmd.Parameters.AddWithValue("@limit", ranking_su);

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }

            return list;
        }

        public static List<String> sql_history(int history_su)
        {

            var list = new List<string>();
            if (!System.IO.File.Exists("path.db")) return list;
            if (history_su <= 0) return list;

            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var cmd = cn.CreateCommand())
                    {
                        cmd.CommandText =
                            "SELECT path FROM path ORDER BY date DESC LIMIT @limit";
                        cmd.Parameters.AddWithValue("@limit", history_su);

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }

            return list;
        }

        public static List<String> sql_kensaku(string keyword, int db_sort, int db_sort_desc)
        {

            var list = new List<string>();
            if (!System.IO.File.Exists("path.db")) return list;
            if (string.IsNullOrEmpty(keyword))
                return list;

            string order = db_sort_desc == 1 ? "DESC" : "ASC";

            string col = db_sort switch
            {
                1 => "created",
                2 => "count",
                3 => "path",
                4 => "rowid",
                5 => "size",
                _ => "modified"
            };

            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var cmd = cn.CreateCommand())
                    {
                        cmd.CommandText =
                            $"SELECT path FROM path " +
                            $"WHERE path LIKE @keyword " +
                            $"ORDER BY {col} {order}";

                        cmd.Parameters.AddWithValue("@keyword", "%" + keyword + "%");

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }

            return list;
        }

        public static double sql_add(string path)
        {

            if (string.IsNullOrEmpty(path))
                return 0;
            if (!System.IO.File.Exists("path.db")) return 0;
            if (System.Text.RegularExpressions.Regex.IsMatch(path, config_class.config_text_read("streamlinkで取得するURLの正規表現"), System.Text.RegularExpressions.RegexOptions.IgnoreCase) ||
               Regex.IsMatch(path, @"\.m3u8?(\?|$)", RegexOptions.IgnoreCase))
            {
                return 0;
            }
            if (System.Text.RegularExpressions.Regex.IsMatch(path, "https?:", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            {

            }
            else
            {
                if (System.IO.File.Exists(path) == false)
                {
                    return 0;
                }
            }


            try
            {
                DateTime dtCreate = DateTime.Now;
                DateTime dtUpdate = DateTime.Now;
                long filesize = 0;

                if (!Regex.IsMatch(path, "https?:", RegexOptions.IgnoreCase))
                {
                    dtCreate = File.GetCreationTime(path);
                    dtUpdate = File.GetLastWriteTime(path);
                    filesize = new FileInfo(path).Length;
                }
                using (var conn = new SQLiteConnection("Data Source=path.db"))
                {
                    conn.Open(); // ← 必ず最初
                    //using (var tran = conn.BeginTransaction())
                    using (var cmd = conn.CreateCommand())
                    {
                        // ① まずINSERT（なければ）
                        cmd.CommandText =
                            "INSERT OR IGNORE INTO path(path,time,date,count, created, modified, size) VALUES(@path,0,@date,0,@created,@modified,@size)";
                        cmd.Parameters.AddWithValue("@path", path);
                        cmd.Parameters.AddWithValue("@date", DateTime.Now);
                        cmd.Parameters.AddWithValue("@created", dtCreate);
                        cmd.Parameters.AddWithValue("@modified", dtUpdate);
                        cmd.Parameters.AddWithValue("@size", filesize);
                        cmd.ExecuteNonQuery();

                        // ② そのままtime取得
                        cmd.Parameters.Clear();
                        cmd.CommandText = "SELECT time FROM path WHERE path=@path";
                        cmd.Parameters.AddWithValue("@path", path);

                        object result = cmd.ExecuteScalar();
                        Debug.WriteLine(result);
                        // tran.Commit();

                        return result != null ? Convert.ToDouble(result) : 0;
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
                return 0;
            }
        }

        //全DB数取得
        public static double count_get(string path)
        {
            if (!System.IO.File.Exists("path.db")) return 0;
            if (string.IsNullOrEmpty(path))
            {
                return 0;
            }
            double count = 0;
            if (System.IO.File.Exists(path) == false)
            {
                return 0;
            }


            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();


                    SQLiteCommand cmd = cn.CreateCommand();
                    // cmd.CommandText = "SELECT * FROM path where path='" + path + "'";
                    cmd.CommandText = "SELECT * FROM path where path=@path";
                    cmd.Parameters.AddWithValue("@path", path);

                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {


                            count = (double)reader["count"];
                            Debug.WriteLine(" count " + count);


                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);

            }




            return count;
        }

        //レジュームで使うタイムライン保存
        public static void time_update(string path, double time_double)
        {
            if (!System.IO.File.Exists("path.db")) return;

            if (string.IsNullOrEmpty(path)) return;

            try
            {
                using (var conn = new SQLiteConnection("Data Source=path.db"))
                {
                    conn.Open();

                    using (var tran = conn.BeginTransaction())
                    using (var cmd = conn.CreateCommand())
                    {
                        DateTime now = DateTime.Now;

                        // ファイル情報
                        DateTime created = now;
                        DateTime modified = now;
                        long size = 0;

                        if (!System.Text.RegularExpressions.Regex.IsMatch(path, "https?:", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                        {
                            if (!System.IO.File.Exists(path)) return;

                            created = System.IO.File.GetCreationTime(path);
                            modified = System.IO.File.GetLastWriteTime(path);
                            size = new System.IO.FileInfo(path).Length;
                        }

                        // ★ 1回で全部やる（重要）
                        cmd.CommandText = @"
INSERT INTO path(path, time, count, date, created, modified, size)
VALUES(@path, @time, 1, @date, @created, @modified, @size)
ON CONFLICT(path) DO UPDATE SET
    time = @time,
    count = count + 1,
    date = @date,
    modified = @modified,
    size = @size
";

                        cmd.Parameters.AddWithValue("@path", path);
                        cmd.Parameters.AddWithValue("@time", time_double);
                        cmd.Parameters.AddWithValue("@date", now);
                        cmd.Parameters.AddWithValue("@created", created);
                        cmd.Parameters.AddWithValue("@modified", modified);
                        cmd.Parameters.AddWithValue("@size", size);

                        cmd.ExecuteNonQuery();

                        tran.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
            }
        }
        public static void sql_delete(string path)
        {
            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var cmd = new SQLiteCommand("DELETE FROM path WHERE path=@p", cn))
                    {
                        cmd.Parameters.AddWithValue("@p", path);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message);
            }
        }
        public static int listview_sql_count(string keyword = "")
        {
            int re_count = 0;

            if (!File.Exists("path.db"))
                return 0;

            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    string sql;

                    if (string.IsNullOrEmpty(keyword))
                        sql = "SELECT COUNT(*) FROM path";
                    else
                        sql = "SELECT COUNT(*) FROM path WHERE path LIKE @k";

                    using (var cmd = new SQLiteCommand(sql, cn))
                    {
                        if (!string.IsNullOrEmpty(keyword))
                            cmd.Parameters.AddWithValue("@k", "%" + keyword + "%");

                        object result = cmd.ExecuteScalar();

                        if (result != null && result != DBNull.Value)
                            re_count = Convert.ToInt32(result);
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message);
            }

            return re_count;
        }
        public int sql_count()
        {
            int re_count = 0;


            if (System.IO.File.Exists("path.db"))
            {

                try
                {
                    using (var cn = new SQLiteConnection("Data Source=path.db"))
                    {
                        cn.Open();


                        SQLiteCommand cmd = cn.CreateCommand();
                        SQLiteCommand command = new SQLiteCommand("SELECT Count(*) FROM path", cn);
                        //SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM sqlite_master where type='table'", connection);

                        re_count = int.Parse(command.ExecuteScalar().ToString()); //this query raises error Specified cast is not valid.

                        cn.Close();
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    Debug.WriteLine(ex.Message + ex.StackTrace);

                }
            }
            return re_count;
        }


        public int count_get()
        {
            if (!System.IO.File.Exists("path.db")) return 0;
            try
            {
                using (var cn = new SQLiteConnection("Data Source=path.db"))
                {
                    cn.Open();

                    using (var command = new SQLiteCommand("SELECT COUNT(*) FROM path", cn))
                    {
                        return Convert.ToInt32(command.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                Debug.WriteLine(ex.Message + ex.StackTrace);
                return 0;
            }



        }
        public void db_add_start()
        {
            Debug.WriteLine("db_add_sta");

            lock (loop_lock)
            {
                _drag_form_state.stop_flag = false;
                var sw = new System.Diagnostics.Stopwatch();
                sw.Start();
                drag_main();



                // 結果表示

                System.TimeSpan ts = sw.Elapsed;
                string time_st = "";
                string ts_h = "";
                string ts_min = "";
                string ts_se = "";
                try
                {
                    if (ts.Hours >= 1)
                    {
                        ts_h = ts.Hours.ToString() + "時間";
                    }
                    if (ts.Minutes >= 1)
                    {
                        ts_min = ts.Minutes.ToString() + "分";
                    }

                    ts_se = ts.Seconds.ToString() + "秒";
                }
                catch (System.Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    _Form1.Invoke(new Action(delegate
                    {

                        _Form1.textbox_add(ex.Message + ex.StackTrace, 2);


                    }));

                }
                time_st = ts_h + ts_min + ts_se;
                _Form1.Invoke(new Action(delegate
                {

                    _Form1.textbox_add("終わりました。", 2);
                    _Form1.textbox_add("処理時間:" + time_st, 2);


                }));
            }

        }
        private void drag_main()
        {
            if (System.IO.File.Exists("path.db") == false)
            {

                _Form1.textbox_add("path.dbが無いです", 2);

            }
            Debug.WriteLine(" drag_mai");
            Debug.WriteLine(_drag_form_state._d_d_file_list.Count);
            List<String> files_Arraylists = new List<String>();
            _Form1.Invoke(new Action(delegate
            {

                _Form1.prog(0, 1);
                _Form1.prog(_drag_form_state._d_d_file_list.Count, 2);


                _Form1.textbox_add(_drag_form_state._d_d_file_list.Count.ToString(), 4);

            }));


            int count = 0;
            Debug.WriteLine(_drag_form_state._d_d_file_list.Count);
            foreach (string get_path in _drag_form_state._d_d_file_list)
            {

                String stFilePath = get_path;
                // stFilePath = stFilePath.Replace("\\", "/");

                Debug.WriteLine(stFilePath);
                count = count + 1;

                _Form1.Invoke(new Action(delegate
                {


                    _Form1.prog(count, 3);


                    _Form1.textbox_add(count.ToString(), 3);

                }));


                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }

                //ファイルなら
                if (System.IO.File.Exists(stFilePath) == true)
                {
                    if (files_Arraylists.Contains(stFilePath) == false)
                    {
                        files_Arraylists.Add(stFilePath);
                    }

                }

                if (System.IO.Directory.Exists(stFilePath) == true)
                {

                    files_Arraylists.AddRange(folder_get(stFilePath));
                }

                Debug.WriteLine(files_Arraylists.Count);


                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }


            }

            count = 0;
            _Form1.Invoke(new Action(delegate
            {

                _Form1.prog(0, 1);
                _Form1.prog(files_Arraylists.Count, 2);


                _Form1.textbox_add(files_Arraylists.Count.ToString(), 4);

            }));

            count = 0;
            foreach (string stFilePath in files_Arraylists)
            {
                count++;   // ← これが無い
                _Form1.Invoke(new Action(delegate
                {


                    _Form1.prog(count, 3);


                    _Form1.textbox_add(count.ToString(), 3);

                }));


                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }
                sql_add_form(stFilePath);
            }

        }
        public void sql_add_form(string path)
        {
            if (System.IO.File.Exists(path) == false)
            {


                _Form1.Invoke(new Action(delegate
            {

                _Form1.textbox_add(path + "：ファイルが存在しないのでスルーします。", 2);


            }));
                return;
            }

            _Form1.Invoke(new Action(delegate
            {

                _Form1.textbox_add(path + "を追加します。", 2);


            }));
            try
            {
                using (var conn = new SQLiteConnection("Data Source=path.db"))
                {
                    conn.Open();

                    using (var tran = conn.BeginTransaction())
                    using (var cmd = conn.CreateCommand())
                    {
                        DateTime now = DateTime.Now;
                        DateTime created = File.GetCreationTime(path);
                        DateTime modified = File.GetLastWriteTime(path);
                        long size = new FileInfo(path).Length;

                        cmd.CommandText =
                            "INSERT OR IGNORE INTO path(path,time,date,count,created,modified,size) " +
                            "VALUES(@path,0,@time,0,@created,@modified,@size)";

                        cmd.Parameters.AddWithValue("@path", path);
                        cmd.Parameters.AddWithValue("@time", now);
                        cmd.Parameters.AddWithValue("@created", created);
                        cmd.Parameters.AddWithValue("@modified", modified);
                        cmd.Parameters.AddWithValue("@size", size);

                        int result = cmd.ExecuteNonQuery();

                        if (result == 0)
                        {
                            _Form1.Invoke(new Action(delegate
                            {

                                _Form1.textbox_add(path + ":DBに登録済みです。", 2);


                            }));
                        }

                        tran.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                _Form1.Invoke(new Action(delegate
                {

                    _Form1.textbox_add(ex.Message + ex.StackTrace, 2);
                }));
            }

        }
        public void thum_creat()
        {

            lock (loop_lock)
            {
                _Form1.Invoke(new Action(delegate
                {



                    _Form1.textbox_add("：サムネイル作します。。", 2);

                }));

                int count_sousu = count_get();
                if (System.IO.File.Exists("path.db"))
                {
                    string Value = "";
                    try
                    {
                        using (var cn = new SQLiteConnection("Data Source=path.db"))
                        {
                            cn.Open();


                            SQLiteCommand cmd = cn.CreateCommand();
                            cmd.CommandText = "SELECT * FROM path";
                            _Form1.Invoke(new Action(delegate
                            {


                                _Form1.prog(0, 1);

                                _Form1.prog(count_sousu, 2);
                            }));
                            int count = 0;
                            using (SQLiteDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    _Form1.Invoke(new Action(delegate
                                    {


                                        _Form1.prog(count, 3);


                                    }));

                                    count = count + 1;
                                    if (_drag_form_state.stop_flag == true)
                                    {
                                        break;
                                    }
                                    Value = (string)reader["path"];

                                    _Form1.Invoke(new Action(delegate
                                    {


                                        _Form1.textbox_add(Value, 2);




                                    }));

                                    if (System.IO.File.Exists(Value))
                                    {
                                        string filename = Value;
                                        filename = filename.Replace(@":\", "_");
                                        filename = filename.Replace(@"\", "_");

                                        cap_creat(Value, "./img/" + filename + ".jpg");







                                    }
                                }
                            }
                            cn.Close();
                        }
                    }
                    catch (Exception ex)
                    {

                        error.write_error_log(ex.Message + ex.StackTrace);

                        _Form1.Invoke(new Action(delegate
                            {

                                _Form1.textbox_add(ex.Message + ex.StackTrace, 2);


                            }));
                    }



                }


                _Form1.Invoke(new Action(delegate
                {

                    _Form1.textbox_add("終了しました", 2);


                }));


            }
        }
        public void dead_link_check()
        {
            lock (loop_lock)
            {
                _drag_form_state.stop_flag = false;

                _Form1.Invoke(() =>
                {
                    _Form1.textbox_add("：デッドリンクチェックします。。", 2);
                });

                var paths = new List<string>();

                // ① 先に全部取得
                try
                {
                    using (var cn = new SQLiteConnection("Data Source=path.db"))
                    {
                        cn.Open();
                        using (var cmd = new SQLiteCommand("SELECT path FROM path", cn))
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                paths.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    Debug.WriteLine(ex.Message);
                    return;
                }

                int count = 0;

                using (var conn = new SQLiteConnection("Data Source=path.db"))
                {
                    conn.Open();

                    foreach (var path in paths)
                    {
                        if (_drag_form_state.stop_flag) break;

                        count++;
                        if (System.Text.RegularExpressions.Regex.IsMatch(path, "https?:", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                        {
                            bool ok = UrlChecker.IsUrlAlive(path);
                            if (ok)
                            {
                                continue;
                            }
                            else
                            {
                                using (var cmd = conn.CreateCommand())
                                {
                                    cmd.CommandText = "DELETE FROM path WHERE path=@path";
                                    cmd.Parameters.AddWithValue("@path", path);
                                    cmd.ExecuteNonQuery();
                                }

                            }
                           
                        }

                        if (!File.Exists(path))
                        {
                            // ② DB削除
                            using (var cmd = conn.CreateCommand())
                            {
                                cmd.CommandText = "DELETE FROM path WHERE path=@path";
                                cmd.Parameters.AddWithValue("@path", path);
                                cmd.ExecuteNonQuery();
                            }

                            // ③ サムネ削除
                            string filename = path.Replace(@":\", "_").Replace(@"\", "_");
                            string img = "./img/" + filename + ".jpg";

                            if (File.Exists(img))
                            {
                                try { File.Delete(img); }
                                catch (Exception ex)
                                {
                                    error.write_error_log(ex.Message + ex.StackTrace);
                                    Debug.WriteLine(ex.Message);
                                }
                            }
                        }
                    }
                }

                _Form1.Invoke(() =>
                {
                    _Form1.textbox_add("終了しました", 2);
                });
            }
        }

        public void thum_creat_thread()
        {
            lock (loop_lock)
            {
                _drag_form_state.stop_flag = false;

                _Form1.Invoke(() =>
                {
                    _Form1.textbox_add("：サムネイルを作成しますします。。", 2);
                });

                var paths = new List<string>();

                // ① 先に全部取得
                try
                {
                    using (var cn = new SQLiteConnection("Data Source=path.db"))
                    {
                        cn.Open();
                        using (var cmd = new SQLiteCommand("SELECT path FROM path", cn))
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                paths.Add(reader["path"].ToString());
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    Debug.WriteLine(ex.Message);
                    return;
                }

                int count = 0;

                using (var conn = new SQLiteConnection("Data Source=path.db"))
                {
                    conn.Open();

                    foreach (var path in paths)
                    {
                        if (_drag_form_state.stop_flag) break;

                        count++;

                        if (System.Text.RegularExpressions.Regex.IsMatch(path, "https?:", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                        {
                          continue;
                        }

                            if (System.IO.File.Exists(path))
                            {
                                string filename = path;
                                filename = filename.Replace(@":\", "_");
                                filename = filename.Replace(@"\", "_");
                                if (System.IO.File.Exists("./img/" + filename + ".jpg")==false)
                                {
                                    cap_creat(path, "./img/" + filename + ".jpg");
                                }
                             
                            }
                        
                    }
                }

                _Form1.Invoke(() =>
                {
                    _Form1.textbox_add("終了しました", 2);
                });
            }
        }
        public void format()
        {



            _Form1.textbox_add("フォーマットします。。", 2);

            if (File.Exists("path.db"))
            {
                _Form1.textbox_add("path.dbのレコードを全削除します。", 2);

                try
                {
                    using (var connection = new SQLiteConnection("Data Source=path.db"))
                    {
                        connection.Open();

                        using (var cmd = connection.CreateCommand())
                        {
                            cmd.CommandText = "DELETE FROM path;";
                            cmd.ExecuteNonQuery();
                        }

                        using (var cmd = connection.CreateCommand())
                        {
                            cmd.CommandText = "VACUUM;";
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    _Form1.textbox_add(ex.Message, 2);
                }
            }

            _Form1.textbox_add("終了しました", 2);

        }
        private HashSet<string> folder_get(string folder_path)
        {
            _Form1.Invoke(new Action(delegate
            {



                _Form1.textbox_add(folder_path + "を取得します", 2);




            }));


            HashSet<string> files_Arraylists = new HashSet<string>();



            string[] file_lists = null;



            HashSet<string> kiji_list = new HashSet<string>();


            HashSet<string> files = new HashSet<string>();
            file_lists = System.IO.Directory.GetFiles(folder_path, "*", System.IO.SearchOption.AllDirectories);

            Debug.WriteLine(file_lists.Length);
            foreach (string file_path in file_lists)
            {


                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }


                if (System.IO.File.Exists(file_path) == true)
                {



                    if (System.Text.RegularExpressions.Regex.IsMatch(file_path, config_class.config_text_read("動画取得の拡張子の正規表現"), System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                    {


                    }
                    else
                    {

                        continue;
                    }

                    files_Arraylists.Add(file_path);

                }

            }
            return files_Arraylists;

        }

        private void cap_creat(string movie_path, string img_path)
        {
            if (System.IO.File.Exists(img_path))
            {
                _Form1.Invoke(new Action(delegate
                {



                    _Form1.textbox_add(img_path + "：サムネイルが既にあるのでスルーします。。", 2);




                }));

                return;
            }
            int THUMB_SIZE = 256;
            _Form1.Invoke(new Action(delegate
          {


              _Form1.textbox_add(img_path + "を作成します。", 2);



          }));



            try
            {
                Bitmap thumbnail = WindowsThumbnailProvider.GetThumbnail(
                  movie_path, THUMB_SIZE, THUMB_SIZE, ThumbnailOptions.None);

                thumbnail.Save(img_path, System.Drawing.Imaging.ImageFormat.Jpeg);
            }
            catch (Exception ex)
            {
                error.write_error_log(ex.Message + ex.StackTrace);
                _Form1.Invoke(new Action(delegate
                {


                    _Form1.textbox_add(ex.Message, 2);



                }));



            }

        }

        public void sql_creat_start()
        {

            lock (loop_lock)
            {
                Debug.WriteLine("sql_creat_st");
                _drag_form_state.stop_flag = false;

                var sw = new System.Diagnostics.Stopwatch();
                sw.Start();
                sql_creat_textbox();
                System.TimeSpan ts = sw.Elapsed;
                string time_st = "";
                string ts_h = "";
                string ts_min = "";
                string ts_se = "";
                try
                {
                    if (ts.Hours >= 1)
                    {
                        ts_h = ts.Hours.ToString() + "時間";
                    }
                    if (ts.Minutes >= 1)
                    {
                        ts_min = ts.Minutes.ToString() + "分";
                    }

                    ts_se = ts.Seconds.ToString() + "秒";
                }
                catch (System.Exception ex)
                {
                    error.write_error_log(ex.Message + ex.StackTrace);
                    _Form1.Invoke(new Action(delegate
                    {

                        _Form1.textbox_add(ex.Message + ex.StackTrace, 2);


                    }));
                }
                time_st = ts_h + ts_min + ts_se;

                _Form1.Invoke(new Action(delegate
                {

                    _Form1.textbox_add("終わりました。", 2);
                    _Form1.textbox_add("処理時間:" + time_st, 2);


                }));



            }
        }


        private void sql_creat_textbox()
        {




            HashSet<string> video_lists = new HashSet<string>();

            HashSet<string> files_Arraylists = new HashSet<string>();
            //ストリームの末端まで繰り返す

            //一行読み込んで表示する
            string stResult = "";



            string text = _drag_form_state._folder_list;

            List<string> lineList = text
            .Split(new[] { "\r\n", "\r", "\n" }, StringSplitOptions.RemoveEmptyEntries)
            .ToList();
            Debug.WriteLine(lineList.Count);
            foreach (var line in lineList)
            {
                Debug.WriteLine(line);

                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }

                if (string.IsNullOrEmpty(line) == true)
                {
                    continue;
                }

                stResult = line;


                Debug.WriteLine(System.IO.Directory.Exists(stResult));

                if (System.IO.Directory.Exists(stResult) == false)
                {

                    continue;
                }



                var fols = folder_get(stResult);
                foreach (var path in fols)
                {
                    files_Arraylists.Add(path);
                }
                Debug.WriteLine(files_Arraylists.Count);
                foreach (string movie_path in files_Arraylists)
                {
                    Debug.WriteLine(movie_path);



                    video_lists.Add(movie_path);


                }
            }



            _Form1.Invoke(new Action(delegate
            {
                _Form1.prog(0, 1);
                _Form1.prog(video_lists.Count, 2);

            }));
            int count = 0;
            _Form1.Invoke(new Action(delegate
            {
                _Form1.textbox_add(video_lists.Count.ToString(), 4);

            }));

            foreach (string movie_path in video_lists)
            {
                count = count + 1;
                _Form1.Invoke(new Action(delegate
                    {
                        _Form1.prog(count, 3);
                    }));





                _Form1.Invoke(new Action(delegate
                {
                    _Form1.textbox_add(count.ToString(), 3);
                }));


                if (_drag_form_state.stop_flag == true)
                {
                    break;
                }
                string seiki = config_class.config_text_read("動画取得の拡張子の正規表現");
                if (System.Text.RegularExpressions.Regex.IsMatch(movie_path, seiki, System.Text.RegularExpressions.RegexOptions.IgnoreCase))
                {

                    sql_add(movie_path);
                    string filename = movie_path;
                    filename = filename.Replace(@":\", "_");
                    filename = filename.Replace(@"\", "_");
                    if (config_class.config_bool_read("サムネイル作成") == true)
                    {
                        cap_creat(movie_path, "./img/" + filename + ".jpg");
                    }






                }








            }


            _Form1.Invoke(new Action(delegate
            {
                _Form1.textbox_add("終了しました", 2);


            }));

        }


    }

}


