#!/usr/bin/env python3
"""
QGIS PDF Export Script
This script loads a QGIS project, applies filters to layers, 
fits the view to remaining features, and exports to PDF.
"""

import sys
import os
from qgis.core import (
    QgsApplication, 
    QgsProject, 
    QgsVectorLayer,
    QgsRectangle,
    QgsCoordinateReferenceSystem,
    QgsLayoutExporter,
    QgsPrintLayout,
    QgsLayoutItemMap,
    QgsLayoutSize,
    QgsUnitTypes,
    QgsLayoutPoint,
    QgsLayoutItemLabel,
    QgsTextFormat,
    QgsCoordinateTransform,
    QgsFeatureRequest,
    QgsMapSettings,
    QgsMapRendererParallelJob,
    QgsLayoutItemPage,
    QgsRenderContext
)
from qgis.PyQt.QtCore import QSizeF, QRectF
from qgis.PyQt.QtGui import QColor, QFont

import db_config as config
import psycopg2

from datetime import datetime

current_time = datetime.now().strftime("%Y-%m-%d %H:%M")
print(current_time)  # Example output: 2025-06-29 14:30

conn = config.db

conn = psycopg2.connect(
    host=conn['host'],
    user=conn['user'],
    password=conn['password'],
    database=conn['database']
)

def get_project_details(project_id):
    cursor = conn.cursor()
    # Columns named explicitly, not `select *`: the caller reads project[1] as the
    # opportunity name, which held only because opportunity_name was the second column of
    # prospector.opportunity. In opportunity.opportunities that position is
    # parent_opportunity_id, so `select *` titles every PDF with a parent id (usually None).
    cursor.execute(f"""select opportunity_id, opportunity_name
    from opportunity.opportunities where opportunity_id = {project_id}
    limit 1""")
    project_details = cursor.fetchall()
    conn.commit()
    return project_details

def get_opportunity_summary(project_id):
    cursor = conn.cursor()
    cursor.execute(f"""
                   
                   with r as (
select 
o.*,
count(*) over (partition by edge, opportunity_id) edge_used_count
,row_number() over (partition by edge, opportunity_id order by route_id asc, seq asc) r 
from opportunity.opportunity_routes o 
where o.opportunity_id = {project_id} 
order by seq asc

)

, pr as (
select
count(distinct opl.project_id) project_count,
count(*) total_premises,
count(case when bt.building_type = 'MDU' then 1 else null end) mdu_premises,
count(case when bt.building_type = 'SDU' then 1 else null end) sdu_premises,
count(case when bt.building_type not in ('MDU','SDU') then 1 else null end) other_premises

from opportunity.opportunities o 
left join opportunity.opportunity_project_link opl on o.opportunity_id = opl.opportunity_id and opl.is_deleted = false
left join projects.projects p on opl.project_id = p.project_id 
left join basedata.abp a on st_intersects(p.geom, a.geom)
left join basedata.toid_building_type bt on a.cross_reference = bt.toid
where o.opportunity_id = {project_id} 
)

, s as (
select 
round(sum(shape_length)::numeric,0) total_distance 
, round(sum(case when edge_used_count > 1 then shape_length else 0 end)::numeric,0) shared_route_distance
, round(sum(case when edge_used_count = 1 then shape_length else 0 end)::numeric,0) unique_route_distance
from 
 r where r.r = 1
)

select 
pr.project_count,
coalesce(s.total_distance,0) total_distance, 
coalesce(s.shared_route_distance,0) shared_route_distance, 
coalesce(s.unique_route_distance,0) unique_route_distance,
pr.total_premises, 
pr.mdu_premises, 
pr.sdu_premises, 
pr.other_premises
,coalesce(round(s.total_distance::numeric/nullif(pr.mdu_premises,0),1),0) as total_mtr_per_mdu_prem
,coalesce(round(s.unique_route_distance::numeric/nullif(pr.mdu_premises,0),1),0) as unique_mtr_per_mdu_prem
,coalesce(round(s.shared_route_distance::numeric/nullif(s.total_distance,0)*100,1),0) as shared_meterage_percent
from pr
left join s on true
;




    """)
    project_details = cursor.fetchall()
    conn.commit()
    return project_details

def initialize_qgis():
    """Initialize QGIS application"""
    # Set QGIS path (adjust for your system)
    QgsApplication.setPrefixPath("/usr", True)  # Linux
    # QgsApplication.setPrefixPath("C:/Program Files/QGIS 3.x", True)  # Windows
    
    # Initialize QGIS
    qgs = QgsApplication([], False)
    qgs.initQgis()
    return qgs

def load_project(project_path):
    """Load QGIS project file"""
    project = QgsProject.instance()
    if not project.read(project_path):
        raise Exception(f"Failed to load project: {project_path}")
    return project

def apply_layer_filters(project, layer_filters):
    """
    Apply filters to multiple layers
    
    Args:
        project: QgsProject instance
        layer_filters: Dict with layer names as keys and filter expressions as values
                      Example: {'layer1': 'population > 1000', 'layer2': 'type = "urban"'}
    """
    filtered_layers = []
    
    for layer_name, filter_expression in layer_filters.items():
        layer = project.mapLayersByName(layer_name)
        
        if not layer:
            print(f"Warning: Layer '{layer_name}' not found")
            continue
            
        layer = layer[0]  # Get first matching layer
        
        if isinstance(layer, QgsVectorLayer):
            # Apply subset string filter
            if not layer.setSubsetString(filter_expression):
                print(f"Warning: Failed to apply filter to layer '{layer_name}'")
            else:
                print(f"Applied filter to '{layer_name}': {filter_expression}")
                # Force layer refresh
                layer.triggerRepaint()
                layer.dataProvider().forceReload()
                filtered_layers.append(layer)
        else:
            print(f"Warning: Layer '{layer_name}' is not a vector layer")
    
    return filtered_layers

def get_filtered_extent(filtered_layers, project):
    """Calculate extent of all filtered features (not the full layer extent) and transform to project CRS"""
    if not filtered_layers:
        return None
    
    # Get project CRS
    project_crs = project.crs()
    print(f"Project CRS: {project_crs.authid()}")
    
    combined_extent = QgsRectangle()
    combined_extent.setMinimal()
    
    for layer in filtered_layers:
        layer_crs = layer.crs()
        print(f"Processing layer '{layer.name()}' with CRS: {layer_crs.authid()}")
        print(f"Layer has {layer.featureCount()} filtered features")
        
        # Calculate extent of ONLY the filtered features
        layer_extent = QgsRectangle()
        layer_extent.setMinimal()
        
        feature_count = 0
        for feature in layer.getFeatures():  # This respects the subset string filter
            geom = feature.geometry()
            if geom and not geom.isEmpty():
                feature_bbox = geom.boundingBox()
                if layer_extent.isNull():
                    layer_extent = feature_bbox
                else:
                    layer_extent.combineExtentWith(feature_bbox)
                feature_count += 1
        
        print(f"Calculated extent from {feature_count} features: {layer_extent}")
        
        if not layer_extent.isNull():
            # Transform layer extent to project CRS if needed
            if layer_crs != project_crs:
                transform = QgsCoordinateTransform(layer_crs, project_crs, project)
                try:
                    transformed_layer_extent = transform.transformBoundingBox(layer_extent)
                    print(f"Transformed layer extent to project CRS: {transformed_layer_extent}")
                    layer_extent = transformed_layer_extent
                except Exception as e:
                    print(f"Failed to transform layer extent: {e}")
                    continue
            
            # Combine with overall extent
            if combined_extent.isNull():
                combined_extent = layer_extent
            else:
                combined_extent.combineExtentWith(layer_extent)
    
    if combined_extent.isNull():
        print("No features found in any filtered layers")
        return None
    
    # Add 10% buffer around the extent
    buffer = max(combined_extent.width(), combined_extent.height()) * 0.1
    buffered_extent = QgsRectangle(
        combined_extent.xMinimum() - buffer,
        combined_extent.yMinimum() - buffer,
        combined_extent.xMaximum() + buffer,
        combined_extent.yMaximum() + buffer
    )
    
    print(f"Final combined extent (in project CRS {project_crs.authid()}): {buffered_extent}")
    return buffered_extent


def create_layout_and_export_pdf(project, extent, output_path, filtered_layers, title="Filtered Features"):
    """
    Use existing print layout and export to PDF
    
    Uses the pre-designed layout 'Project_PDF_Export' with:
    - Map item ID: "map_1" 
    - Title item ID: "project_title"
    """

    print(output_path)
    
    # Get the existing layout instead of creating a new one
    manager = project.layoutManager()

    
    #layout = manager.layoutByName("Project_PDF_Export")

    layout = manager.layoutByName("Opportunity_PDF_Export")
    
    if not layout:
        print(f"Error: Layout '{layout}' not found!")
        print("Available layouts:")
        for layout_item in manager.layouts():
            print(f"  - {layout_item.name()}")
        return False
    
    # Get the existing map item
    map_item = layout.itemById("map_1")
    if not map_item:
        print("Error: Map item 'map_1' not found in layout!")
        return False
    
    # Get the existing title item
    title_item = layout.itemById("project_title")
    if not title_item:
        print("Warning: Title item 'project_title' not found in layout!")

    # Get the existing title item
    summary_item = layout.itemById("opportunity_summary")
    if not summary_item:
        print("Warning: Title item 'project_title' not found in layout!")


    
    
    # Get the existing title item
    export_dt_item = layout.itemById("exported_datetime")
    if not export_dt_item:
        print("Warning: Title item 'exported_datetime' not found in layout!")
    
    # Get the existing title item
    export_dt_item2 = layout.itemById("exported_datetime2")
    if not export_dt_item2:
        print("Warning: Title item 'exported_datetime2' not found in layout!")
    
    # IMPORTANT: Get layers in the order they appear in the layer tree
    root = project.layerTreeRoot()
    
    # Get all layers in tree order (preserves the order from your QGIS project)
    ordered_basemap_layers = []
    ordered_vector_layers = []
    
    # Traverse the layer tree to get layers in the correct order
    for tree_layer in root.findLayers():
        if tree_layer.isVisible() and tree_layer.layer():
            layer = tree_layer.layer()
            
            # Check if it's a raster/XYZ layer (type 1) or WMS layer
            if layer.type() == 1 or layer.dataProvider().name() in ['wms', 'xyz']:
                ordered_basemap_layers.append(layer)
                print(f"Found basemap layer: {layer.name()}")
            else:
                ordered_vector_layers.append(layer)
                print(f"Found vector layer: {layer.name()}")
    
    # Reverse the lists because QGIS layer tree order is opposite of rendering order
    # (top of tree = renders last/on top, bottom of tree = renders first/underneath)
    ordered_basemap_layers.reverse()
    
    # Build the final layer order: vectors on top, basemaps on bottom
    ordered_layers = ordered_vector_layers + ordered_basemap_layers
    
    print("\nFinal layer order (bottom to top rendering):")
    for i, layer in enumerate(ordered_layers):
        layer_type = "Basemap" if layer in ordered_basemap_layers else "Vector"
        is_filtered = " (FILTERED)" if layer in filtered_layers else ""
        print(f"  {i}: {layer.name()} ({layer_type}){is_filtered}")
    
    # Ensure filtered layers are visible
    for layer in ordered_vector_layers:
        if layer in filtered_layers:
            # Ensure the layer is visible
            if hasattr(layer, 'renderer') and layer.renderer():
                layer.renderer().setReferenceScale(-1)  # Disable reference scale
            layer.setScaleBasedVisibility(False)  # Disable scale-based visibility
            print(f"Ensured layer '{layer.name()}' is visible")
    
    # Set the map to show layers in the correct order
    map_item.setLayers(ordered_layers)
    
    # Set the extent
    # Set the extent with proper aspect ratio handling
    if extent and not extent.isEmpty():
        print(f"Original extent: {extent}")
        print(f"Map item will use project CRS: {project.crs().authid()}")
        
        # Get the map item's dimensions (in mm)
        map_rect = map_item.rect()
        map_aspect_ratio = map_rect.width() / map_rect.height()
        print(f"Map item aspect ratio: {map_aspect_ratio:.3f} (width: {map_rect.width():.1f}mm, height: {map_rect.height():.1f}mm)")
        
        # Calculate extent dimensions
        extent_width = extent.width()
        extent_height = extent.height()
        extent_aspect_ratio = extent_width / extent_height
        print(f"Extent aspect ratio: {extent_aspect_ratio:.3f}")
        
        # Adjust extent to match map item aspect ratio
        if extent_aspect_ratio > map_aspect_ratio:
            # Extent is wider than map item - expand height
            new_height = extent_width / map_aspect_ratio
            height_diff = new_height - extent_height
            adjusted_extent = QgsRectangle(
                extent.xMinimum(),
                extent.yMinimum() - height_diff / 2,
                extent.xMaximum(),
                extent.yMaximum() + height_diff / 2
            )
            print("Adjusted extent by expanding height")
        else:
            # Extent is taller than map item - expand width
            new_width = extent_height * map_aspect_ratio
            width_diff = new_width - extent_width
            adjusted_extent = QgsRectangle(
                extent.xMinimum() - width_diff / 2,
                extent.yMinimum(),
                extent.xMaximum() + width_diff / 2,
                extent.yMaximum()
            )
            print("Adjusted extent by expanding width")
        
        # Add a small buffer (5%) to ensure nothing is cut off at the edges
        buffer_x = adjusted_extent.width() * 0.001
        buffer_y = adjusted_extent.height() * 0.001
        final_extent = QgsRectangle(
            adjusted_extent.xMinimum() - buffer_x,
            adjusted_extent.yMinimum() - buffer_y,
            adjusted_extent.xMaximum() + buffer_x,
            adjusted_extent.yMaximum() + buffer_y
        )
        
        print(f"Final extent with buffer: {final_extent}")
        
        # Set the adjusted extent
        map_item.setExtent(final_extent)
        
        # Force refresh the map item
        map_item.invalidateCache()
        map_item.refresh()
        
        # Wait for the map to render
        map_item.refreshDataDefinedProperty()
        
        print(f"Map extent after setting: {map_item.extent()}")
    else:
        print("Warning: No valid extent found, using default view")
    
    # Update the title if title item exists
    if title_item:
        title_item.setText(f"Opportunity Overview: {title}")
        print(f"Updated title to: {title}")

    # Update the title if title item exists

    summary = get_opportunity_summary(project_id)
    #print(summary)

    summary_text = f"""
            <h4>Opportunity: {title}</h4>
            <p>Summary:</p>
            <p>Opportunity contains <strong>{summary[0][0]}</strong> projects, covering <strong>{summary[0][4]}</strong> total premises and <strong>{summary[0][5]}</strong> MDU premises.</p>
        <p>An estimated <strong>{summary[0][1]}m</strong> of cable/duct will be required to connect all projects.</p>
        <p>On average <strong>{summary[0][8]}m</strong> of cable/duct is being used per premise.</p>
        <p><strong>{summary[0][2]}m ({summary[0][10]}%)</strong> of the routes are shared between multiple projects within the opportunity.</p>
        """

    #print(summary_text)

    if summary_item:
        summary_item.setText(summary_text)
        print(f"Updated summary")

    if export_dt_item:
        export_dt_item.setText(f"Document Generated On: {current_time}")
        print(f"Exported at: {current_time}")

    if export_dt_item2:
        export_dt_item2.setText(f"Document Generated On: {current_time}")
        print(f"Exported at: {current_time}")

    textItems = ['project_title','header_logo','exported_datetime']

    # Apply TextFormatAlwaysOutlines to ALL text items in the layout
    """for item in layout.items():
        print(item)
        item_type = type(item).__name__
        item_id = item.id() if hasattr(item, 'id') else "No ID"
        
        print(f"=== {item_type} (ID: '{item_id}') ===")
        if item_id in textItems:
            print('Set Render Mode')
            item.textRenderFormat = QgsRenderContext.TextFormatAlwaysOutlines
        """
    
    for item in layout.items():
        if isinstance(item, QgsLayoutItemLabel):
            # Force the item to update its rendering
            item.invalidateCache()
            item.refresh()
            
            # If possible, access the render context
            if hasattr(item, 'setRenderContext'):
                context = QgsRenderContext()
                context.setTextRenderFormat(QgsRenderContext.TextFormatAlwaysOutlines)
                item.setRenderContext(context)
    
    # Refresh the entire layout
    print('Refresh The Layout')
    layout.refresh()
    
    # Export to PDF
    exporter = QgsLayoutExporter(layout)
    export_settings = QgsLayoutExporter.PdfExportSettings()
    export_settings.dpi = 300
    export_settings.forceVectorOutput = True  # Force vector output for better quality
    export_settings.simplifyGeometries = False  # Don't simplify geometries

    
    # Debug: Check if the attribute exists and what values are available
    print(f"TextFormatAlwaysOutlines value: {QgsRenderContext.TextFormatAlwaysOutlines}")
    print(f"Export settings has textRenderFormat: {hasattr(export_settings, 'textRenderFormat')}")

    # Try setting it and checking if it's applied
    export_settings.textRenderFormat = QgsRenderContext.TextFormatAlwaysOutlines
    print(f"textRenderFormat after setting: {export_settings.textRenderFormat}")

    # Add this - it might help force the text rendering
    export_settings.rasterizeWholeImage = False
    export_settings.exportMetadata = True

    # If available in your QGIS version, try:
    if hasattr(export_settings, 'alwaysExportAsVectors'):
        export_settings.alwaysExportAsVectors = True
    
    result = exporter.exportToPdf(output_path, export_settings)
    
    if result == QgsLayoutExporter.Success:
        print(f"PDF successfully exported to: {output_path}")
        return True
    else:
        print(f"PDF export failed with code: {result}")
        return False
    

def main(project_id, project_name):
    """Main function"""
    # Configuration
    filename = f"opportunity_overview_{project_id}_{project_name}.pdf"
    filename = filename.replace(" ", "_")
    
    project_path = "/home/davebasnett/Python/netplanner/Opportunity_PDF_Export.qgz"  # Update this path
    #output_pdf = f"output_pdf/{filename}"  # Update this path
    #output_pdf = f"/var/lib/netplannerexports/{filename}.pdf"  # Update this path
    
    output_dir = "/var/lib/netplannerexports"  # This is outside the web document root
    # Check if directory exists
    if not os.path.exists(output_dir):
        print(f"Directory {output_dir} does not exist. Attempting to create...")
        try:
            os.makedirs(output_dir, exist_ok=True)
            print(f"Directory created successfully")
        except PermissionError:
            print(f"Permission denied: Cannot create directory {output_dir}")
            print("Try running with sudo or check directory permissions")
            return
        except Exception as e:
            print(f"Error creating directory: {e}")
            return

    # Check if directory is writable
    if not os.access(output_dir, os.W_OK):
        print(f"Directory {output_dir} is not writable")
        return

    output_pdf = os.path.join(output_dir, filename)
    print(f"Output path: {output_pdf}")


    output_pdf = os.path.join(output_dir, filename)
    
    #project_id = 9
    print(project_id)
    
    # Define filters for different layers
    layer_filters = {
        #"vw_opportunity_network_points": f"opportunity_id = {project_id}",  
        "vw_opportunity_routes": f"opportunity_id = {project_id}",     
        "vw_opportunity_projects_output": f"opportunity_id = {project_id}" 
    }

    print(layer_filters)
    
    try:
        # Initialize QGIS
        print("Initializing QGIS...")
        qgs = initialize_qgis()
        
        # Load project
        print(f"Loading project: {project_path}")
        project = load_project(project_path)
        
        # Apply filters
        print("Applying filters...")
        filtered_layers = apply_layer_filters(project, layer_filters)
        
        if not filtered_layers:
            print("No layers were successfully filtered. Exiting.")
            return
        
        # Calculate extent of filtered features
        print("Calculating extent of filtered features...")
        extent = get_filtered_extent(filtered_layers, project)
        
        if extent is None or extent.isEmpty():
            print("No features found after filtering. Exiting.")
            return
        
        print(f"Filtered extent: {extent}")
        
        # Create output directory if it doesn't exist
        os.makedirs(os.path.dirname(output_pdf), exist_ok=True)
        
        # Export to PDF - pass filtered_layers to the function
        print("Creating layout and exporting PDF...")
        success = create_layout_and_export_pdf(
            project, 
            extent, 
            output_pdf,
            filtered_layers,  # Pass filtered layers
            title=f"{project_name}"
        )
        
        if success:
            print("Export completed successfully!")
            return output_pdf
        else:
            print("Export failed!")
            
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()
        
    finally:
        # Clean up
        QgsApplication.exitQgis()


if __name__ == "__main__":

    # Check if command line arguments are provided
    if len(sys.argv) < 2:
        print("Usage: python script.py <filter_id>")
        print("Example: python script.py '12345'")
        sys.exit(1)
    
    # Get filter_id from command line
    project_id = sys.argv[1]
    project = get_project_details(project_id)[0]
    #print(project)
    project_name = project[1]
    print(project_name)
    
    export = main(project_id, project_name)
    print(f"PDF created: {export}")