"""
Report Handler
"""

from typing import Optional

import logging
from datetime import datetime, timedelta
from pathlib import Path

import typer

from laceworkreports import common
from laceworkreports.sdk.DataHandlers import DataHandlerTypes, ExportHandler
from laceworkreports.sdk.ReportHelpers import (
    ReportHelper,
    ReportSeverityTypes,
    VulnerabilityQueries,
)

app = typer.Typer(no_args_is_help=True)


@app.command(no_args_is_help=True, help="Generate HTML report")
def html(
    ctx: typer.Context,
    start_time: datetime = typer.Option(
        (datetime.utcnow() - timedelta(hours=25)).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="Start time for query period",
    ),
    end_time: datetime = typer.Option(
        (datetime.utcnow()).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="End time for query period",
    ),
    package_active: bool = typer.Option(
        True, help="Return only active package vulnerabilities"
    ),
    fixable: bool = typer.Option(True, help="Return only fixable vulnerabilities"),
    severity: ReportSeverityTypes = typer.Option(
        ReportSeverityTypes.HIGH.value,
        help="Return only vulnerabilities with at or above the severity threshold",
    ),
    namespace: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching package namespace",
    ),
    cve: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching cve",
    ),
    subaccounts: bool = typer.Option(
        False,
        help="Enumerate subaccounts",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    file_path: str = typer.Option(
        ...,
        help="Path to exported result",
        envvar=common.LACEWORK_REPORTS_FILE_PATH,
    ),
    template_path: str = typer.Option(
        Path(__file__).resolve().parent.joinpath("vulnerability_coverage.html.j2"),
        help="Path to jinja2 template. Results will be passed as 'dataset' variable.",
        envvar=common.LACEWORK_REPORTS_TEMPLATE_PATH,
    ),
    ignore_errors: bool = typer.Option(
        True,
        help="Ignore error for missing reports or inaccessible account details.",
    ),
) -> None:
    """
    Set the command context
    """

    # connect lacework client
    lw = common.config.connect()

    # report details
    report_title = "Vulnerability Coverage"
    db_table = "vulnerability_coverage"

    reportHelper = ReportHelper()
    db_path = Path("database.db")
    # db_path.unlink(missing_ok=True)
    db_connection = f"sqlite:///{db_path.absolute()}?check_same_thread=False"

    reportHelper.sqlite_drop_table(db_table, db_connection)
    reportHelper.sqlite_drop_table("active_cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("machines", db_connection)
    reportHelper.sqlite_drop_table("cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("instances", db_connection)

    has_subaccounts = False
    if subaccounts:
        lwAccounts = reportHelper.get_subaccounts(client=lw)
        if len(lwAccounts) == 0:
            logging.error("Subaccounts specificed but none found")
            raise Exception("Subaccounts specificed but none found")
        else:
            has_subaccounts = True
    else:
        lwAccounts = [{"accountName": lw._account}]

    lacework_account_count = 0
    for lwAccount in lwAccounts:
        lacework_account_count += 1
        if has_subaccounts:
            logging.info(f"Switching to subaccount context: {lwAccount['accountName']}")
            lw.set_subaccount(lwAccount["accountName"])

        # sync cloud accounts with deployed agents
        logging.info("Syncing cloud accounts with deployed agents")
        reportHelper.get_active_cloud_accounts(
            client=lw,
            lwAccount=lwAccount["accountName"],
            start_time=start_time,
            end_time=end_time,
            use_sqlite=True,
            db_table="active_cloud_accounts",
            db_connection=db_connection,
        )
        try:
            result = reportHelper.sqlite_queries(
                queries={
                    "cloud_account_query": """
                                    SELECT 
                                        DISTINCT ACCOUNTID
                                    FROM 
                                        :db_table
                                    WHERE 
                                        ACCOUNTID IS NOT NULL
                                    """
                },
                db_connection=db_connection,
                db_table="active_cloud_accounts",
            )
            active_cloud_accounts = [
                x["ACCOUNTID"] for x in result["cloud_account_query"]
            ]
        except Exception:
            active_cloud_accounts = []

        # get cloud accounts and sync to sqlite
        cloud_accounts = reportHelper.get_cloud_accounts(
            client=lw, lwAccount=lwAccount["accountName"]
        )
        ExportHandler(
            format=DataHandlerTypes.SQLITE,
            results=[{"data": cloud_accounts}],
            file_path=file_path,
            db_table="cloud_accounts",
            db_connection=db_connection,
        ).export()

        logging.info(
            f"Discovered {len(active_cloud_accounts)} cloud accounts with agents deployed: {active_cloud_accounts}"
        )

        for cloud_account in cloud_accounts:

            if (
                cloud_account["enabled"] == 1
                and cloud_account["accountId"] in active_cloud_accounts
            ):
                # sync machines for this cloud account
                logging.info(
                    f"Syncing machines for {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_active_machines(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    start_time=start_time,
                    end_time=end_time,
                    use_sqlite=True,
                    db_table="machines",
                    db_connection=db_connection,
                )

                logging.info(
                    f"Enumerating {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_vulnerability_report(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    ignore_errors=ignore_errors,
                    start_time=start_time,
                    end_time=end_time,
                    package_active=package_active,
                    fixable=fixable,
                    namespace=namespace,
                    severity=severity,
                    cve=cve,
                    use_sqlite=True,
                    db_table=db_table,
                    db_connection=db_connection,
                )

                # ensure we have machines table if no machines were found
                if not reportHelper.sqlite_table_exists(
                    db_table="machines", db_connection=db_connection
                ):
                    machines_table = """
                                        CREATE TABLE machines (
                                                "LWACCOUNT" TEXT, 
                                                "ACCOUNTID" TEXT, 
                                                "TAG_HOSTNAME" TEXT, 
                                                "TAG_INSTANCEID" TEXT, 
                                                "TAG_ACCOUNTID" TEXT, 
                                                "TAG_PROJECTID" TEXT, 
                                                "TAG_VMPROVIDER" TEXT, 
                                                "LWTOKENSHORT" TEXT
                                            )
                                        """
                    reportHelper.sqlite_execute(
                        query=machines_table, db_connection=db_connection
                    )

                # ensure we have a vulnerability_coverage table
                if not reportHelper.sqlite_table_exists(
                    db_table="vulnerability_coverage", db_connection=db_connection
                ):
                    vulnerability_coverage_table = """
                                                    CREATE TABLE vulnerability_coverage (
                                                        "cveProps" JSON, 
                                                        "endTime" TEXT, 
                                                        "featureKey" JSON, 
                                                        "fixInfo" JSON, 
                                                        "machineTags" JSON, 
                                                        mid BIGINT, 
                                                        severity TEXT, 
                                                        "startTime" TEXT, 
                                                        status TEXT, 
                                                        "vulnId" TEXT
                                                    , accountId TEXT, lwAccount TEXT)
                                                    """
                    reportHelper.sqlite_execute(
                        query=vulnerability_coverage_table, db_connection=db_connection
                    )
            else:
                logging.info(
                    f"Skipping disabled or inactive account {lwAccount['accountName']}:{cloud_account['accountId']}"
                )

    # use sqlite query to generate final result
    results = reportHelper.sqlite_queries(
        queries=VulnerabilityQueries, db_table=db_table, db_connection=db_connection
    )

    if len(results["report"]) > 0:
        report = results["report"]

        # return additional stats under summary
        stats = {}
        for key in [x for x in results.keys() if x != "report"]:
            stats[key] = results[key]

        # write jinja template
        ExportHandler(
            format=DataHandlerTypes.JINJA2,
            results=[
                {
                    "data": [
                        {
                            "name": db_table,
                            "report": report,
                            "summary": {
                                "rows": len(report),
                                "reportTitle": report_title,
                                "stats": stats,
                            },
                        }
                    ]
                }
            ],
            template_path=template_path,
            file_path=file_path,
        ).export()
    else:
        logging.warn("No report results found.")


@app.command(name="csv", no_args_is_help=True, help="Generate CSV Report")
def csv_handler(
    ctx: typer.Context,
    start_time: datetime = typer.Option(
        (datetime.utcnow() - timedelta(hours=25)).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="Start time for query period",
    ),
    end_time: datetime = typer.Option(
        (datetime.utcnow()).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="End time for query period",
    ),
    package_active: bool = typer.Option(
        True, help="Return only active package vulnerabilities"
    ),
    fixable: bool = typer.Option(True, help="Return only fixable vulnerabilities"),
    severity: ReportSeverityTypes = typer.Option(
        ReportSeverityTypes.HIGH.value,
        help="Return only vulnerabilities with at or above the severity threshold",
    ),
    namespace: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching package namespace",
    ),
    cve: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching cve",
    ),
    subaccounts: bool = typer.Option(
        False,
        help="Enumerate subaccounts",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    summary_only: bool = typer.Option(
        False,
        help="Return only summary details",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    file_path: str = typer.Option(
        ...,
        help="Path to exported result",
        envvar=common.LACEWORK_REPORTS_FILE_PATH,
    ),
    ignore_errors: bool = typer.Option(
        True,
        help="Ignore error for missing reports or inaccessible account details.",
    ),
) -> None:
    """
    Set the command context
    """

    # connect lacework client
    lw = common.config.connect()

    # report details
    report_title = "Vulnerability Coverage"
    db_table = "vulnerability_coverage"

    reportHelper = ReportHelper()
    db_path = Path("database.db")
    # db_path.unlink(missing_ok=True)
    db_connection = f"sqlite:///{db_path.absolute()}?check_same_thread=False"

    reportHelper.sqlite_drop_table(db_table, db_connection)
    reportHelper.sqlite_drop_table("active_cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("machines", db_connection)
    reportHelper.sqlite_drop_table("cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("instances", db_connection)

    has_subaccounts = False
    if subaccounts:
        lwAccounts = reportHelper.get_subaccounts(client=lw)
        if len(lwAccounts) == 0:
            logging.error("Subaccounts specificed but none found")
            raise Exception("Subaccounts specificed but none found")
        else:
            has_subaccounts = True
    else:
        lwAccounts = [{"accountName": lw._account}]

    lacework_account_count = 0
    for lwAccount in lwAccounts:
        lacework_account_count += 1
        if has_subaccounts:
            logging.info(f"Switching to subaccount context: {lwAccount['accountName']}")
            lw.set_subaccount(lwAccount["accountName"])

        # sync cloud accounts with deployed agents
        logging.info("Syncing cloud accounts with deployed agents")
        reportHelper.get_active_cloud_accounts(
            client=lw,
            lwAccount=lwAccount["accountName"],
            start_time=start_time,
            end_time=end_time,
            use_sqlite=True,
            db_table="active_cloud_accounts",
            db_connection=db_connection,
        )
        try:
            result = reportHelper.sqlite_queries(
                queries={
                    "cloud_account_query": """
                                    SELECT 
                                        DISTINCT ACCOUNTID
                                    FROM 
                                        :db_table
                                    WHERE 
                                        ACCOUNTID IS NOT NULL
                                    """
                },
                db_connection=db_connection,
                db_table="active_cloud_accounts",
            )
            active_cloud_accounts = [
                x["ACCOUNTID"] for x in result["cloud_account_query"]
            ]
        except Exception:
            active_cloud_accounts = []

        # get cloud accounts and sync to sqlite
        cloud_accounts = reportHelper.get_cloud_accounts(
            client=lw, lwAccount=lwAccount["accountName"]
        )
        ExportHandler(
            format=DataHandlerTypes.SQLITE,
            results=[{"data": cloud_accounts}],
            file_path=file_path,
            db_table="cloud_accounts",
            db_connection=db_connection,
        ).export()

        logging.info(
            f"Discovered {len(active_cloud_accounts)} cloud accounts with agents deployed: {active_cloud_accounts}"
        )

        for cloud_account in cloud_accounts:

            if (
                cloud_account["enabled"] == 1
                and cloud_account["accountId"] in active_cloud_accounts
            ):
                # sync machines for this cloud account
                logging.info(
                    f"Syncing machines for {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_active_machines(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    start_time=start_time,
                    end_time=end_time,
                    use_sqlite=True,
                    db_table="machines",
                    db_connection=db_connection,
                )

                logging.info(
                    f"Enumerating {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_vulnerability_report(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    ignore_errors=ignore_errors,
                    start_time=start_time,
                    end_time=end_time,
                    package_active=package_active,
                    fixable=fixable,
                    namespace=namespace,
                    severity=severity,
                    cve=cve,
                    use_sqlite=True,
                    db_table=db_table,
                    db_connection=db_connection,
                )

                # ensure we have machines table if no machines were found
                if not reportHelper.sqlite_table_exists(
                    db_table="machines", db_connection=db_connection
                ):
                    machines_table = """
                                        CREATE TABLE machines (
                                                "LWACCOUNT" TEXT, 
                                                "ACCOUNTID" TEXT, 
                                                "TAG_HOSTNAME" TEXT, 
                                                "TAG_INSTANCEID" TEXT, 
                                                "TAG_ACCOUNTID" TEXT, 
                                                "TAG_PROJECTID" TEXT, 
                                                "TAG_VMPROVIDER" TEXT, 
                                                "LWTOKENSHORT" TEXT
                                            )
                                        """
                    reportHelper.sqlite_execute(
                        query=machines_table, db_connection=db_connection
                    )

                # ensure we have a vulnerability_coverage table
                if not reportHelper.sqlite_table_exists(
                    db_table="vulnerability_coverage", db_connection=db_connection
                ):
                    vulnerability_coverage_table = """
                                                    CREATE TABLE vulnerability_coverage (
                                                        "cveProps" JSON, 
                                                        "endTime" TEXT, 
                                                        "featureKey" JSON, 
                                                        "fixInfo" JSON, 
                                                        "machineTags" JSON, 
                                                        mid BIGINT, 
                                                        severity TEXT, 
                                                        "startTime" TEXT, 
                                                        status TEXT, 
                                                        "vulnId" TEXT
                                                    , accountId TEXT, lwAccount TEXT)
                                                    """
                    reportHelper.sqlite_execute(
                        query=vulnerability_coverage_table, db_connection=db_connection
                    )
            else:
                logging.info(
                    f"Skipping disabled or inactive account {lwAccount['accountName']}:{cloud_account['accountId']}"
                )

    # use sqlite query to generate final result
    results = reportHelper.sqlite_queries(
        queries=VulnerabilityQueries, db_table=db_table, db_connection=db_connection
    )

    if len(results["report"]) > 0:
        if summary_only:
            report = results["account_coverage"]
        else:
            report = results["report"]

        logging.info("Building CSV from resultant data...")
        ExportHandler(
            format=DataHandlerTypes.CSV,
            results=[{"data": report}],
            file_path=file_path,
        ).export()
    else:
        logging.warn("No report results found.")


if __name__ == "__main__":
    app()

"""
Report Handler
"""

from typing import Optional

import logging
from datetime import datetime, timedelta
from pathlib import Path

import typer

from laceworkreports import common
from laceworkreports.sdk.DataHandlers import DataHandlerTypes, ExportHandler
from laceworkreports.sdk.ReportHelpers import (
    ReportHelper,
    ReportSeverityTypes,
    VulnerabilityQueries,
)

app = typer.Typer(no_args_is_help=True)


@app.command(no_args_is_help=True, help="Generate HTML report")
def html(
    ctx: typer.Context,
    start_time: datetime = typer.Option(
        (datetime.utcnow() - timedelta(hours=25)).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="Start time for query period",
    ),
    end_time: datetime = typer.Option(
        (datetime.utcnow()).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="End time for query period",
    ),
    package_active: bool = typer.Option(
        True, help="Return only active package vulnerabilities"
    ),
    fixable: bool = typer.Option(True, help="Return only fixable vulnerabilities"),
    severity: ReportSeverityTypes = typer.Option(
        ReportSeverityTypes.HIGH.value,
        help="Return only vulnerabilities with at or above the severity threshold",
    ),
    namespace: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching package namespace",
    ),
    cve: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching cve",
    ),
    subaccounts: bool = typer.Option(
        False,
        help="Enumerate subaccounts",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    file_path: str = typer.Option(
        ...,
        help="Path to exported result",
        envvar=common.LACEWORK_REPORTS_FILE_PATH,
    ),
    template_path: str = typer.Option(
        Path(__file__).resolve().parent.joinpath("vulnerability_coverage.html.j2"),
        help="Path to jinja2 template. Results will be passed as 'dataset' variable.",
        envvar=common.LACEWORK_REPORTS_TEMPLATE_PATH,
    ),
    ignore_errors: bool = typer.Option(
        True,
        help="Ignore error for missing reports or inaccessible account details.",
    ),
) -> None:
    """
    Set the command context
    """

    # connect lacework client
    lw = common.config.connect()

    # report details
    report_title = "Vulnerability Coverage"
    db_table = "vulnerability_coverage"

    reportHelper = ReportHelper()
    db_path = Path("database.db")
    # db_path.unlink(missing_ok=True)
    db_connection = f"sqlite:///{db_path.absolute()}?check_same_thread=False"

    reportHelper.sqlite_drop_table(db_table, db_connection)
    reportHelper.sqlite_drop_table("active_cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("machines", db_connection)
    reportHelper.sqlite_drop_table("cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("instances", db_connection)

    has_subaccounts = False
    if subaccounts:
        lwAccounts = reportHelper.get_subaccounts(client=lw)
        if len(lwAccounts) == 0:
            logging.error("Subaccounts specificed but none found")
            raise Exception("Subaccounts specificed but none found")
        else:
            has_subaccounts = True
    else:
        lwAccounts = [{"accountName": lw._account}]

    lacework_account_count = 0
    for lwAccount in lwAccounts:
        lacework_account_count += 1
        if has_subaccounts:
            logging.info(f"Switching to subaccount context: {lwAccount['accountName']}")
            lw.set_subaccount(lwAccount["accountName"])

        # sync cloud accounts with deployed agents
        logging.info("Syncing cloud accounts with deployed agents")
        reportHelper.get_active_cloud_accounts(
            client=lw,
            lwAccount=lwAccount["accountName"],
            start_time=start_time,
            end_time=end_time,
            use_sqlite=True,
            db_table="active_cloud_accounts",
            db_connection=db_connection,
        )
        try:
            result = reportHelper.sqlite_queries(
                queries={
                    "cloud_account_query": """
                                    SELECT 
                                        DISTINCT ACCOUNTID
                                    FROM 
                                        :db_table
                                    WHERE 
                                        ACCOUNTID IS NOT NULL
                                    """
                },
                db_connection=db_connection,
                db_table="active_cloud_accounts",
            )
            active_cloud_accounts = [
                x["ACCOUNTID"] for x in result["cloud_account_query"]
            ]
        except Exception:
            active_cloud_accounts = []

        # get cloud accounts and sync to sqlite
        cloud_accounts = reportHelper.get_cloud_accounts(
            client=lw, lwAccount=lwAccount["accountName"]
        )
        ExportHandler(
            format=DataHandlerTypes.SQLITE,
            results=[{"data": cloud_accounts}],
            file_path=file_path,
            db_table="cloud_accounts",
            db_connection=db_connection,
        ).export()

        logging.info(
            f"Discovered {len(active_cloud_accounts)} cloud accounts with agents deployed: {active_cloud_accounts}"
        )

        for cloud_account in cloud_accounts:

            if (
                cloud_account["enabled"] == 1
                and cloud_account["accountId"] in active_cloud_accounts
            ):
                # sync machines for this cloud account
                logging.info(
                    f"Syncing machines for {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_active_machines(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    start_time=start_time,
                    end_time=end_time,
                    use_sqlite=True,
                    db_table="machines",
                    db_connection=db_connection,
                )

                logging.info(
                    f"Enumerating {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_vulnerability_report(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    ignore_errors=ignore_errors,
                    start_time=start_time,
                    end_time=end_time,
                    package_active=package_active,
                    fixable=fixable,
                    namespace=namespace,
                    severity=severity,
                    cve=cve,
                    use_sqlite=True,
                    db_table=db_table,
                    db_connection=db_connection,
                )

                # ensure we have machines table if no machines were found
                if not reportHelper.sqlite_table_exists(
                    db_table="machines", db_connection=db_connection
                ):
                    machines_table = """
                                        CREATE TABLE machines (
                                                "LWACCOUNT" TEXT, 
                                                "ACCOUNTID" TEXT, 
                                                "TAG_HOSTNAME" TEXT, 
                                                "TAG_INSTANCEID" TEXT, 
                                                "TAG_ACCOUNTID" TEXT, 
                                                "TAG_PROJECTID" TEXT, 
                                                "TAG_VMPROVIDER" TEXT, 
                                                "LWTOKENSHORT" TEXT
                                            )
                                        """
                    reportHelper.sqlite_execute(
                        query=machines_table, db_connection=db_connection
                    )

                # ensure we have a vulnerability_coverage table
                if not reportHelper.sqlite_table_exists(
                    db_table="vulnerability_coverage", db_connection=db_connection
                ):
                    vulnerability_coverage_table = """
                                                    CREATE TABLE vulnerability_coverage (
                                                        "cveProps" JSON, 
                                                        "endTime" TEXT, 
                                                        "featureKey" JSON, 
                                                        "fixInfo" JSON, 
                                                        "machineTags" JSON, 
                                                        mid BIGINT, 
                                                        severity TEXT, 
                                                        "startTime" TEXT, 
                                                        status TEXT, 
                                                        "vulnId" TEXT
                                                    , accountId TEXT, lwAccount TEXT)
                                                    """
                    reportHelper.sqlite_execute(
                        query=vulnerability_coverage_table, db_connection=db_connection
                    )
            else:
                logging.info(
                    f"Skipping disabled or inactive account {lwAccount['accountName']}:{cloud_account['accountId']}"
                )

    # use sqlite query to generate final result
    results = reportHelper.sqlite_queries(
        queries=VulnerabilityQueries, db_table=db_table, db_connection=db_connection
    )

    if len(results["report"]) > 0:
        report = results["report"]

        # return additional stats under summary
        stats = {}
        for key in [x for x in results.keys() if x != "report"]:
            stats[key] = results[key]

        # write jinja template
        ExportHandler(
            format=DataHandlerTypes.JINJA2,
            results=[
                {
                    "data": [
                        {
                            "name": db_table,
                            "report": report,
                            "summary": {
                                "rows": len(report),
                                "reportTitle": report_title,
                                "stats": stats,
                            },
                        }
                    ]
                }
            ],
            template_path=template_path,
            file_path=file_path,
        ).export()
    else:
        logging.warn("No report results found.")


@app.command(name="csv", no_args_is_help=True, help="Generate CSV Report")
def csv_handler(
    ctx: typer.Context,
    start_time: datetime = typer.Option(
        (datetime.utcnow() - timedelta(hours=25)).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="Start time for query period",
    ),
    end_time: datetime = typer.Option(
        (datetime.utcnow()).strftime(common.ISO_FORMAT),
        formats=[common.ISO_FORMAT],
        help="End time for query period",
    ),
    package_active: bool = typer.Option(
        True, help="Return only active package vulnerabilities"
    ),
    fixable: bool = typer.Option(True, help="Return only fixable vulnerabilities"),
    severity: ReportSeverityTypes = typer.Option(
        ReportSeverityTypes.HIGH.value,
        help="Return only vulnerabilities with at or above the severity threshold",
    ),
    namespace: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching package namespace",
    ),
    cve: Optional[str] = typer.Option(
        None,
        help="Return only vulnerabilities matching cve",
    ),
    subaccounts: bool = typer.Option(
        False,
        help="Enumerate subaccounts",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    summary_only: bool = typer.Option(
        False,
        help="Return only summary details",
        envvar=common.LACEWORK_REPORTS_SUBACCOUNTS,
    ),
    file_path: str = typer.Option(
        ...,
        help="Path to exported result",
        envvar=common.LACEWORK_REPORTS_FILE_PATH,
    ),
    ignore_errors: bool = typer.Option(
        True,
        help="Ignore error for missing reports or inaccessible account details.",
    ),
) -> None:
    """
    Set the command context
    """

    # connect lacework client
    lw = common.config.connect()

    # report details
    report_title = "Vulnerability Coverage"
    db_table = "vulnerability_coverage"

    reportHelper = ReportHelper()
    db_path = Path("database.db")
    # db_path.unlink(missing_ok=True)
    db_connection = f"sqlite:///{db_path.absolute()}?check_same_thread=False"

    reportHelper.sqlite_drop_table(db_table, db_connection)
    reportHelper.sqlite_drop_table("active_cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("machines", db_connection)
    reportHelper.sqlite_drop_table("cloud_accounts", db_connection)
    reportHelper.sqlite_drop_table("instances", db_connection)

    has_subaccounts = False
    if subaccounts:
        lwAccounts = reportHelper.get_subaccounts(client=lw)
        if len(lwAccounts) == 0:
            logging.error("Subaccounts specificed but none found")
            raise Exception("Subaccounts specificed but none found")
        else:
            has_subaccounts = True
    else:
        lwAccounts = [{"accountName": lw._account}]

    lacework_account_count = 0
    for lwAccount in lwAccounts:
        lacework_account_count += 1
        if has_subaccounts:
            logging.info(f"Switching to subaccount context: {lwAccount['accountName']}")
            lw.set_subaccount(lwAccount["accountName"])

        # sync cloud accounts with deployed agents
        logging.info("Syncing cloud accounts with deployed agents")
        reportHelper.get_active_cloud_accounts(
            client=lw,
            lwAccount=lwAccount["accountName"],
            start_time=start_time,
            end_time=end_time,
            use_sqlite=True,
            db_table="active_cloud_accounts",
            db_connection=db_connection,
        )
        try:
            result = reportHelper.sqlite_queries(
                queries={
                    "cloud_account_query": """
                                    SELECT 
                                        DISTINCT ACCOUNTID
                                    FROM 
                                        :db_table
                                    WHERE 
                                        ACCOUNTID IS NOT NULL
                                    """
                },
                db_connection=db_connection,
                db_table="active_cloud_accounts",
            )
            active_cloud_accounts = [
                x["ACCOUNTID"] for x in result["cloud_account_query"]
            ]
        except Exception:
            active_cloud_accounts = []

        # get cloud accounts and sync to sqlite
        cloud_accounts = reportHelper.get_cloud_accounts(
            client=lw, lwAccount=lwAccount["accountName"]
        )
        ExportHandler(
            format=DataHandlerTypes.SQLITE,
            results=[{"data": cloud_accounts}],
            file_path=file_path,
            db_table="cloud_accounts",
            db_connection=db_connection,
        ).export()

        logging.info(
            f"Discovered {len(active_cloud_accounts)} cloud accounts with agents deployed: {active_cloud_accounts}"
        )

        for cloud_account in cloud_accounts:

            if (
                cloud_account["enabled"] == 1
                and cloud_account["accountId"] in active_cloud_accounts
            ):
                # sync machines for this cloud account
                logging.info(
                    f"Syncing machines for {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_active_machines(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    start_time=start_time,
                    end_time=end_time,
                    use_sqlite=True,
                    db_table="machines",
                    db_connection=db_connection,
                )

                logging.info(
                    f"Enumerating {lwAccount['accountName']}:{cloud_account['accountId']}"
                )
                reportHelper.get_vulnerability_report(
                    client=lw,
                    lwAccount=lwAccount["accountName"],
                    cloud_account=cloud_account["accountId"],
                    ignore_errors=ignore_errors,
                    start_time=start_time,
                    end_time=end_time,
                    package_active=package_active,
                    fixable=fixable,
                    namespace=namespace,
                    severity=severity,
                    cve=cve,
                    use_sqlite=True,
                    db_table=db_table,
                    db_connection=db_connection,
                )

                # ensure we have machines table if no machines were found
                if not reportHelper.sqlite_table_exists(
                    db_table="machines", db_connection=db_connection
                ):
                    machines_table = """
                                        CREATE TABLE machines (
                                                "LWACCOUNT" TEXT, 
                                                "ACCOUNTID" TEXT, 
                                                "TAG_HOSTNAME" TEXT, 
                                                "TAG_INSTANCEID" TEXT, 
                                                "TAG_ACCOUNTID" TEXT, 
                                                "TAG_PROJECTID" TEXT, 
                                                "TAG_VMPROVIDER" TEXT, 
                                                "LWTOKENSHORT" TEXT
                                            )
                                        """
                    reportHelper.sqlite_execute(
                        query=machines_table, db_connection=db_connection
                    )

                # ensure we have a vulnerability_coverage table
                if not reportHelper.sqlite_table_exists(
                    db_table="vulnerability_coverage", db_connection=db_connection
                ):
                    vulnerability_coverage_table = """
                                                    CREATE TABLE vulnerability_coverage (
                                                        "cveProps" JSON, 
                                                        "endTime" TEXT, 
                                                        "featureKey" JSON, 
                                                        "fixInfo" JSON, 
                                                        "machineTags" JSON, 
                                                        mid BIGINT, 
                                                        severity TEXT, 
                                                        "startTime" TEXT, 
                                                        status TEXT, 
                                                        "vulnId" TEXT
                                                    , accountId TEXT, lwAccount TEXT)
                                                    """
                    reportHelper.sqlite_execute(
                        query=vulnerability_coverage_table, db_connection=db_connection
                    )
            else:
                logging.info(
                    f"Skipping disabled or inactive account {lwAccount['accountName']}:{cloud_account['accountId']}"
                )

    # use sqlite query to generate final result
    results = reportHelper.sqlite_queries(
        queries=VulnerabilityQueries, db_table=db_table, db_connection=db_connection
    )

    if len(results["report"]) > 0:
        if summary_only:
            report = results["account_coverage"]
        else:
            report = results["report"]

        logging.info("Building CSV from resultant data...")
        ExportHandler(
            format=DataHandlerTypes.CSV,
            results=[{"data": report}],
            file_path=file_path,
        ).export()
    else:
        logging.warn("No report results found.")


if __name__ == "__main__":
    app()

Report Handler Set the command context # connect lacework client # report details # db_path.unlink(missing_ok=True) # sync cloud accounts with deployed agents SELECT                                          DISTINCT ACCOUNTID                                     FROM                                          :db_table                                     WHERE                                          ACCOUNTID IS NOT NULL # get cloud accounts and sync to sqlite # sync machines for this cloud account # ensure we have machines table if no machines were found CREATE TABLE machines (                                                 "LWACCOUNT" TEXT,                                                  "ACCOUNTID" TEXT,                                                  "TAG_HOSTNAME" TEXT,                                                  "TAG_INSTANCEID" TEXT,                                                  "TAG_ACCOUNTID" TEXT,                                                  "TAG_PROJECTID" TEXT,                                                  "TAG_VMPROVIDER" TEXT,                                                  "LWTOKENSHORT" TEXT                                             ) # ensure we have a vulnerability_coverage table CREATE TABLE vulnerability_coverage (                                                         "cveProps" JSON,                                                          "endTime" TEXT,                                                          "featureKey" JSON,                                                          "fixInfo" JSON,                                                          "machineTags" JSON,                                                          mid BIGINT,                                                          severity TEXT,                                                          "startTime" TEXT,                                                          status TEXT,                                                          "vulnId" TEXT                                                     , accountId TEXT, lwAccount TEXT) # use sqlite query to generate final result # return additional stats under summary # write jinja template Set the command context # connect lacework client # report details # db_path.unlink(missing_ok=True) # sync cloud accounts with deployed agents SELECT                                          DISTINCT ACCOUNTID                                     FROM                                          :db_table                                     WHERE                                          ACCOUNTID IS NOT NULL # get cloud accounts and sync to sqlite # sync machines for this cloud account # ensure we have machines table if no machines were found CREATE TABLE machines (                                                 "LWACCOUNT" TEXT,                                                  "ACCOUNTID" TEXT,                                                  "TAG_HOSTNAME" TEXT,                                                  "TAG_INSTANCEID" TEXT,                                                  "TAG_ACCOUNTID" TEXT,                                                  "TAG_PROJECTID" TEXT,                                                  "TAG_VMPROVIDER" TEXT,                                                  "LWTOKENSHORT" TEXT                                             ) # ensure we have a vulnerability_coverage table CREATE TABLE vulnerability_coverage (                                                         "cveProps" JSON,                                                          "endTime" TEXT,                                                          "featureKey" JSON,                                                          "fixInfo" JSON,                                                          "machineTags" JSON,                                                          mid BIGINT,                                                          severity TEXT,                                                          "startTime" TEXT,                                                          status TEXT,                                                          "vulnId" TEXT                                                     , accountId TEXT, lwAccount TEXT) # use sqlite query to generate final result