import psycopg2
from sqlalchemy import create_engine, text

# Connection parameters
engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres')
connection = engine.connect()

connection.execute(text('''
SELECT * FROM drivers WHERE team IN ('Ferrari', 'Mercedes');
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE victories BETWEEN 50 AND 100;
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE team NOT IN ('Ferrari', 'Mercedes');
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE victories IS NULL;
''')).fetchall()

connection.execute(text('''
SELECT driver_name, COALESCE(victories, 30) FROM drivers;
''')).fetchall()

connection.execute(text('''
SELECT 
  driver_name,
  COALESCE(victories, 
    (SELECT AVG(victories)::int
     FROM drivers 
     WHERE victories IS NOT NULL)) 
FROM drivers;
''')).fetchall()

connection.execute(text('''
INSERT INTO drivers (driver_name, team, nationality, victories)
VALUES 
    ('Valtteri Bottas', 'Mercedes', 'Finnish', 10),
    ('Sergio Perez', 'Red Bull Racing', 'Mexican', 5),
    ('Lando Norris', 'McLaren', 'British', 2),
    ('Esteban Ocon', 'Ferrari', 'French', 1)
''')) 

connection.execute(text('''
SELECT driver_name, team, victories,
    ROUND(AVG(victories) OVER (), 2) AS avg_victories,
    ROUND(AVG(victories) OVER (PARTITION BY team), 2) AS avg_victories_team,
    RANK() OVER (ORDER BY victories DESC) AS rank
FROM drivers
ORDER BY victories DESC;
''')).fetchall()

connection.execute(text('''
SELECT driver_name, 
    LEFT(driver_name, 5) AS first_name,
    RIGHT(driver_name, 8) AS last_name,
    LENGTH(driver_name) AS name_length,
    UPPER(driver_name) AS upper_name,
    LOWER(driver_name) AS lower_name,
    INITCAP(driver_name) AS initcap_name
FROM drivers;
''')).fetchall()

connection.execute(text('''
SELECT driver_name, 
    TRIM(driver_name) AS trimmed_name,
    STRPOS(driver_name, 'a') AS a_position,
    REPLACE(driver_name, ' ', '_') AS replaced_name,
    CONCAT(driver_name, ' is a great driver!') AS message
FROM drivers;
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE victories IS NULL;
''')).fetchall()

connection.execute(text('''
SELECT driver_name, 
    CASE 
        WHEN nationality IS NULL THEN 'Spanish'
        ELSE nationality
    END,
    CASE 
        WHEN victories IS NULL THEN 30
        ELSE victories
    END
FROM drivers
''')).fetchall()

# Create employees table first, then insert data
'Alice', 'HR', 5000
'Bob', 'IT', 6000
'Charlie', 'HR', 7000
'David', 'IT', 8000

connection.execute(text('''
DROP TABLE IF EXISTS student_scores;
CREATE TABLE student_scores (
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INTEGER,
    term VARCHAR(10)
);

INSERT INTO student_scores (student_name, subject, score, term)
VALUES 
    ('Alice', 'Maths', 90, 'Q1'),
    ('Alice', 'Maths', 80, 'Q2'),
    ('Alice', 'Science', 80, 'Q1'),
    ('Alice', 'Science', 75, 'Q2'),
    ('Bob', 'Maths', 80, 'Q1'),
    ('Bob', 'Maths', 100, 'Q2'),
    ('Bob', 'Science', 80, 'Q1'),
    ('Bob', 'Science', 70, 'Q2'),
    ('Charles', 'Maths', 70, 'Q1'),
    ('Charles', 'Maths', 75, 'Q2'),
    ('Charles', 'Science', 90, 'Q1'),
    ('Charles', 'Science', 85, 'Q2')
'''))

connection.commit()

connection.execute(text('SELECT * FROM student_scores')).fetchall()

df2 = pd.read_sql('''
SELECT 
    student_name,
    AVG(CASE WHEN subject = 'Maths' THEN score END) as Maths,
    AVG(CASE WHEN subject = 'Science' THEN score END) as Science,
    AVG(score) as total
FROM student_scores
GROUP BY student_name
ORDER BY total DESC;
''', connection)

df2

#} eval: true
connection.execute(text('CREATE EXTENSION IF NOT EXISTS tablefunc;'))
connection.commit()

df3 = pd.read_sql('''
DROP TABLE IF EXISTS ct;
SELECT student_name, Maths, Science, (Maths + Science)/2 as Total
FROM crosstab(
    'SELECT student_name, subject, AVG(score)
     FROM student_scores
     GROUP BY student_name, subject
     ORDER BY student_name, subject',
    'SELECT DISTINCT subject FROM student_scores ORDER BY subject'
) AS ct (
    student_name VARCHAR, Maths NUMERIC, Science NUMERIC
);
''', connection)

df3

connection.execute(text('''
SELECT * FROM drivers WHERE driver_name LIKE 'M%';
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE nationality LIKE '_______';
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE LENGTH(nationality) = 7;
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE driver_name LIKE 'L%' OR driver_name LIKE 'M%';
''')).fetchall()

connection.execute(text('''
SELECT * FROM drivers WHERE victories BETWEEN 1 AND 10;
''')).fetchall()

connection.execute(text('''
SELECT driver_name, nationality, victories,
    RANK() OVER (PARTITION BY nationality ORDER BY victories DESC) AS rank_nationality
FROM drivers
WHERE victories IS NOT NULL
ORDER BY rank_nationality
''')).fetchall()

connection.execute(text('''
SELECT driver_name, 
    CASE 
        WHEN victories < 10 THEN 'Beginner'
        WHEN victories BETWEEN 10 AND 100 THEN 'Intermediate'
        ELSE 'Expert'
    END AS driver_level
FROM drivers;
''')).fetchall()

connection.execute(text('''
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);
'''))

connection.execute(text('''
INSERT INTO employees (employee_name, department, salary)
VALUES 
    ('Alice', 'HR', 5000),
    ('Bob', 'IT', 6000),
    ('Charlie', 'HR', 7000),
    ('David', 'IT', 8000)
'''))

connection.commit()

# Read data with pandas
pd.read_sql('SELECT * FROM employees', connection)

# Compute average salary by department
pd.read_sql('SELECT department, AVG(salary) FROM employees GROUP BY department', connection)

from sqlalchemy import create_engine, text, inspect
import time

engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres', future=True)
connection = engine.connect()

def clean_database(engine):
    with engine.connect() as conn:
        try:
            # Get inspector to check existing tables
            inspector = inspect(engine)
            existing_tables = inspector.get_table_names()
            
            if not existing_tables:
                print("No tables found in database")
                return
                
            print(f"Found {len(existing_tables)} tables: {existing_tables}")
            
            # Kill other connections
            conn.execute(text("""
                SELECT pg_terminate_backend(pid) 
                FROM pg_stat_activity 
                WHERE pid <> pg_backend_pid()
                AND datname = current_database()
            """))
            
            conn.execute(text("ROLLBACK"))
            conn.execute(text("SET statement_timeout = '30s'"))
            
            # Only drop tables that exist
            for table in existing_tables:
                try:
                    conn.execute(text(f"DROP TABLE IF EXISTS {table} CASCADE"))
                    print(f"Dropped {table}")
                    conn.commit()
                    time.sleep(1)
                except Exception as e:
                    print(f"Error with {table}: {str(e)}")
                    conn.execute(text("ROLLBACK"))
                    
        except Exception as e:
            print(f"Fatal error: {str(e)}")
            conn.execute(text("ROLLBACK"))

# Execute
clean_database(engine)