#!/usr/bin/env python3
"""Grant DELETE permission on auth database users table.

This script grants DELETE permission to the database user specified in DATABASE_URL_AUTH.
It requires superuser access (postgres user) to grant permissions.
"""

from __future__ import annotations

import os
import sys
from pathlib import Path
from urllib.parse import urlparse

from dotenv import load_dotenv
from sqlalchemy import create_engine, text

# Load environment variables
env_path = Path(__file__).parent.parent.parent / ".env"
load_dotenv(dotenv_path=env_path)


def parse_database_url(db_url: str) -> dict:
    """Parse database URL into components."""
    # Handle postgresql+psycopg2:// format
    if db_url.startswith("postgresql+psycopg2://"):
        db_url = db_url.replace("postgresql+psycopg2://", "postgresql://")
    
    parsed = urlparse(db_url)
    return {
        "user": parsed.username,
        "password": parsed.password,
        "host": parsed.hostname or "localhost",
        "port": parsed.port or 5432,
        "database": parsed.path.lstrip("/"),
    }


def grant_delete_permission() -> None:
    """Grant DELETE permission on users and pending_photos tables in auth database."""
    auth_db_url = os.getenv("DATABASE_URL_AUTH")
    if not auth_db_url:
        print("❌ Error: DATABASE_URL_AUTH environment variable not set")
        sys.exit(1)
    
    if not auth_db_url.startswith("postgresql"):
        print("ℹ️  Auth database is not PostgreSQL. No permissions to grant.")
        return
    
    db_info = parse_database_url(auth_db_url)
    db_user = db_info["user"]
    db_name = db_info["database"]
    
    print(f"📋 Granting DELETE permission on auth database tables...")
    print(f"   Database: {db_name}")
    print(f"   User: {db_user}")
    
    # Tables that need DELETE permission
    tables = ["users", "pending_photos", "pending_identifications", "inappropriate_photo_reports"]
    
    # Connect as postgres superuser to grant permissions
    # Try to connect as postgres user (superuser)
    try:
        # Try to get postgres password from environment or use peer authentication
        postgres_url = f"postgresql://postgres@{db_info['host']}:{db_info['port']}/{db_name}"
        engine = create_engine(postgres_url)
        
        with engine.connect() as conn:
            for table in tables:
                try:
                    # Grant DELETE permission
                    conn.execute(text(f"""
                        GRANT DELETE ON TABLE {table} TO {db_user}
                    """))
                    print(f"   ✅ Granted DELETE on {table}")
                except Exception as e:
                    # Table might not exist, skip it
                    print(f"   ⚠️  Could not grant DELETE on {table}: {e}")
            conn.commit()
        
        print(f"✅ Successfully granted DELETE permissions to user '{db_user}'")
        return
    except Exception as e:
        # If connecting as postgres fails, try with the same user (might have grant privileges)
        print(f"⚠️  Could not connect as postgres user: {e}")
        print(f"   Trying with current database user...")
        
        try:
            engine = create_engine(auth_db_url)
            with engine.connect() as conn:
                for table in tables:
                    try:
                        # Try to grant permission
                        conn.execute(text(f"""
                            GRANT DELETE ON TABLE {table} TO {db_user}
                        """))
                        print(f"   ✅ Granted DELETE on {table}")
                    except Exception as e2:
                        print(f"   ⚠️  Could not grant DELETE on {table}: {e2}")
                conn.commit()
            
            print(f"✅ Successfully granted DELETE permissions to user '{db_user}'")
            return
        except Exception as e2:
            print(f"❌ Failed to grant permission: {e2}")
            print(f"\n💡 To grant permission manually, run as postgres superuser:")
            for table in tables:
                print(f"   sudo -u postgres psql -d {db_name} -c \"GRANT DELETE ON TABLE {table} TO {db_user};\"")
            sys.exit(1)


if __name__ == "__main__":
    grant_delete_permission()


