using CsvHelper;
using Guna.UI2.WinForms;
using Npgsql;
using System;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Linq;

namespace DeanOfficeApp
{
    public partial class StudentForm : Form
    {
        private Guna2DataGridView dgvStudents;
        private Guna2Button btnImportCsv;
        private Guna2ComboBox cmbGroupFilter;
        private Guna2Button btnRefresh;
        private Guna2HtmlLabel lblFilter;
        private Guna2TextBox txtSearch;
        private DataTable _studentsTable;

        private readonly string _connString = "Host=245e1-rw.db.pub.dbaas.postgrespro.ru;Port=5432;Database=dbpractice;Username=mardasov_mv;Password=A4%2x$$2Ynr";

        public StudentForm()
        {
            InitializeComponent();
            this.Load += async (s, e) => await LoadAllDataAsync();
        }

        private async Task LoadAllDataAsync()
        {
            await LoadGroupsAsync();
            await LoadStudentsAsync();
        }

        private async Task LoadGroupsAsync()
        {
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    var cmd = new NpgsqlCommand("SELECT group_id, name FROM \"Group\" ORDER BY name", conn);
                    var dt = new DataTable();
                    dt.Load(await cmd.ExecuteReaderAsync());
                    cmbGroupFilter.DisplayMember = "name";
                    cmbGroupFilter.ValueMember = "group_id";
                    cmbGroupFilter.DataSource = dt;
                    cmbGroupFilter.SelectedIndex = -1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки групп: {ex.Message}");
            }
        }

        private async Task LoadStudentsAsync(int? groupId = null)
        {
            string sql = @"
                SELECT 
                    s.record_book,
                    s.full_name,
                    g.name AS group_name,
                    COALESCE(AVG(p.grade), 0) AS average_grade
                FROM Student s
                LEFT JOIN ""Group"" g ON s.group_id = g.group_id
                LEFT JOIN Progress p ON s.record_book = p.record_book
            ";
            if (groupId.HasValue)
                sql += " WHERE g.group_id = @gid";
            sql += " GROUP BY s.record_book, s.full_name, g.name ORDER BY s.full_name";

            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        if (groupId.HasValue)
                            cmd.Parameters.AddWithValue("@gid", groupId.Value);
                        var dt = new DataTable();
                        dt.Load(await cmd.ExecuteReaderAsync());
                        _studentsTable = dt;
                        ApplyFilter();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки студентов: {ex.Message}");
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }

        private void ApplyFilter()
        {
            if (_studentsTable == null) return;
            string filterText = txtSearch.Text.Trim().ToLower();
            if (string.IsNullOrEmpty(filterText))
            {
                dgvStudents.DataSource = _studentsTable;
            }
            else
            {
                var filteredRows = _studentsTable.AsEnumerable()
                    .Where(row => row["full_name"].ToString().ToLower().Contains(filterText))
                    .CopyToDataTable();
                dgvStudents.DataSource = filteredRows;
            }

            if (dgvStudents.Columns.Contains("record_book"))
                dgvStudents.Columns["record_book"].Visible = false;
            dgvStudents.Columns["average_grade"].HeaderText = "Средний балл";
            dgvStudents.Columns["average_grade"].DefaultCellStyle.Format = "0.00";
        }

        private async void btnRefresh_Click(object sender, EventArgs e)
        {
            int? groupId = cmbGroupFilter.SelectedValue as int?;
            await LoadStudentsAsync(groupId);
        }

        private async void cmbGroupFilter_SelectedIndexChanged(object sender, EventArgs e)
        {
            int? groupId = cmbGroupFilter.SelectedValue as int?;
            await LoadStudentsAsync(groupId);
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
            ApplyFilter();
        }

        private async void btnImportCsv_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                ofd.Filter = "CSV files (*.csv)|*.csv";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        Cursor = Cursors.WaitCursor;
                        using (var reader = new StreamReader(ofd.FileName, Encoding.UTF8))
                        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
                        {
                            var records = csv.GetRecords<dynamic>();
                            int inserted = 0, skipped = 0;
                            foreach (var rec in records)
                            {
                                string fullName = rec.full_name;
                                string birthDateStr = rec.birth_date;
                                string address = rec.address;
                                int groupId = int.Parse(rec.group_id);

                                using (var conn = new NpgsqlConnection(_connString))
                                {
                                    await conn.OpenAsync();
                                    var check = new NpgsqlCommand("SELECT COUNT(*) FROM Student WHERE full_name = @name AND birth_date = @birth", conn);
                                    check.Parameters.AddWithValue("@name", fullName);
                                    check.Parameters.AddWithValue("@birth", DateTime.Parse(birthDateStr));
                                    long cnt = (long)await check.ExecuteScalarAsync();
                                    if (cnt == 0)
                                    {
                                        var ins = new NpgsqlCommand("INSERT INTO Student (full_name, birth_date, address, group_id) VALUES (@f, @b, @a, @g)", conn);
                                        ins.Parameters.AddWithValue("@f", fullName);
                                        ins.Parameters.AddWithValue("@b", DateTime.Parse(birthDateStr));
                                        ins.Parameters.AddWithValue("@a", address);
                                        ins.Parameters.AddWithValue("@g", groupId);
                                        await ins.ExecuteNonQueryAsync();
                                        inserted++;
                                    }
                                    else skipped++;
                                }
                            }
                            MessageBox.Show($"Импорт завершён.\nДобавлено: {inserted}\nПропущено (дубликаты): {skipped}");
                            await LoadStudentsAsync(cmbGroupFilter.SelectedValue as int?);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Ошибка импорта: " + ex.Message);
                    }
                    finally
                    {
                        Cursor = Cursors.Default;
                    }
                }
            }
        }

        // Дизайнер
        private void InitializeComponent()
        {
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle2 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle3 = new System.Windows.Forms.DataGridViewCellStyle();
            this.dgvStudents = new Guna.UI2.WinForms.Guna2DataGridView();
            this.btnImportCsv = new Guna.UI2.WinForms.Guna2Button();
            this.cmbGroupFilter = new Guna.UI2.WinForms.Guna2ComboBox();
            this.btnRefresh = new Guna.UI2.WinForms.Guna2Button();
            this.lblFilter = new Guna.UI2.WinForms.Guna2HtmlLabel();
            this.txtSearch = new Guna.UI2.WinForms.Guna2TextBox();
            ((System.ComponentModel.ISupportInitialize)(this.dgvStudents)).BeginInit();
            this.SuspendLayout();
            // 
            // dgvStudents
            // 
            this.dgvStudents.AllowUserToAddRows = false;
            this.dgvStudents.AllowUserToDeleteRows = false;
            dataGridViewCellStyle1.BackColor = System.Drawing.Color.White;
            this.dgvStudents.AlternatingRowsDefaultCellStyle = dataGridViewCellStyle1;
            dataGridViewCellStyle2.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
            dataGridViewCellStyle2.BackColor = System.Drawing.Color.FromArgb(100, 88, 255);
            dataGridViewCellStyle2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(204)));
            dataGridViewCellStyle2.ForeColor = System.Drawing.Color.White;
            dataGridViewCellStyle2.SelectionBackColor = System.Drawing.SystemColors.Highlight;
            dataGridViewCellStyle2.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
            dataGridViewCellStyle2.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
            this.dgvStudents.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle2;
            this.dgvStudents.ColumnHeadersHeight = 40;
            dataGridViewCellStyle3.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
            dataGridViewCellStyle3.BackColor = System.Drawing.Color.White;
            dataGridViewCellStyle3.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(204)));
            dataGridViewCellStyle3.ForeColor = System.Drawing.Color.FromArgb(71, 69, 94);
            dataGridViewCellStyle3.SelectionBackColor = System.Drawing.Color.FromArgb(231, 229, 255);
            dataGridViewCellStyle3.SelectionForeColor = System.Drawing.Color.FromArgb(71, 69, 94);
            dataGridViewCellStyle3.WrapMode = System.Windows.Forms.DataGridViewTriState.False;
            this.dgvStudents.DefaultCellStyle = dataGridViewCellStyle3;
            this.dgvStudents.GridColor = System.Drawing.Color.FromArgb(230, 230, 230);
            this.dgvStudents.Location = new System.Drawing.Point(20, 60);
            this.dgvStudents.Name = "dgvStudents";
            this.dgvStudents.ReadOnly = true;
            this.dgvStudents.RowHeadersVisible = false;
            this.dgvStudents.RowTemplate.Height = 30;
            this.dgvStudents.Size = new System.Drawing.Size(760, 420);
            this.dgvStudents.TabIndex = 0;
            // 
            // btnImportCsv
            // 
            this.btnImportCsv.BorderRadius = 10;
            this.btnImportCsv.FillColor = System.Drawing.Color.FromArgb(40, 167, 69);
            this.btnImportCsv.Font = new System.Drawing.Font("Segoe UI", 9F);
            this.btnImportCsv.ForeColor = System.Drawing.Color.White;
            this.btnImportCsv.Location = new System.Drawing.Point(620, 12);
            this.btnImportCsv.Name = "btnImportCsv";
            this.btnImportCsv.Size = new System.Drawing.Size(160, 36);
            this.btnImportCsv.TabIndex = 1;
            this.btnImportCsv.Text = "📂 Импорт студентов (CSV)";
            this.btnImportCsv.Click += new System.EventHandler(this.btnImportCsv_Click);
            // 
            // cmbGroupFilter
            // 
            this.cmbGroupFilter.BackColor = System.Drawing.Color.Transparent;
            this.cmbGroupFilter.DrawMode = System.Windows.Forms.DrawMode.OwnerDrawFixed;
            this.cmbGroupFilter.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.cmbGroupFilter.FocusedColor = System.Drawing.Color.Empty;
            this.cmbGroupFilter.Font = new System.Drawing.Font("Segoe UI", 9F);
            this.cmbGroupFilter.ForeColor = System.Drawing.Color.FromArgb(68, 88, 112);
            this.cmbGroupFilter.ItemHeight = 30;
            this.cmbGroupFilter.Location = new System.Drawing.Point(120, 12);
            this.cmbGroupFilter.Name = "cmbGroupFilter";
            this.cmbGroupFilter.Size = new System.Drawing.Size(180, 36);
            this.cmbGroupFilter.TabIndex = 2;
            this.cmbGroupFilter.SelectedIndexChanged += new System.EventHandler(this.cmbGroupFilter_SelectedIndexChanged);
            // 
            // btnRefresh
            // 
            this.btnRefresh.BorderRadius = 10;
            this.btnRefresh.FillColor = System.Drawing.Color.FromArgb(52, 73, 94);
            this.btnRefresh.Font = new System.Drawing.Font("Segoe UI", 9F);
            this.btnRefresh.ForeColor = System.Drawing.Color.White;
            this.btnRefresh.Location = new System.Drawing.Point(320, 12);
            this.btnRefresh.Name = "btnRefresh";
            this.btnRefresh.Size = new System.Drawing.Size(100, 36);
            this.btnRefresh.TabIndex = 3;
            this.btnRefresh.Text = "Обновить";
            this.btnRefresh.Click += new System.EventHandler(this.btnRefresh_Click);
            // 
            // lblFilter
            // 
            this.lblFilter.BackColor = System.Drawing.Color.Transparent;
            this.lblFilter.Font = new System.Drawing.Font("Segoe UI", 9F, System.Drawing.FontStyle.Bold);
            this.lblFilter.Location = new System.Drawing.Point(20, 22);
            this.lblFilter.Name = "lblFilter";
            this.lblFilter.Size = new System.Drawing.Size(109, 17);
            this.lblFilter.TabIndex = 4;
            this.lblFilter.Text = "Фильтр по группе:";
            // 
            // txtSearch
            // 
            this.txtSearch.Cursor = System.Windows.Forms.Cursors.IBeam;
            this.txtSearch.DefaultText = "";
            this.txtSearch.Font = new System.Drawing.Font("Segoe UI", 9F);
            this.txtSearch.Location = new System.Drawing.Point(440, 12);
            this.txtSearch.Name = "txtSearch";
            this.txtSearch.PlaceholderText = "🔍 Поиск по фамилии...";
            this.txtSearch.Size = new System.Drawing.Size(150, 36);
            this.txtSearch.TabIndex = 5;
            this.txtSearch.TextChanged += new System.EventHandler(this.txtSearch_TextChanged);
            // 
            // StudentForm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.BackColor = System.Drawing.Color.FromArgb(240, 242, 245);
            this.ClientSize = new System.Drawing.Size(800, 500);
            this.Controls.Add(this.txtSearch);
            this.Controls.Add(this.lblFilter);
            this.Controls.Add(this.btnRefresh);
            this.Controls.Add(this.cmbGroupFilter);
            this.Controls.Add(this.btnImportCsv);
            this.Controls.Add(this.dgvStudents);
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
            this.MaximizeBox = false;
            this.Name = "StudentForm";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent;
            this.Text = "Управление студентами и средний балл";
            ((System.ComponentModel.ISupportInitialize)(this.dgvStudents)).EndInit();
            this.ResumeLayout(false);
            this.PerformLayout();
        }
    }
}