using Npgsql;
using System;
using System.Configuration;
using System.Data;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using System.IO;
using OfficeOpenXml;

namespace WindowsFormsApp4
{
    public partial class AdminMainForm : Form
    {
        private readonly string connStr = ConfigurationManager.ConnectionStrings["PostgreSQL"].ConnectionString;

        public AdminMainForm()
        {
            ExcelPackage.License.SetNonCommercialPersonal("Ваше Имя");

            InitializeComponent();
        }

        private void AdminMainForm_Load(object sender, EventArgs e)
        {
            labelWelcome.Text = $"Админ: {Session.UserFullName}";
            LoadLogs();
            LoadUsers();
            dateTimePickerEnd.Value = DateTime.Now;
            dateTimePickerStart.Value = DateTime.Now.AddDays(-30);
            LoadStatistics();
        }

        private void LoadLogs()
        {
            string query = @"
                SELECT 
                    l.id,
                    l.action_time,
                    l.user_name,
                    l.table_name,
                    l.action,
                    l.record_id
                FROM logs l
                ORDER BY l.action_time DESC";
            DataTable dt = new DataTable();
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            dataGridViewLogs.DataSource = dt;
        }

        private void btnExportLogs_Click(object sender, EventArgs e)
        {
            ExportDataGridViewToExcel(dataGridViewLogs, "Логи_системы");
        }
        private void LoadUsers()
        {
            string filter = cmbRoleFilter.SelectedItem?.ToString();
            string query = @"
                SELECT 
                    u.id,
                    u.name,
                    u.lastname,
                    mask_sensitive_data(u.phone),
                    mask_sensitive_data(u.email),
                    u.role_name AS role,
                    u.bilet_active,
                    l.name AS library_name
                FROM users u
                LEFT JOIN libraries l ON u.library_id = l.id
                WHERE 1=1";
            if (!string.IsNullOrEmpty(filter) && filter != "Все")
            {
                query += " AND u.role_name = @role::role";
            }
            query += " ORDER BY u.id";
            DataTable dt = new DataTable();
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                if (!string.IsNullOrEmpty(filter) && filter != "Все")
                    cmd.Parameters.AddWithValue("role", filter);
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            dataGridViewUsers.DataSource = dt;
            if (dataGridViewUsers.Columns["id"] != null)
                dataGridViewUsers.Columns["id"].Visible = false;
        }

        private void cmbRoleFilter_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadUsers();
        }

        private void btnAddUser_Click(object sender, EventArgs e)
        {
            using (var addForm = new AdminUserEditForm(null))
            {
                if (addForm.ShowDialog() == DialogResult.OK)
                    LoadUsers();
            }
        }

        private void btnEditUser_Click(object sender, EventArgs e)
        {
            if (dataGridViewUsers.CurrentRow == null)
            {
                MessageBox.Show("Выберите пользователя для редактирования");
                return;
            }
            int userId = Convert.ToInt32(dataGridViewUsers.CurrentRow.Cells["id"].Value);
            AdminUserEditForm editForm = new AdminUserEditForm(userId);
            editForm.FormClosed += (s, args) => LoadUsers();
            editForm.ShowDialog();
        }

        private void btnDeleteUser_Click(object sender, EventArgs e)
        {
            if (dataGridViewUsers.CurrentRow == null)
            {
                MessageBox.Show("Выберите пользователя для удаления");
                return;
            }
            int userId = Convert.ToInt32(dataGridViewUsers.CurrentRow.Cells["id"].Value);
            string role = dataGridViewUsers.CurrentRow.Cells["role"].Value.ToString();
            if (role == "admin")
            {
                MessageBox.Show("Нельзя удалить администратора через интерфейс.");
                return;
            }
            if (MessageBox.Show("Удалить пользователя? Это действие необратимо.", "Подтверждение", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                try
                {
                    using (var conn = new NpgsqlConnection(connStr))
                    {
                        conn.Open();
                        string query = "DELETE FROM users WHERE id = @id";
                        using (var cmd = new NpgsqlCommand(query, conn))
                        {
                            cmd.Parameters.AddWithValue("id", userId);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    LoadUsers();
                    MessageBox.Show("Пользователь удалён.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Ошибка: {ex.Message}");
                }
            }
        }

        private void LoadStatistics()
        {
            DateTime start = dateTimePickerStart.Value.Date;
            DateTime end = dateTimePickerEnd.Value.Date.AddDays(1).AddSeconds(-1);
            LoadTopBooks(start, end);
            LoadChart(start, end);
        }

        private void LoadTopBooks(DateTime start, DateTime end)
        {
            string query = @"
                SELECT 
                    b.title AS Название,
                    COUNT(o.d) AS Выдано
                FROM orders o
                JOIN book_exemplar be ON o.book_exemplar = be.isbn
                JOIN book b ON be.book_id = b.id
                WHERE o.give_date BETWEEN @start AND @end
                GROUP BY b.id, b.title
                ORDER BY Выдано DESC
                LIMIT 10";
            DataTable dt = new DataTable();
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("end", end);
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            dataGridViewTopBooks.DataSource = dt;
        }

        private void LoadChart(DateTime start, DateTime end)
        {
            string query = @"
                SELECT 
                    DATE(o.give_date) AS Дата,
                    COUNT(*) AS Количество
                FROM orders o
                WHERE o.give_date BETWEEN @start AND @end
                GROUP BY DATE(o.give_date)
                ORDER BY Дата";
            DataTable dt = new DataTable();
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("start", start);
                cmd.Parameters.AddWithValue("end", end);
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            chartPopularBooks.Series.Clear();
            Series series = new Series("Выдачи");
            series.ChartType = SeriesChartType.Column;
            series.XValueMember = "Дата";
            series.YValueMembers = "Количество";
            chartPopularBooks.DataSource = dt;
            chartPopularBooks.Series.Add(series);
            chartPopularBooks.ChartAreas[0].AxisX.Title = "Дата";
            chartPopularBooks.ChartAreas[0].AxisY.Title = "Количество выдач";
            chartPopularBooks.DataBind();
        }

        private void btnRefreshStats_Click(object sender, EventArgs e)
        {
            LoadStatistics();
        }

        private void btnExportExcel_Click(object sender, EventArgs e)
        {
            ExportDataGridViewToExcel(dataGridViewTopBooks, "Топ_книг");
        }

        private void ExportDataGridViewToExcel(DataGridView dgv, string fileName)
        {
            if (dgv.Rows.Count == 0)
            {
                MessageBox.Show("Нет данных для экспорта.");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
            saveFileDialog.FileName = $"{fileName}_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                using (ExcelPackage package = new ExcelPackage())
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
                    for (int i = 1; i <= dgv.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i].Value = dgv.Columns[i - 1].HeaderText;
                        worksheet.Cells[1, i].Style.Font.Bold = true;
                        worksheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                    }
                    for (int i = 0; i < dgv.Rows.Count; i++)
                    {
                        for (int j = 0; j < dgv.Columns.Count; j++)
                        {
                            var value = dgv.Rows[i].Cells[j].Value;
                            worksheet.Cells[i + 2, j + 1].Value = value != null ? value.ToString() : "";
                        }
                    }
                    worksheet.Cells.AutoFitColumns();
                    package.SaveAs(new FileInfo(saveFileDialog.FileName));
                }
                MessageBox.Show("Экспорт завершён.");
            }
        }

        private void btnLogout_Click(object sender, EventArgs e)
        {
            Session.UserId = 0;
            Session.UserRole = null;
            Session.UserFullName = null;
            Session.LibraryId = null;

            this.Close(); 
        }

        private void dataGridViewLogs_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
        
        private void dataGridViewUsers_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                int userId = Convert.ToInt32(dataGridViewUsers.Rows[e.RowIndex].Cells["id"].Value);
                using (var editForm = new AdminUserEditForm(userId))
                {
                    if (editForm.ShowDialog() == DialogResult.OK)
                        LoadUsers(); 
                }
            }
        }

        private void chartPopularBooks_Click(object sender, EventArgs e)
        {

        }

        private void dataGridViewTopBooks_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dataGridViewUsers_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}