using System.IO;
using OfficeOpenXml;
using System.Collections.Generic;
using System.Linq;

namespace RevitEstimate.PlumbingModule
{
    public static class PlumbingExcelExporter
    {
        public static void GenerateIntPlumExcel(string filePath, List<RoomFixtureData> rooms, double tankElevation)
        {
            // Note: EPPlus v4.5.3.3 does not require LicenseContext setting, so it's removed to prevent errors.

            using (var package = new ExcelPackage())
            {
                var ws = package.Workbook.Worksheets.Add("INT. PLUM.");

                ws.Cells["C1"].Value = "Internal Plumbing Estimation & Measurement Sheet (Complete DSR)";
                ws.Cells["C1"].Style.Font.Bold = true;
                ws.Cells["C1"].Style.Font.Size = 14;

                ws.Cells["B3"].Value = "Sr. No.";
                ws.Cells["C3"].Value = "Description";
                ws.Cells["D3"].Value = "Unit";
                ws.Cells["E3"].Value = "Qty";
                ws.Cells["B3:E3"].Style.Font.Bold = true;

                int totalWCs = rooms.Sum(r => r.WaterClosetQty);
                int totalHealthFaucets = rooms.Sum(r => r.HealthFaucetQty);
                int totalWashBasins = rooms.Sum(r => r.WashBasinQty);
                int totalKitchenSinks = rooms.Sum(r => r.KitchenSinkQty);
                int totalShowers = rooms.Sum(r => r.ShowerQty);
                int totalUrinals = rooms.Sum(r => r.UrinalQty);
                int totalPurifiers = rooms.Sum(r => r.WaterPurifierQty);
                int totalCoolers = rooms.Sum(r => r.WaterCoolerQty);
                int totalGeysers = rooms.Sum(r => r.GeyserQty);

                int totalAngleCocks = rooms.Sum(r => r.AutoAngleCockQty);
                int totalLongBody = rooms.Sum(r => r.AutoLongBodyQty);
                int totalBibCocks = rooms.Sum(r => r.AutoBibCockQty);
                int totalBottleTraps = rooms.Sum(r => r.AutoBottleTrapQty);

                int row = 5;

                void AddSubHead(string title)
                {
                    row++;
                    ws.Cells[row, 3].Value = title;
                    ws.Cells[row, 3].Style.Font.Bold = true;
                    row += 2;
                }

                void AddRow(int sr, string desc, string unit, double qty)
                {
                    ws.Cells[row, 2].Value = sr;
                    ws.Cells[row, 3].Value = desc;
                    ws.Cells[row, 4].Value = unit;
                    ws.Cells[row, 5].Value = qty;
                    row++;
                }

                AddSubHead("SUB HEAD-I-SANITARY INSTALLATIONS");
                AddRow(1, "White Vitreous china Orissa pattern W.C. pan of size 580x440 mm with integral type foot rests", "Each", 0);
                AddRow(2, "W.C. pan with ISI marked white solid plastic seat and lid", "Each", 0);
                AddRow(3, "Providing and fixing white vitreous china extended wall mounting type water closet", "Each", totalWCs);
                AddRow(4, "Providing & fixing toilet paper holder (C.P. brass)", "Each", totalWCs);
                AddRow(5, "Providing and fixing 8 mm dia C.P. / S.S. Jet with flexible tube upto 1 metre long", "Each", totalWCs);
                AddRow(6, "Providing and fixing Jaquar Make Hand Shower (Health Faucet) with 8mm Dia, 1.2 Meter Long Flexible Tube", "Each", totalHealthFaucets);
                AddRow(7, "White Vitreous China Wash basin size 630x450 mm with a single 15 mm C.P. brass pillar tap", "Each", totalWashBasins);
                AddRow(8, "White Vitreous China Wash basin size 550x400 mm with a 15 mm CP Brass single hole basin mixer", "Each", 0);
                AddRow(9, "Providing and fixing CP Brass 32mm size Bottle Trap of approved quality & make", "Each", totalBottleTraps);
                AddRow(10, "Providing and fixing PTMT liquid soap container 109 mm wide, 125 mm high", "Each", totalWashBasins);
                AddRow(11, "Providing and fixing PTMT towel ring trapezoidal shape 215 mm long, 200 mm wide", "Each", totalWashBasins);
                AddRow(12, "Providing and fixing 600x450 mm beveled edge mirror of superior glass complete with 6 mm thick hard board", "Each", totalWashBasins);
                AddRow(13, "Providing and fixing C.P. brass angle valve for basin mixer and geyser points (15 mm nominal bore)", "Each", totalAngleCocks);
                AddRow(14, "Providing and fixing uplasticised PVC connection pipe with brass unions (45 cm length, 15 mm nominal bore)", "Each", totalAngleCocks);
                AddRow(15, "Providing and fixing C.P. brass shower rose with 15 or 20 mm inlet (100 mm diameter)", "Each", totalShowers);
                AddRow(16, "Providing and fixing C.P. brass stop cock (concealed) of standard design (15 mm nominal bore)", "Each", totalShowers);
                AddRow(17, "Providing and fixing CP Brass Single lever telephonic wall mixer", "Each", 0);
                AddRow(18, "Providing and fixing PTMT towel rail complete with brackets (600 mm long towel rail)", "Each", rooms.Count);
                AddRow(19, "Providing and fixing PTMT soap Dish Holder having length of 138mm", "Each", totalShowers);
                AddRow(20, "Kitchen sink with drain board 510x1040 mm bowl depth 200 mm", "Each", totalKitchenSinks);
                AddRow(21, "Kitchen sink without drain board 610x510 mm bowl depth 200 mm", "Each", 0);
                AddRow(22, "Providing & fixing PVC waste pipe for sink including PVC waste fittings Complete (Flexible Pipe 32mm Dia)", "Each", totalKitchenSinks);
                AddRow(23, "Providing and fixing C.P. brass bib cock of approved quality (15mm nominal bore)", "Each", totalBibCocks);
                AddRow(24, "Providing and fixing C.P. brass long body bib cock of approved quality (15mm nominal bore)", "Each", totalLongBody);
                AddRow(25, "Providing and fixing C.P. Brass extension nipple (size 15 mm x 50 mm)", "Each", totalAngleCocks + totalLongBody);
                AddRow(26, "Providing and fixing white vitreous china battery based infrared sensor operated urinal", "Each", totalUrinals);
                AddRow(27, "Providing and fixing stone slab for urinal partitions (Granite Stone of approved shade)", "sqm", totalUrinals > 0 ? (totalUrinals - 1) * 0.375 : 0);
                AddRow(28, "Providing and fixing of Kent Elite Plus Water Purifier (RO+UV+UF+TDS Control)", "Each", totalPurifiers);
                AddRow(29, "Providing and fixing of Water Cooler (Voltas Make)", "Each", totalCoolers);
                AddRow(30, "Providing and fixing of storage type electric hot water heater (Capacity 25 Liters)", "Each", totalGeysers);

                AddSubHead("SUB HEAD-II-WATER SUPPLY");
                double totalConcealedCPVC = rooms.Count * 6.5;
                double totalExposedCPVC = rooms.Count * 12.0;
                double giLoopLength = 33.0;

                AddRow(31, "Providing and fixing CPVC pipes Concealed work - For Internal Toilet - 15 mm nominal outer dia Pipes", "Metre", totalConcealedCPVC * 0.4);
                AddRow(32, "Providing and fixing CPVC pipes Concealed work - For Internal Toilet - 20 mm nominal outer dia Pipes", "Metre", totalConcealedCPVC * 0.6);
                AddRow(33, "Providing and fixing CPVC pipes Concealed work - For Internal Toilet - 25 mm nominal outer dia Pipes", "Metre", totalConcealedCPVC * 0.2);
                AddRow(34, "Providing and fixing CPVC pipes Concealed work - For Internal Toilet - 32 mm nominal outer dia Pipes", "Metre", 0);
                AddRow(35, "Providing and fixing CPVC pipes Internal work - Exposed on wall - 20 mm nominal bore", "Metre", totalExposedCPVC * 0.5);
                AddRow(36, "Providing and fixing CPVC pipes Internal work - Exposed on wall - 25 mm nominal bore", "Metre", totalExposedCPVC * 0.5);
                AddRow(37, "Providing and fixing CPVC pipes Internal work - Exposed on wall - 32 mm nominal bore", "Metre", 0);
                AddRow(38, "Providing and fixing CPVC pipes Internal work - Exposed on wall - 40 mm nominal bore", "Metre", 0);
                AddRow(39, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 20 mm nominal bore", "Metre", 0);
                AddRow(40, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 25 mm nominal bore", "Metre", 0);
                AddRow(41, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 32 mm nominal bore", "Metre", 0);
                AddRow(42, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 40 mm nominal bore", "Metre", 0);
                AddRow(43, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 50 mm nominal bore", "Metre", 0);
                AddRow(44, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 65 mm nominal bore", "Metre", giLoopLength);
                AddRow(45, "Providing and fixing GI pipes complete with GI fittings - Terrace loop & Tank Filling Pipe - 80 mm nominal bore", "Metre", 0);
                AddRow(46, "Painting G.I. pipes and fittings with two coats of anti-corrosive bitumastic paint - 65mm diameter pipe", "Metre", giLoopLength);
                AddRow(47, "Providing and fixing G.I. Union in G.I. pipe including cutting and threading - 20mm diameter pipe", "Each", 0);
                AddRow(48, "Providing and fixing G.I. Union in G.I. pipe including cutting and threading - 25mm diameter pipe", "Each", 0);
                AddRow(49, "Providing and fixing G.I. Union in G.I. pipe including cutting and threading - 65mm diameter pipe", "Each", giLoopLength > 0 ? (int)(giLoopLength / 6.0) + 1 : 0);

                AddSubHead("SUB HEAD-III-DRAINAGE & SANITARY DISPOSAL (SWR PIPES)");
                AddRow(50, "Providing and fixing Sand Cast Iron / SWR PVC pipes (Soil, Waste and Vent pipes) - 75 mm dia", "Metre", rooms.Count * 3.0);
                AddRow(51, "Providing and fixing Sand Cast Iron / SWR PVC pipes (Soil, Waste and Vent pipes) - 110 mm dia", "Metre", rooms.Count * 5.0);
                AddRow(52, "Providing and fixing SWR PVC fittings (Bends, Tees, Cowls, Shoes) - 75 mm", "Each", rooms.Count * 2);
                AddRow(53, "Providing and fixing SWR PVC fittings (Bends, Tees, Cowls, Shoes) - 110 mm", "Each", rooms.Count * 3);
                AddRow(54, "Providing and fixing Floor Trap (Jali Trap) with C.P. grating - 110x75 mm", "Each", rooms.Count);

                AddSubHead("SUB HEAD-IV-RAIN WATER & STORMWATER DRAINAGE");
                AddRow(55, "Providing and fixing Rain Water pipes (UPVC / SWR) on external walls - 110 mm diameter", "Metre", 25.0);
                AddRow(56, "Providing and fixing Rain Water pipes (UPVC / SWR) on external walls - 160 mm diameter", "Metre", 0);
                AddRow(57, "Providing and fixing Rain Water outlet cowls / Rain water heads at terrace level", "Each", 4);
                AddRow(58, "Construction of Brick Masonry inspection chamber / Manhole with RCC cover", "Each", 2);

                ws.Columns.AutoFit();
                File.WriteAllBytes(filePath, package.GetAsByteArray());
            }
        }
    }
}