using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml; // 引入 EPPlus 套件
using WebApi_data_value.Models; // 更新為正確的命名空間
using Microsoft.EntityFrameworkCore;
using Parking_space_WebAPI.Services;

namespace WebApi_data_value.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ExcelController : ControllerBase
    {
        private readonly SqlContext _context;

        public ExcelController(SqlContext context)
        {
            _context = context;
        }

        // GET: api/Excel/Download
        [HttpGet("Download")]
        public async Task<IActionResult> DownloadExcel()
        {
            // 從資料庫中讀取 yuntech_parking 表的數據
            var parkingSpaces = await _context.yuntech_parking.ToListAsync();

            if (parkingSpaces == null || !parkingSpaces.Any())
            {
                return NotFound("No parking spaces data found.");
            }

            // 從資料庫中讀取月租車與臨停車的數據
            var monthlyRent = await _context.yuntech_monthly_rent_number
                .FirstOrDefaultAsync(x => x.category == "月租");
            var temporaryParking = await _context.yuntech_monthly_rent_number
                .FirstOrDefaultAsync(x => x.category == "臨停");

            if (monthlyRent == null || temporaryParking == null)
            {
                return NotFound("No data found for monthly rent or temporary parking.");
            }

            var file = GenerateExcel(parkingSpaces, monthlyRent, temporaryParking);
            var fileName = $"ParkingSpaces_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
            return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
        }

        private byte[] GenerateExcel(IEnumerable<Yuntech_parking> parkingData, Yuntech_monthly_rent_number monthlyRent, Yuntech_monthly_rent_number temporaryParking)
        {
            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("Yuntech Parking");

            // 設定標題
            worksheet.Cells[1, 1].Value = "總車位";
            worksheet.Cells[1, 2].Value = "剩餘車位";
            worksheet.Cells[1, 3].Value = "月租車數量";
            worksheet.Cells[1, 4].Value = "臨停數量";

            // 添加下載時間
            worksheet.Cells[1, 6].Value = "下載時間";
            worksheet.Cells[2, 6].Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

            var row = 2;

            // 添加每一行的數據
            foreach (var item in parkingData)
            {
                worksheet.Cells[2, 1].Value = item.all_num;
                worksheet.Cells[2, 2].Value = item.now_num;
                row++;
            }

            // 添加月租車與臨停車的數據
            worksheet.Cells[2, 3].Value = monthlyRent.number;
            worksheet.Cells[2, 4].Value = temporaryParking.number;

            return package.GetAsByteArray();
        }
    }
}