import re
from typing import Dict
from .base import BasePRWaiter, BasePromptBuilder, BasePRAnalyzer


class DatabasePRWaiter(BasePRWaiter):
    """Database PR waiter that checks for migration validation."""

    def wait_for_analysis_ready(
        self, github_client, pr_number: int, timeout: int = 600
    ) -> Dict:
        """Wait for database migration validation before analysis."""
        # For now, proceed immediately for database repos
        # In the future, this could check for migration validation CI, syntax checks, etc.
        pr_data = github_client.get_pr_details(pr_number)
        return pr_data


class DatabasePromptBuilder(BasePromptBuilder):
    """Database migration review prompt builder for Liquibase/Liquigraph."""

    def __init__(self):
        self.base_prompt = self._get_database_prompt_template()

    def _get_database_prompt_template(self) -> str:
        """Get the database migration review prompt template."""
        return """# Database Migration Review Prompt

You are an AI assistant tasked with reviewing a database migration pull request. This repository contains database schema and data migrations using Liquibase (for relational databases) and Liquigraph (for Neo4j graph databases). You will be given the PR title, description, and migration files. Perform a thorough review, focusing on the following key areas:

1. **Migration Safety & Data Integrity**
   - Ensure migrations are reversible where possible (proper rollback strategies)
   - Check for potential data loss operations (DROP TABLE, DROP COLUMN, etc.)
   - Verify that schema changes maintain data integrity and referential constraints
   - Look for operations that could cause downtime or lock issues (ALTER TABLE on large tables)
   - Ensure proper backup/restore considerations for destructive operations
   - Check for proper transaction handling and atomicity

2. **Liquibase/Liquigraph Best Practices**
   - **Liquibase**: Verify proper changeset structure, author, and ID uniqueness
   - **Liquigraph**: Check Cypher query syntax and graph model consistency
   - Ensure changesets are immutable (no modifications to existing changesets)
   - Verify proper file naming conventions and directory structure
   - Check for appropriate preconditions and rollback statements
   - Validate proper use of contexts and labels for environment targeting

3. **Schema Design & Performance**
   - Review table/node structures for normalization and efficiency
   - Check index creation for query performance optimization
   - Ensure proper data types and constraints are used
   - Look for potential performance bottlenecks in large data operations
   - Verify foreign key relationships and graph relationships are logical
   - Check for proper partitioning strategies if applicable

4. **Security & Access Control**
   - Ensure no sensitive data is exposed in migration scripts
   - Verify proper permission and role management changes
   - Check for SQL injection vulnerabilities in dynamic queries
   - Ensure encryption requirements are met for sensitive columns
   - Validate that database users have appropriate access levels
   - Review any stored procedures or functions for security issues

5. **Migration Sequencing & Dependencies**
   - Verify migration order and dependencies between changesets
   - Check for conflicts with existing schema or data
   - Ensure migrations can be applied in different environments consistently
   - Validate that migration files follow proper versioning conventions
   - Check for proper handling of environment-specific differences
   - Ensure migrations work correctly with existing data

6. **Data Quality & Validation**
   - Review data migration scripts for accuracy and completeness
   - Check for proper data type conversions and transformations
   - Ensure data validation rules and constraints are maintained
   - Verify that data migrations handle edge cases and null values
   - Check for proper error handling in data transformation scripts
   - Validate that migrated data maintains business logic consistency

After analyzing the PR against the above points, **separate your feedback into two categories**: **Blocking Issues** and **Suggestions for Improvement**.

- **Blocking Issues** should detail any problems that **must** be addressed before merging. These include potential data loss, security vulnerabilities, migration syntax errors, or operations that could cause significant downtime.

- **Suggestions for Improvement** should cover optional improvements that would enhance migration safety, performance, or maintainability but don't necessarily block the merge.

**Tone and style**: Present your feedback in a **constructive and knowledgeable manner**. Focus on database-specific concerns and provide specific recommendations for migration best practices.

**Output format**: Use clear markdown formatting with headers for "Blocking Issues" and "Suggestions for Improvement". If there are no blocking issues, state "No blocking issues found" before proceeding to suggestions.

**Migration File Types to Review:**
- Liquibase: .xml, .yaml, .yml, .sql changelog files
- Liquigraph: .cypher, .xml changelog files for Neo4j
- SQL scripts: .sql files with DDL/DML operations
- Configuration: liquibase.properties, liquigraph.properties

PR Title:
{pr_title}

PR Description:
{pr_body}

Migration Changes:
{diff}

Comments (if any):
{comments}
"""

    def build_prompt(self, pr_data: Dict) -> str:
        """Build the complete prompt with PR data."""
        return self.base_prompt.format(
            pr_title=pr_data['pr_title'],
            pr_body=pr_data['pr_body'],
            diff=pr_data['diff'],
            comments='\n'.join(
                [comment['body'] for comment in pr_data.get('comments', [])]
            ),
        )


class DatabasePRAnalyzer(BasePRAnalyzer):
    """Database migration PR analyzer."""

    def post_process_analysis(self, analysis: str) -> str:
        """Standard post-processing for database repos."""
        # De-emphasize headers for GitHub formatting
        return re.sub(r'^#{1,3}(?= )', '####', analysis, flags=re.MULTILINE)