#!/usr/bin/env python3
"""
Abacus Looker Documentation Generator

This script automatically generates comprehensive documentation for all Looker models and views
in the abacus-looker project, including:
- Markdown documentation
- CSV/Excel data catalogs  
- Interactive HTML documentation
- Data lineage and dependency reports

Usage: python generate_documentation.py
"""

import os
import re
import json
import csv
from pathlib import Path
from datetime import datetime
from typing import Dict, List, Set, Tuple
import pandas as pd

class LookerDocGenerator:
    def __init__(self, project_root: str):
        self.project_root = Path(project_root)
        self.docs_dir = self.project_root / "docs"
        self.models = {}
        self.views = {}
        self.explores = {}
        self.dependencies = {}
        
    def parse_lkml_file(self, file_path: Path) -> Dict:
        """Parse a .lkml file and extract metadata"""
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                content = f.read()
        except Exception as e:
            print(f"Error reading {file_path}: {e}")
            return {}
            
        metadata = {
            'file_path': str(file_path),
            'file_name': file_path.name,
            'relative_path': str(file_path.relative_to(self.project_root)),
            'content': content,
            'size': len(content),
            'line_count': len(content.splitlines())
        }
        
        return metadata
    
    def extract_model_info(self, content: str) -> Dict:
        """Extract model-specific information from LookML content"""
        info = {
            'label': None,
            'connection': None,
            'includes': [],
            'explores': [],
            'fiscal_month_offset': None,
            'datagroups': [],
            'description': None,
            'comments': []
        }
        
        # Extract label
        label_match = re.search(r'label:\s*"([^"]*)"', content)
        if label_match:
            info['label'] = label_match.group(1)
            
        # Extract connection
        conn_match = re.search(r'connection:\s*"([^"]*)"', content)
        if conn_match:
            info['connection'] = conn_match.group(1)
            
        # Extract includes
        includes = re.findall(r'include:\s*"([^"]*)"', content)
        info['includes'] = includes
        
        # Extract explores
        explore_matches = re.findall(r'explore:\s*(\w+)\s*{[^}]*label:\s*"([^"]*)"', content, re.DOTALL)
        for explore_name, explore_label in explore_matches:
            info['explores'].append({
                'name': explore_name,
                'label': explore_label
            })
        
        # Also find explores without labels
        simple_explores = re.findall(r'explore:\s*(\w+)\s*{', content)
        existing_explores = {exp['name'] for exp in info['explores']}
        for explore_name in simple_explores:
            if explore_name not in existing_explores:
                info['explores'].append({
                    'name': explore_name,
                    'label': explore_name
                })
        
        # Extract fiscal month offset
        fiscal_match = re.search(r'fiscal_month_offset:\s*(-?\d+)', content)
        if fiscal_match:
            info['fiscal_month_offset'] = int(fiscal_match.group(1))
        
        # Extract description from comments or description field
        desc_match = re.search(r'description:\s*"([^"]*)"', content)
        if desc_match:
            info['description'] = desc_match.group(1)
        
        # Extract meaningful comments (not just # comments, but proper documentation)
        comments = re.findall(r'#\s*(.+)', content)
        meaningful_comments = [c.strip() for c in comments if len(c.strip()) > 10 and not c.strip().startswith('include')]
        info['comments'] = meaningful_comments[:3]  # Keep top 3 meaningful comments
            
        return info
    
    def extract_view_info(self, content: str) -> Dict:
        """Extract view-specific information from LookML content"""
        info = {
            'view_name': None,
            'sql_table_name': None,
            'derived_table': None,
            'dimensions': [],
            'measures': [],
            'dimension_groups': [],
            'filters': [],
            'description': None,
            'comments': [],
            'sql_comments': []
        }
        
        # Extract view name
        view_match = re.search(r'view:\s*(\w+)\s*{', content)
        if view_match:
            info['view_name'] = view_match.group(1)
        
        # Extract SQL table name
        table_match = re.search(r'sql_table_name:\s*([^;]+);', content)
        if table_match:
            info['sql_table_name'] = table_match.group(1).strip()
        
        # Extract derived table
        derived_match = re.search(r'derived_table:\s*{([^}]+)}', content, re.DOTALL)
        if derived_match:
            derived_sql = derived_match.group(1).strip()
            info['derived_table'] = derived_sql
            
            # Extract SQL comments from derived table
            sql_comments = re.findall(r'--\s*(.+)', derived_sql)
            info['sql_comments'] = [c.strip() for c in sql_comments if len(c.strip()) > 5][:5]  # Top 5 SQL comments
            
            # If it's a derived table, extract table names from the SQL
            if not info['sql_table_name']:
                extracted_tables = self.extract_tables_from_sql(derived_sql)
                if extracted_tables:
                    # Format the table list for display
                    table_list = ', '.join(sorted(set(extracted_tables))[:5])  # Show up to 5 unique tables
                    if len(extracted_tables) > 5:
                        table_list += f" + {len(extracted_tables) - 5} more"
                    info['sql_table_name'] = f"Derived from: {table_list}"
                else:
                    info['sql_table_name'] = "Derived Table (Custom SQL)"
        
        # Extract dimensions with descriptions - simpler approach
        # First, let's find all dimension blocks
        dim_blocks = []
        lines = content.split('\n')
        i = 0
        while i < len(lines):
            line = lines[i].strip()
            if line.startswith('dimension:'):
                # Extract dimension name
                name_match = re.search(r'dimension:\s*(\w+)', line)
                if name_match:
                    dim_name = name_match.group(1)
                    dim_body = []
                    i += 1
                    brace_count = 0
                    if '{' in line:
                        brace_count = 1
                    
                    # Collect the dimension body
                    while i < len(lines) and (brace_count > 0 or not lines[i].strip().startswith('dimension:')):
                        current_line = lines[i]
                        dim_body.append(current_line)
                        brace_count += current_line.count('{') - current_line.count('}')
                        if brace_count == 0:
                            break
                        i += 1
                    
                    dim_blocks.append((dim_name, '\n'.join(dim_body)))
            i += 1
        
        for name, body in dim_blocks:
            dim_info = {'name': name, 'type': 'string', 'description': ''}
            
            # Extract type
            type_match = re.search(r'type:\s*(\w+)', body)
            if type_match:
                dim_info['type'] = type_match.group(1)
            
            # Extract description or label
            desc_match = re.search(r'(?:description|label):\s*"([^"]*)"', body)
            if desc_match:
                dim_info['description'] = desc_match.group(1)
            
            # If no description, try to infer from field name
            if not dim_info['description']:
                # Convert snake_case to readable format
                readable_name = name.replace('_', ' ').title()
                if 'id' in name.lower():
                    dim_info['description'] = f"Unique identifier for {readable_name.replace(' Id', '')}"
                elif 'name' in name.lower():
                    dim_info['description'] = f"Name or label for {readable_name.replace(' Name', '')}"
                elif 'amount' in name.lower():
                    dim_info['description'] = f"Monetary amount for {readable_name.replace(' Amount', '')}"
                elif 'date' in name.lower() or 'time' in name.lower():
                    dim_info['description'] = f"Date/time information for {readable_name}"
                else:
                    dim_info['description'] = f"{readable_name}"
            
            info['dimensions'].append(dim_info)
        
        # Extract measures with descriptions - simpler approach
        measure_blocks = []
        i = 0
        while i < len(lines):
            line = lines[i].strip()
            if line.startswith('measure:'):
                # Extract measure name
                name_match = re.search(r'measure:\s*(\w+)', line)
                if name_match:
                    measure_name = name_match.group(1)
                    measure_body = []
                    i += 1
                    brace_count = 0
                    if '{' in line:
                        brace_count = 1
                    
                    # Collect the measure body
                    while i < len(lines) and (brace_count > 0 or not lines[i].strip().startswith('measure:')):
                        current_line = lines[i]
                        measure_body.append(current_line)
                        brace_count += current_line.count('{') - current_line.count('}')
                        if brace_count == 0:
                            break
                        i += 1
                    
                    measure_blocks.append((measure_name, '\n'.join(measure_body)))
            i += 1
        
        for name, body in measure_blocks:
            measure_info = {'name': name, 'type': 'number', 'description': ''}
            
            # Extract type
            type_match = re.search(r'type:\s*(\w+)', body)
            if type_match:
                measure_info['type'] = type_match.group(1)
            
            # Extract description or label
            desc_match = re.search(r'(?:description|label):\s*"([^"]*)"', body)
            if desc_match:
                measure_info['description'] = desc_match.group(1)
            
            # If no description, infer from measure type and name
            if not measure_info['description']:
                readable_name = name.replace('_', ' ').title()
                if measure_info['type'] == 'count':
                    measure_info['description'] = f"Count of {readable_name}"
                elif measure_info['type'] == 'sum':
                    measure_info['description'] = f"Total sum of {readable_name}"
                elif measure_info['type'] == 'average':
                    measure_info['description'] = f"Average {readable_name}"
                elif 'total' in name.lower():
                    measure_info['description'] = f"Total {readable_name}"
                else:
                    measure_info['description'] = f"Calculated measure: {readable_name}"
            
            info['measures'].append(measure_info)
        
        # Extract dimension groups with descriptions
        dimgrp_blocks = []
        i = 0
        while i < len(lines):
            line = lines[i].strip()
            if line.startswith('dimension_group:'):
                # Extract dimension group name
                name_match = re.search(r'dimension_group:\s*(\w+)', line)
                if name_match:
                    dimgrp_name = name_match.group(1)
                    dimgrp_body = []
                    i += 1
                    brace_count = 0
                    if '{' in line:
                        brace_count = 1
                    
                    # Collect the dimension group body
                    while i < len(lines) and (brace_count > 0 or not lines[i].strip().startswith('dimension_group:')):
                        current_line = lines[i]
                        dimgrp_body.append(current_line)
                        brace_count += current_line.count('{') - current_line.count('}')
                        if brace_count == 0:
                            break
                        i += 1
                    
                    dimgrp_blocks.append((dimgrp_name, '\n'.join(dimgrp_body)))
            i += 1
        
        for name, body in dimgrp_blocks:
            dimgrp_info = {'name': name, 'type': 'time', 'description': ''}
            
            # Extract type
            type_match = re.search(r'type:\s*(\w+)', body)
            if type_match:
                dimgrp_info['type'] = type_match.group(1)
            
            # Extract description or label
            desc_match = re.search(r'(?:description|label):\s*"([^"]*)"', body)
            if desc_match:
                dimgrp_info['description'] = desc_match.group(1)
            
            info['dimension_groups'].append(dimgrp_info)
        
        # Extract filters
        filters = re.findall(r'filter:\s*(\w+)\s*{', content)
        info['filters'] = [{'name': name} for name in filters]
        
        # Extract view description
        view_desc_match = re.search(r'view:\s*\w+\s*{[^}]*description:\s*"([^"]*)"', content, re.DOTALL)
        if view_desc_match:
            info['description'] = view_desc_match.group(1)
        
        # Extract meaningful comments
        comments = re.findall(r'#\s*(.+)', content)
        meaningful_comments = [c.strip() for c in comments if len(c.strip()) > 10]
        info['comments'] = meaningful_comments[:3]
        
        return info
    
    def extract_tables_from_sql(self, sql_content: str) -> List[str]:
        """Extract actual database table names from SQL content in derived tables"""
        tables = []
        
        # Clean up the SQL content but preserve case for better pattern matching
        sql_content = sql_content.strip()
        
        # More sophisticated patterns to match actual database tables
        # Look for schema.table patterns that are likely real tables (not CTEs)
        patterns = [
            # Pattern: FROM/JOIN database.schema.table_name
            r'(?:FROM|JOIN)\s+([A-Z_][A-Z0-9_]*\.[A-Z_][A-Z0-9_]*\.[A-Z_][A-Z0-9_]*)',
            # Pattern: FROM/JOIN schema.table_name  
            r'(?:FROM|JOIN)\s+([A-Z_][A-Z0-9_]*\.[A-Z_][A-Z0-9_]*)',
            # Pattern: FROM/JOIN table_name (single name, likely a table)
            r'(?:FROM|JOIN)\s+([A-Z_][A-Z0-9_]+)(?:\s+(?:AS\s+)?[A-Z_][A-Z0-9_]*)?'
        ]
        
        # Convert to uppercase for pattern matching
        sql_upper = sql_content.upper()
        
        for pattern in patterns:
            matches = re.findall(pattern, sql_upper)
            for match in matches:
                table_name = match.strip()
                
                # Skip obvious CTEs and subquery aliases (usually shorter names)
                skip_patterns = [
                    # Skip single letter aliases
                    r'^[A-Z]$',
                    # Skip obvious CTE names (ending with _CTE)
                    r'.*_CTE$',
                    # Skip common SQL keywords
                    r'^(SELECT|WHERE|ORDER|GROUP|HAVING|AS|ON|AND|OR|WITH|CASE|WHEN|THEN|ELSE|END)$'
                ]
                
                should_skip = False
                for skip_pattern in skip_patterns:
                    if re.match(skip_pattern, table_name):
                        should_skip = True
                        break
                
                if not should_skip and len(table_name) > 2:
                    # Clean up the table name for better display
                    if '.' in table_name:
                        parts = table_name.split('.')
                        if len(parts) == 3:
                            # database.schema.table -> schema.table
                            clean_name = f"{parts[1]}.{parts[2]}"
                        elif len(parts) == 2:
                            # schema.table -> keep as is
                            clean_name = table_name
                        else:
                            clean_name = parts[-1]
                    else:
                        clean_name = table_name
                    
                    # Convert back to a more readable format
                    clean_name = clean_name.lower().replace('_', ' ').title().replace(' ', '_')
                    
                    # Only include if it looks like a real table (has meaningful length and structure)
                    if len(clean_name) > 3 and '_' in clean_name:
                        tables.append(clean_name)
        
        # Additional pass to find tables with specific database prefixes common in your project
        royalty_pattern = r'(?:FROM|JOIN)\s+(ROYALTY_ACCOUNTING\.[A-Z_]+\.[A-Z_][A-Z0-9_]+)'
        orchard_pattern = r'(?:FROM|JOIN)\s+(ORCHARD_APP[A-Z0-9_]*\.[A-Z_]+\.[A-Z_][A-Z0-9_]+)'
        intelligence_pattern = r'(?:FROM|JOIN)\s+(intelligence\.[A-Z_]+\.[A-Z_][A-Z0-9_]+)'
        
        for pattern in [royalty_pattern, orchard_pattern, intelligence_pattern]:
            matches = re.findall(pattern, sql_upper)
            for match in matches:
                # Keep the last two parts (schema.table)
                parts = match.split('.')
                if len(parts) >= 2:
                    clean_name = f"{parts[-2]}.{parts[-1]}".lower().replace('_', ' ').title().replace(' ', '_')
                    tables.append(clean_name)
        
        # Remove duplicates and return first 8 most relevant tables
        unique_tables = list(dict.fromkeys(tables))  # Preserves order while removing duplicates
        return unique_tables[:8]
    
    def scan_all_files(self):
        """Scan all .lkml files in the project"""
        print("Scanning Looker files...")
        
        # Find all model files
        model_files = list(self.project_root.glob('**/*.model.lkml'))
        print(f"Found {len(model_files)} model files")
        
        for model_file in model_files:
            metadata = self.parse_lkml_file(model_file)
            if metadata:
                model_info = self.extract_model_info(metadata['content'])
                metadata.update(model_info)
                # Extract proper model name by removing .model.lkml suffix
                model_key = model_file.name.replace('.model.lkml', '')
                self.models[model_key] = metadata
        
        # Find all view files
        view_files = list(self.project_root.glob('**/*.view.lkml'))
        print(f"Found {len(view_files)} view files")
        
        for view_file in view_files:
            metadata = self.parse_lkml_file(view_file)
            if metadata:
                view_info = self.extract_view_info(metadata['content'])
                metadata.update(view_info)
                # Extract proper view name by removing both .view.lkml suffixes
                view_key = view_file.name.replace('.view.lkml', '')
                self.views[view_key] = metadata
    
    def generate_markdown_docs(self):
        """Generate markdown documentation"""
        print("Generating Markdown documentation...")
        
        # Generate main README
        readme_content = f"""# Abacus Looker Documentation

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

## Project Overview

This repository contains {len(self.models)} Looker models and {len(self.views)} views for the Abacus system.

## Quick Navigation

- [Models Documentation](models/model-inventory.md) - Complete list of all Looker models
- [Views Documentation](views/view-inventory.md) - Complete list of all Looker views  
- [Explores Catalog](explores/explore-catalog.md) - All available explores
- [Data Lineage](data-lineage/dependency-map.md) - View and model dependencies
- [Usage Reports](reports/usage-analysis.md) - Analysis and statistics

## Models Summary

| Model | Label | Connection | Explores | Views Used |
|-------|-------|------------|----------|------------|
"""
        
        for model_name, model_data in sorted(self.models.items()):
            label = model_data.get('label', model_name)
            connection = model_data.get('connection', 'N/A')
            explore_count = len(model_data.get('explores', []))
            include_count = len(model_data.get('includes', []))
            
            readme_content += f"| [{model_name}](models/{model_name}.model.md) | {label} | {connection} | {explore_count} | {include_count} |\n"
        
        readme_content += f"""

## Views Summary

Total views: {len(self.views)}

| View | Table Source | Dimensions | Measures |
|------|-------------|------------|----------|
"""
        
        for view_name, view_data in sorted(list(self.views.items())[:20]):  # Show first 20
            table_name = view_data.get('sql_table_name', 'N/A') or 'N/A'
            # Clean up table names for better display
            if table_name.strip().startswith('intelligence.') or table_name.strip().startswith('royalty_'):
                display_name = table_name.split('.')[-1] if '.' in table_name else table_name
            else:
                display_name = table_name
            display_name = display_name[:50] + ('...' if len(display_name) > 50 else '')
            
            dim_count = len(view_data.get('dimensions', []))
            measure_count = len(view_data.get('measures', []))
            
            readme_content += f"| [{view_name}](views/{view_name}.md) | {display_name} | {dim_count} | {measure_count} |\n"
        
        if len(self.views) > 20:
            readme_content += f"\n*Showing first 20 views. See [complete views inventory](views/view-inventory.md) for all {len(self.views)} views.*\n"
        
        # Write main README
        with open(self.docs_dir / "README.md", 'w') as f:
            f.write(readme_content)
        
        # Generate model inventory
        self._generate_model_inventory()
        
        # Generate view inventory  
        self._generate_view_inventory()
        
        # Generate individual model docs
        self._generate_individual_model_docs()
        
        # Generate individual view docs
        self._generate_individual_view_docs()
        
        # Generate explores catalog
        self._generate_explores_catalog()
        
        # Generate data lineage documentation
        self._generate_data_lineage()
    
    def _generate_model_inventory(self):
        """Generate models inventory page"""
        content = f"""# Models Inventory

Total Models: {len(self.models)}

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

"""
        
        for model_name, model_data in sorted(self.models.items()):
            label = model_data.get('label', model_name)
            connection = model_data.get('connection', 'N/A')
            explores = model_data.get('explores', [])
            includes = model_data.get('includes', [])
            
            content += f"""## [{model_name}]({model_name}.model.md)

- **Label:** {label}
- **Connection:** {connection}
- **File Path:** `{model_data.get('relative_path', 'N/A')}`
- **Explores:** {len(explores)}
- **Includes:** {len(includes)}

"""
            
            if explores:
                content += "**Available Explores:**\n"
                for explore in explores:
                    content += f"- `{explore['name']}` - {explore['label']}\n"
                content += "\n"
        
        with open(self.docs_dir / "models" / "model-inventory.md", 'w') as f:
            f.write(content)
    
    def _generate_view_inventory(self):
        """Generate views inventory page"""
        content = f"""# Views Inventory

Total Views: {len(self.views)}

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

"""
        
        for view_name, view_data in sorted(self.views.items()):
            view_display_name = view_data.get('view_name', view_name)
            table_name = view_data.get('sql_table_name', 'N/A')
            dimensions = view_data.get('dimensions', [])
            measures = view_data.get('measures', [])
            
            content += f"""## [{view_name}]({view_name}.md)

- **View Name:** {view_display_name}
- **Table Source:** `{table_name}`
- **File Path:** `{view_data.get('relative_path', 'N/A')}`
- **Dimensions:** {len(dimensions)}
- **Measures:** {len(measures)}
- **File Size:** {view_data.get('size', 0)} bytes

"""
        
        with open(self.docs_dir / "views" / "view-inventory.md", 'w') as f:
            f.write(content)
    
    def _generate_individual_model_docs(self):
        """Generate individual documentation files for each model"""
        for model_name, model_data in self.models.items():
            content = f"""# Model: {model_name}

**Label:** {model_data.get('label', model_name)}
**Connection:** {model_data.get('connection', 'N/A')}
**File Path:** `{model_data.get('relative_path', 'N/A')}`

## Overview

- **File Size:** {model_data.get('size', 0)} bytes
- **Lines of Code:** {model_data.get('line_count', 0)}
- **Number of Explores:** {len(model_data.get('explores', []))}
- **Number of Includes:** {len(model_data.get('includes', []))}

"""
            
            # Add description if available
            description = model_data.get('description')
            if description:
                content += f"## Description\n\n{description}\n\n"
            
            # Add comments if available
            comments = model_data.get('comments', [])
            if comments:
                content += "## Comments & Notes\n\n"
                for comment in comments:
                    content += f"- {comment}\n"
                content += "\n"
                
            explores = model_data.get('explores', [])
            if explores:
                content += "## Explores\n\n"
                for explore in explores:
                    content += f"""### {explore['name']}
- **Label:** {explore['label']}
- **View:** `{explore['name']}`

"""
            
            includes = model_data.get('includes', [])
            if includes:
                content += "## Included Views\n\n"
                for include in includes:
                    content += f"- `{include}`\n"
                content += "\n"
            
            fiscal_offset = model_data.get('fiscal_month_offset')
            if fiscal_offset is not None:
                content += f"## Configuration\n\n- **Fiscal Month Offset:** {fiscal_offset}\n\n"
            
            with open(self.docs_dir / "models" / f"{model_name}.model.md", 'w') as f:
                f.write(content)
    
    def _generate_individual_view_docs(self):
        """Generate individual documentation files for each view"""
        print(f"Generating individual docs for {len(self.views)} views...")
        success_count = 0
        error_count = 0
        
        for view_name, view_data in self.views.items():
            try:
                view_display_name = view_data.get('view_name', view_name)
                
                content = f"""# View: {view_name}

**View Name:** {view_display_name}
**Table Source:** `{view_data.get('sql_table_name', 'N/A')}`
**File Path:** `{view_data.get('relative_path', 'N/A')}`

## Overview

- **File Size:** {view_data.get('size', 0)} bytes
- **Lines of Code:** {view_data.get('line_count', 0)}
- **Dimensions:** {len(view_data.get('dimensions', []))}
- **Measures:** {len(view_data.get('measures', []))}
- **Dimension Groups:** {len(view_data.get('dimension_groups', []))}
- **Filters:** {len(view_data.get('filters', []))}

"""
                
                # Add description if available
                description = view_data.get('description')
                if description:
                    content += f"## Description\n\n{description}\n\n"
                
                # Add comments if available
                comments = view_data.get('comments', [])
                if comments:
                    content += "## Comments & Notes\n\n"
                    for comment in comments:
                        content += f"- {comment}\n"
                    content += "\n"
                    
                dimensions = view_data.get('dimensions', [])
                if dimensions:
                    content += "## Dimensions\n\n| Name | Type |\n|------|------|\n"
                    for dim in dimensions:
                        content += f"| `{dim['name']}` | {dim['type']} |\n"
                    content += "\n"
                
                measures = view_data.get('measures', [])
                if measures:
                    content += "## Measures\n\n| Name | Type |\n|------|------|\n"
                    for measure in measures:
                        content += f"| `{measure['name']}` | {measure['type']} |\n"
                    content += "\n"
                
                dimension_groups = view_data.get('dimension_groups', [])
                if dimension_groups:
                    content += "## Dimension Groups\n\n| Name | Type |\n|------|------|\n"
                    for dg in dimension_groups:
                        content += f"| `{dg['name']}` | {dg['type']} |\n"
                    content += "\n"
                
                filters = view_data.get('filters', [])
                if filters:
                    content += "## Filters\n\n"
                    for f in filters:
                        content += f"- `{f['name']}`\n"
                    content += "\n"
                
                # Add SQL comments if available (from derived tables)
                sql_comments = view_data.get('sql_comments', [])
                if sql_comments:
                    content += "## SQL Comments\n\n"
                    for comment in sql_comments:
                        content += f"- {comment}\n"
                    content += "\n"
                
                derived_table = view_data.get('derived_table')
                if derived_table:
                    content += f"## Derived Table\n\n```sql\n{derived_table}\n```\n\n"
                    
                with open(self.docs_dir / "views" / f"{view_name}.md", 'w') as f:
                    f.write(content)
                
                success_count += 1
                    
            except Exception as e:
                error_count += 1
                print(f"❌ Error generating view {view_name}: {str(e)}")
                import traceback
                traceback.print_exc()
        
        print(f"Individual view docs generation complete: {success_count} success, {error_count} errors")
        if error_count > 0:
            print(f"⚠️  WARNING: {error_count} view files failed to generate")
    
    def _generate_explores_catalog(self):
        """Generate explores catalog documentation"""
        content = f"""# Explores Catalog

Total Explores: {sum(len(model.get('explores', [])) for model in self.models.values())}

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

This catalog shows all available explores across the Looker project, organized by model.

"""
        
        for model_name, model_data in sorted(self.models.items()):
            explores = model_data.get('explores', [])
            if explores:
                model_label = model_data.get('label', model_name)
                connection = model_data.get('connection', 'N/A')
                
                content += f"""## Model: {model_name}

- **Label:** {model_label}
- **Connection:** {connection}
- **File:** `{model_data.get('relative_path', 'N/A')}`
- **Explores:** {len(explores)}

### Available Explores

"""
                for explore in explores:
                    content += f"""#### {explore['name']}
- **Label:** {explore['label']}
- **Base View:** `{explore['name']}`
- **Model:** {model_name}

"""
        
        with open(self.docs_dir / "explores" / "explore-catalog.md", 'w') as f:
            f.write(content)
    
    def _generate_data_lineage(self):
        """Generate data lineage documentation"""
        content = f"""# Data Lineage & Dependencies

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

This document shows the relationships and dependencies between models, views, and data sources.

## Overview

- **Total Models:** {len(self.models)}
- **Total Views:** {len(self.views)}
- **Derived Views:** {len([v for v in self.views.values() if v.get('derived_table')])}
- **Direct Table Views:** {len([v for v in self.views.values() if not v.get('derived_table')])}

## Model Dependencies

### Models and Their Views

"""
        
        for model_name, model_data in sorted(self.models.items()):
            includes = model_data.get('includes', [])
            if includes:
                content += f"""#### {model_name}
- **Includes:** {len(includes)} views
- **Views:**
"""
                for include in includes:
                    view_name = include.replace('/views/', '').replace('.view.lkml', '')
                    content += f"  - `{view_name}`\n"
                content += "\n"
        
        content += """## View Dependencies

### Views by Data Source

#### Direct Database Tables
Views that directly reference database tables:

"""
        
        # Group views by their source tables
        table_sources = {}
        derived_views = []
        
        for view_name, view_data in self.views.items():
            sql_table = view_data.get('sql_table_name', '')
            if view_data.get('derived_table'):
                derived_views.append(view_name)
            elif sql_table and not sql_table.startswith('Derived from:'):
                # Clean up table name for grouping
                clean_table = sql_table
                if '.' in clean_table:
                    parts = clean_table.split('.')
                    clean_table = '.'.join(parts[-2:]) if len(parts) > 1 else clean_table
                
                if clean_table not in table_sources:
                    table_sources[clean_table] = []
                table_sources[clean_table].append(view_name)
        
        for table, views in sorted(table_sources.items()):
            content += f"""**{table}**
- Used by {len(views)} views: {', '.join(f'`{v}`' for v in views[:5])}
{'  - And ' + str(len(views) - 5) + ' more...' if len(views) > 5 else ''}

"""
        
        content += f"""#### Derived Tables
Views with custom SQL (complex transformations):

Total: {len(derived_views)} views

"""
        
        # Show derived views with their complexity
        derived_with_complexity = []
        for view_name in derived_views:
            view_data = self.views.get(view_name, {})
            line_count = view_data.get('line_count', 0)
            sql_table = view_data.get('sql_table_name', '')
            derived_with_complexity.append((view_name, line_count, sql_table))
        
        # Sort by complexity (line count)
        derived_with_complexity.sort(key=lambda x: x[1], reverse=True)
        
        content += "| View | Lines of Code | Source Tables |\n|------|---------------|---------------|\n"
        
        for view_name, lines, sources in derived_with_complexity[:20]:  # Show top 20
            if sources.startswith('Derived from:'):
                source_display = sources[13:60] + '...' if len(sources) > 73 else sources[13:]
            else:
                source_display = 'Custom SQL'
            content += f"| `{view_name}` | {lines} | {source_display} |\n"
        
        if len(derived_with_complexity) > 20:
            content += f"\n*Showing top 20 most complex derived views. Total: {len(derived_with_complexity)}*\n"
        
        content += """

## Explore Relationships

### Explores and Their Dependencies

"""
        
        for model_name, model_data in sorted(self.models.items()):
            explores = model_data.get('explores', [])
            if explores:
                content += f"""#### Model: {model_name}

"""
                for explore in explores:
                    base_view = explore['name']
                    view_data = self.views.get(base_view, {})
                    view_type = "Derived Table" if view_data.get('derived_table') else "Direct Table"
                    source = view_data.get('sql_table_name', 'Unknown')
                    
                    content += f"""**{explore['name']}** ({explore['label']})
- Base View: `{base_view}`
- View Type: {view_type}
- Data Source: {source[:80] + '...' if len(source) > 80 else source}

"""
        
        content += """## Impact Analysis Guide

### Making Changes Safely

1. **Before changing a database table:**
   - Check "Views by Data Source" section above
   - Identify all views that use the table
   - Review explores that depend on those views

2. **Before modifying a view:**
   - Check which models include the view
   - Review explores that use the view as base
   - Consider downstream reporting impact

3. **Before changing a model:**
   - Review all explores in the model
   - Check business usage of those explores
   - Coordinate with report consumers

### Complexity Indicators

- **High Line Count Views**: Complex business logic, may need optimization
- **Many Source Tables**: Complex joins, potential performance impact
- **Multiple Dependencies**: Changes have wide impact, need careful testing

"""
        
        with open(self.docs_dir / "data-lineage" / "dependency-map.md", 'w') as f:
            f.write(content)
    
    def generate_csv_catalog(self):
        """Generate CSV data catalog"""
        print("Generating CSV data catalog...")
        
        # Models CSV
        models_data = []
        for model_name, model_data in self.models.items():
            models_data.append({
                'Model Name': model_name,
                'Label': model_data.get('label', ''),
                'Connection': model_data.get('connection', ''),
                'File Path': model_data.get('relative_path', ''),
                'Explores Count': len(model_data.get('explores', [])),
                'Includes Count': len(model_data.get('includes', [])),
                'File Size (bytes)': model_data.get('size', 0),
                'Lines of Code': model_data.get('line_count', 0),
                'Fiscal Month Offset': model_data.get('fiscal_month_offset', ''),
            })
        
        models_df = pd.DataFrame(models_data)
        models_df.to_csv(self.docs_dir / "reports" / "models_catalog.csv", index=False)
        
        # Views CSV
        views_data = []
        for view_name, view_data in self.views.items():
            views_data.append({
                'View Name': view_name,
                'Display Name': view_data.get('view_name', ''),
                'Table Source': view_data.get('sql_table_name', ''),
                'File Path': view_data.get('relative_path', ''),
                'Dimensions Count': len(view_data.get('dimensions', [])),
                'Measures Count': len(view_data.get('measures', [])),
                'Dimension Groups Count': len(view_data.get('dimension_groups', [])),
                'Filters Count': len(view_data.get('filters', [])),
                'File Size (bytes)': view_data.get('size', 0),
                'Lines of Code': view_data.get('line_count', 0),
                'Has Derived Table': bool(view_data.get('derived_table'))
            })
        
        views_df = pd.DataFrame(views_data)
        views_df.to_csv(self.docs_dir / "reports" / "views_catalog.csv", index=False)
        
        # Explores CSV
        explores_data = []
        for model_name, model_data in self.models.items():
            for explore in model_data.get('explores', []):
                explores_data.append({
                    'Model': model_name,
                    'Explore Name': explore['name'],
                    'Explore Label': explore['label'],
                    'Model Label': model_data.get('label', ''),
                    'Connection': model_data.get('connection', '')
                })
        
        explores_df = pd.DataFrame(explores_data)
        explores_df.to_csv(self.docs_dir / "reports" / "explores_catalog.csv", index=False)
        
        print(f"Generated CSV catalogs:")
        print(f"- Models: {len(models_data)} records")
        print(f"- Views: {len(views_data)} records") 
        print(f"- Explores: {len(explores_data)} records")
    
    def generate_reports(self):
        """Generate analysis and usage reports"""
        print("Generating usage reports...")
        
        # Analysis report
        content = f"""# Usage Analysis Report

Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

## Project Statistics

### Models Overview
- **Total Models:** {len(self.models)}
- **Models with Labels:** {sum(1 for m in self.models.values() if m.get('label'))}
- **Unique Connections:** {len(set(m.get('connection', '') for m in self.models.values() if m.get('connection')))}
- **Total Explores:** {sum(len(m.get('explores', [])) for m in self.models.values())}

### Views Overview  
- **Total Views:** {len(self.views)}
- **Views with SQL Tables:** {sum(1 for v in self.views.values() if v.get('sql_table_name'))}
- **Views with Derived Tables:** {sum(1 for v in self.views.values() if v.get('derived_table'))}
- **Total Dimensions:** {sum(len(v.get('dimensions', [])) for v in self.views.values())}
- **Total Measures:** {sum(len(v.get('measures', [])) for v in self.views.values())}

### Code Statistics
- **Total Lines of Code:** {sum(m.get('line_count', 0) for m in self.models.values()) + sum(v.get('line_count', 0) for v in self.views.values())}
- **Average Model Size:** {sum(m.get('line_count', 0) for m in self.models.values()) / max(len(self.models), 1):.1f} lines
- **Average View Size:** {sum(v.get('line_count', 0) for v in self.views.values()) / max(len(self.views), 1):.1f} lines

## Top Models by Explores

"""
        
        models_by_explores = sorted(
            [(name, len(data.get('explores', []))) for name, data in self.models.items()],
            key=lambda x: x[1], reverse=True
        )[:10]
        
        for model_name, explore_count in models_by_explores:
            content += f"- **{model_name}:** {explore_count} explores\n"
        
        content += "\n## Top Views by Fields\n\n"
        
        views_by_fields = sorted(
            [(name, len(data.get('dimensions', [])) + len(data.get('measures', []))) 
             for name, data in self.views.items()],
            key=lambda x: x[1], reverse=True
        )[:10]
        
        for view_name, field_count in views_by_fields:
            content += f"- **{view_name}:** {field_count} fields\n"
        
        # Connection usage
        connection_usage = {}
        for model_data in self.models.values():
            conn = model_data.get('connection', 'Unknown')
            connection_usage[conn] = connection_usage.get(conn, 0) + 1
        
        content += "\n## Connection Usage\n\n"
        for conn, count in sorted(connection_usage.items(), key=lambda x: x[1], reverse=True):
            content += f"- **{conn}:** {count} models\n"
        
        with open(self.docs_dir / "reports" / "usage-analysis.md", 'w') as f:
            f.write(content)
    
    def run(self):
        """Run the complete documentation generation process"""
        print("🚀 Starting Abacus Looker Documentation Generation")
        print("=" * 60)
        
        # Create necessary directories
        self._create_directories()
        
        # Scan all files
        self.scan_all_files()
        
        # Generate all documentation formats
        self.generate_markdown_docs()
        self.generate_csv_catalog() 
        self.generate_reports()
        
        print("=" * 60)
        print("✅ Documentation generation complete!")
    
    def _create_directories(self):
        """Create necessary documentation directories"""
        directories = [
            self.docs_dir,
            self.docs_dir / "models",
            self.docs_dir / "views",
            self.docs_dir / "explores",
            self.docs_dir / "data-lineage",
            self.docs_dir / "reports",
        ]
        for directory in directories:
            directory.mkdir(parents=True, exist_ok=True)
        print(f"📁 Created documentation directories in: {self.docs_dir}")
        print(f"📁 Documentation available in: {self.docs_dir}")
        print("\n📋 Generated files:")
        print("  - README.md (Main documentation)")
        print("  - models/ (Individual model documentation)")
        print("  - views/ (Individual view documentation)")
        print("  - explores/ (Explores catalog)")
        print("  - data-lineage/ (Dependency mapping)")
        print("  - reports/ (CSV catalogs and analysis)")

if __name__ == "__main__":
    # Get project root (parent of docs directory)
    project_root = Path(__file__).parent.parent
    
    generator = LookerDocGenerator(project_root)
    generator.run()