using Npgsql;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
using static System.Collections.Specialized.BitVector32;

namespace WindowsFormsApp4
{
    public partial class bibliot : Form
    {
        readonly string connStr = ConfigurationManager.ConnectionStrings["PostgreSQL"].ConnectionString;
        public bibliot()
        {
            InitializeComponent();

        }

        private void bibliot_Load(object sender, EventArgs e)
        {

            LoadLibraryInfo();
            LoadStats();
            LoadActiveLoans();
        }
        private void LoadLibraryInfo()
        {
            string query = "SELECT name FROM libraries WHERE id = @libId";
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("libId", Session.LibraryId);
                conn.Open();
                var result = cmd.ExecuteScalar();
                labelLibraryName.Text = result != null ? result.ToString() : "Неизвестная библиотека";
            }
        }

        private void LoadStats()
        {
            string readersQuery = "SELECT COUNT(*) FROM users WHERE library_id = @libId AND role_name = 'reader'";
            string debtorsQuery = @"
                SELECT COUNT(DISTINCT u.id)
                FROM users u
                JOIN orders o ON u.id = o.user_id
                WHERE u.library_id = @libId
                  AND u.role_name = 'reader'
                  AND o.fact_return_date IS NULL
                  AND o.return_date < CURRENT_DATE";

            using (var conn = new NpgsqlConnection(connStr))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(readersQuery, conn))
                {
                    cmd.Parameters.AddWithValue("libId", Session.LibraryId);
                    var readersCount = cmd.ExecuteScalar();
                    labelReadersCount.Text = readersCount != null ? readersCount.ToString() : "0";
                }
                using (var cmd = new NpgsqlCommand(debtorsQuery, conn))
                {
                    cmd.Parameters.AddWithValue("libId", Session.LibraryId);
                    var debtorsCount = cmd.ExecuteScalar();
                    labelDebtorsCount.Text = debtorsCount != null ? debtorsCount.ToString() : "0";
                }
            }
        }

        private void LoadActiveLoans()
        {
            string query = @"
        SELECT 
            o.d AS order_id,
            u.name || ' ' || u.lastname AS reader_name,
            b.title AS book_title,
            be.isbn AS inventory_number,
            o.give_date,
            o.return_date,
            CASE WHEN o.return_date < CURRENT_DATE THEN 'Просрочено' ELSE 'Активно' END AS status
        FROM orders o
        JOIN users u ON o.user_id = u.id
        JOIN book_exemplar be ON o.book_exemplar = be.isbn
        JOIN book b ON be.book_id = b.id
        WHERE o.fact_return_date IS NULL
          AND u.library_id = @libId
        ORDER BY o.return_date";

            DataTable dt = new DataTable();
            using (var conn = new NpgsqlConnection(connStr))
            using (var cmd = new NpgsqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("libId", Session.LibraryId);
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            dataGridViewActiveLoans.DataSource = dt;
            if (dataGridViewActiveLoans.Columns["order_id"] != null)
                dataGridViewActiveLoans.Columns["order_id"].Visible = false;
        }


        private void buttonReturnBook_Click_1(object sender, EventArgs e)
        {
            ReturnBookForm returnForm = new ReturnBookForm();
            returnForm.FormClosed += (s, args) => { LoadStats(); LoadActiveLoans(); };
            returnForm.ShowDialog();
        }

        private void buttonIssueBook_Click_1(object sender, EventArgs e)
        {
            IssueBookForm issueForm = new IssueBookForm();
            issueForm.FormClosed += (s, args) => { LoadStats(); LoadActiveLoans(); };
            issueForm.ShowDialog();
        }

        private void buttonQuickRegister_Click(object sender, EventArgs e)
        {
            Register regForm = new Register();
            regForm.FormClosed += (s, args) => { LoadStats(); LoadActiveLoans(); };
            regForm.ShowDialog();
        }

        private void buttonAddBook_Click(object sender, EventArgs e)
        {
            using (var addForm = new AddExemplarsForm())
            {
                addForm.ShowDialog();
            }
        }

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

            this.Close();
        }
    }
}