import scrapy
from scrapy import Request
from openpyxl import Workbook


class SolKeySpider(scrapy.Spider):
    name = "SolKeySpider"
    start_urls = ['https://www.duurzaamloket.nl/SolKey_X014/index.php?SchemeNo=0&Offset=1&SearchText=&PageCnt=448']

    def parse(self, response):
        wb = Workbook()
        ws = wb.active
        ws.append(["Manufacturer", "Model", "License No", "Type", "Collector Area", "Efficiency"])

        for tr in response.xpath("//table[@id='LicTbl']/tbody/tr"):
            link = tr.xpath(".//td[2]/a/@href").get()
            id = link.split("ID=")[1].split("&")[0]
            manufacturer = tr.xpath(".//td[2]/a/text()").get().strip()
            model = tr.xpath(".//td[3]/text()").get().strip()
            license_no = tr.xpath(".//td[4]/text()").get().strip()
            type = tr.xpath(".//td[5]/text()").get().strip()
            collector_area = tr.xpath(".//td[6]/text()").get().strip()
            efficiency = tr.xpath(".//td[7]/text()").get().strip()

            yield Request(
                url=f"https://www.duurzaamloket.nl/SolKey_X014/Spec_Collector.php?ID={id}&SchemeNo=0&Offset=1&SearchText=&PageCnt=448#",
                callback=self.parse_details,
                meta={
                    'wb': wb,
                    'ws': ws,
                    'manufacturer': manufacturer,
                    'model': model,
                    'license_no': license_no,
                    'type': type,
                    'collector_area': collector_area,
                    'efficiency': efficiency
                }
            )

    def parse_details(self, response):
        wb = response.meta['wb']
        ws = response.meta['ws']
        manufacturer = response.meta['manufacturer']
        model = response.meta['model']
        license_no = response.meta['license_no']
        type = response.meta['type']
        collector_area = response.meta['collector_area']
        efficiency = response.meta['efficiency']

        for tr in response.xpath("//table[@class='LicDetails']/tbody/tr"):
            key = tr.xpath(".//td[1]/text()").get().strip()
            value = tr.xpath(".//td[2]/text()").get().strip()

            if key == "Test report number":
                test_report_number = value
            elif key == "Test Standard":
                test_standard = value

        ws.append([manufacturer, model, license_no, type, collector_area, efficiency, test_report_number, test_standard])
        wb.save("solkey_results.xlsx")