import pandas as pd
import psycopg2
from sqlalchemy import create_engine, text

# Connect to the database
engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres')
connection = engine.connect()

# Create the tables
connection.execute(text('''
DROP TABLE IF EXISTS players;
CREATE TABLE players (
    player_id SERIAL PRIMARY KEY,
    player_name VARCHAR(50) NOT NULL,
    goals INT NOT NULL,
    victories INT NOT NULL
);
'''))

connection.execute(text('''
DROP TABLE IF EXISTS teams;
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL
);
'''))
connection.commit()

# Insert data into the tables
connection.execute(text('''
INSERT INTO players (player_name, goals, victories) VALUES
('Messi', 10, 5),
('Vini Jr', 8, 4),
('Neymar', 6, 3),
('Mbappe', 5, 2),
('Lewandowski', 4, 1),
('Haaland', 5, 3);
'''))

connection.execute(text('''
INSERT INTO teams (team_name) VALUES
('Inter Miami'),
('Real Madrid'),
('Al Hilal'),
('Real Madrid'),
('Bayern');
'''))
connection.commit()

pd.read_sql('SELECT * FROM players', connection)

pd.read_sql('SELECT * FROM teams', connection)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals, players.victories
FROM players
INNER JOIN teams 
ON players.player_id = teams.team_id;
''', connection)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM players
LEFT JOIN teams
ON players.player_id = teams.team_id;
''', connection)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM players
RIGHT JOIN teams
ON players.player_id = teams.team_id;
''', connection)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM players
FULL OUTER JOIN teams
ON players.player_id = teams.team_id;
''', connection)

# Create the tables and insert data
connection.execute(text('''
DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) -- 10 digits, 2 decimal places for money
);

-- Insert products (notice some won't have reviews)
INSERT INTO products (product_name, price) VALUES
    ('Coffee Maker', 99.99),
    ('Toaster', 29.99),
    ('Blender', 79.99),
    ('Microwave', 149.99),
    ('Air Fryer', 89.99);

DROP TABLE IF EXISTS reviews CASCADE;
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert reviews (only for some products)
INSERT INTO reviews (product_id, rating, comment) VALUES
    (1, 5, 'Great coffee maker!'),
    (1, 4, 'Good but expensive'),
    (2, 3, 'Average toaster'),
    (3, 5, 'Best blender ever');
'''))
connection.commit()

pd.read_sql('''
SELECT players.player_name, teams.team_name
FROM players
CROSS JOIN teams
ORDER BY players.player_id;
''', connection)

# Drop and recreate tables
connection.execute(text('''
DROP TABLE IF EXISTS colors CASCADE;
CREATE TABLE colors (color_name VARCHAR(20));
DROP TABLE IF EXISTS sizes CASCADE;
CREATE TABLE sizes (size_code VARCHAR(5));
INSERT INTO colors VALUES ('Black'), ('Red');
INSERT INTO sizes VALUES ('S'), ('M');
'''))

pd.read_sql(text('''
SELECT 
    colors.color_name, 
    sizes.size_code,
    CONCAT(colors.color_name, ' - ', sizes.size_code) as t_shirt
FROM colors
CROSS JOIN sizes
ORDER BY colors.color_name, sizes.size_code DESC;
'''), connection)

connection.execute(text('''
DROP TABLE IF EXISTS family CASCADE;
CREATE TABLE family (
    person_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    mother_id INT
);

INSERT INTO family (name, mother_id) VALUES
    ('Emma', NULL), -- grandmother
    ('Sarah', 1),   -- Emma's daughter
    ('Lisa', 1),    -- Emma's daughter
    ('Tom', 2),     -- Sarah's son
    ('Alice', 2);   -- Sarah's daughter
'''))

pd.read_sql('''
SELECT 
    children.name as child,
    mothers.name as mother
FROM family children
JOIN family mothers ON children.mother_id = mothers.person_id
ORDER BY mothers.name;
''', connection)

pd.read_sql('''
SELECT 
    p1.player_name,
    p1.goals,
    p2.player_name as compared_to,
    p2.goals as their_goals,
    p1.goals - p2.goals as difference
FROM players p1
JOIN players p2 
ON p1.player_id < p2.player_id
ORDER BY difference DESC;
''', connection)

pd.read_sql('''
SELECT players.player_name, teams.team_name, players.goals
FROM players
NATURAL JOIN teams;
''', connection)

pd.read_sql('''
SELECT player_name, goals FROM players
UNION
SELECT team_name, NULL FROM teams
ORDER BY player_name;
''', connection)

pd.read_sql('''
INSERT INTO players (player_name, goals, victories) VALUES ('Real Madrid', 0, 0); 
SELECT player_name FROM players
EXCEPT
SELECT team_name FROM teams;
''', connection)

connection.execute(text('''
ALTER TABLE teams ADD COLUMN IF NOT EXISTS player_id SERIAL;
ALTER TABLE teams ADD COLUMN IF NOT EXISTS team_player VARCHAR(100);

MERGE INTO teams AS t
USING players AS p
ON t.player_id = p.player_id
WHEN MATCHED THEN
    UPDATE SET team_player = (p.player_name || ' - ' || t.team_name)
WHEN NOT MATCHED THEN
    INSERT (team_name) VALUES (p.player_name);
'''))

pd.read_sql('SELECT * FROM teams', connection)

print("INNER JOIN Results:")
print(pd.read_sql('''
    SELECT p.product_name, r.rating, r.comment
    FROM products p
    INNER JOIN reviews r ON p.product_id = r.product_id
    ORDER BY p.product_id;
''', connection))

print("\nLEFT JOIN Results:")
print(pd.read_sql('''
    SELECT p.product_name, r.rating, r.comment
    FROM products p
    LEFT JOIN reviews r ON p.product_id = r.product_id
    ORDER BY p.product_id;
''', connection))

pd.read_sql('''
SELECT p1.player_name, p1.victories, p2.player_name, p2.victories,
    ROUND(CAST(p1.victories AS NUMERIC) / CAST(p2.victories AS NUMERIC), 2) AS victories_ratio
FROM players p1
JOIN players p2
ON p1.player_id < p2.player_id
WHERE p2.victories > 0
ORDER BY p1.player_id;
''', 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)