import psycopg2
from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

def createDatabase(dbuser, dbpass, dbhost, dbname):
    try:
        conn = psycopg2.connect(
            host=dbhost,
            user=dbuser,
            password=dbpass,
            database="postgres"
        )
        conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        cur = conn.cursor()
        
        cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (dbname,))
        exists = cur.fetchone()
        
        if not exists:
            cur.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(dbname)))
            print(f"Database '{dbname}' created successfully")
        else:
            print(f"Database '{dbname}' already exists")
        
        cur.close()
        conn.close()
        
    except Exception as e:
        print(f"Error creating database: {e}")
        raise

def createTables(dbuser, dbpass, dbhost, dbname):
    try:
        conn = psycopg2.connect(
            host=dbhost,
            user=dbuser,
            password=dbpass,
            database=dbname
        )
        cur = conn.cursor()
        
        cur.execute("""
            CREATE TABLE IF NOT EXISTS groups (
                id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
                name VARCHAR(255) UNIQUE NOT NULL,
                parent UUID REFERENCES groups(id),
                creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("Table 'groups' created or already exists")
        
        cur.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
                name VARCHAR(255) NOT NULL,
                username VARCHAR(255) UNIQUE NOT NULL,
                email VARCHAR(255),
                creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                password_hash VARCHAR(255) NOT NULL,
                group_id UUID REFERENCES groups(id)
            )
        """)
        print("Table 'users' created or already exists")
        
        cur.execute("""
            CREATE TABLE IF NOT EXISTS userData (
                id SERIAL PRIMARY KEY,
                user_id uuid REFERENCES users(id),
                service_id VARCHAR(255) NOT NULL,
                creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                property VARCHAR(255) NOT NULL,
                value VARCHAR(255) NOT NULL
            )
        """)
        print("Table 'userData' created or already exists")

        cur.execute("""
            CREATE TABLE IF NOT EXISTS groupData (
                id SERIAL PRIMARY KEY,
                group_id uuid REFERENCES groups(id),
                service_id VARCHAR(255) NOT NULL,
                creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                property VARCHAR(255) NOT NULL,
                value VARCHAR(255) NOT NULL
            )
        """)
        print("Table 'userData' created or already exists")

        cur.execute("""
            CREATE TABLE IF NOT EXISTS logs (
            id SERIAL PRIMARY KEY,
            user_id uuid REFERENCES users(id),
            action VARCHAR(255),
            details VARCHAR(255),
            user_ip VARCHAR(255),
            user_agent VARCHAR(255),
            date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("Table 'logs' created or already exists")

        cur.execute("""
            CREATE TABLE IF NOT EXISTS userTokens (
            id VARCHAR(255) PRIMARY KEY,
            owner_id uuid REFERENCES users(id),
            creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expiration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP + INTERVAL '30 days'
            )
        """)
        print("Table 'userTokens' created or already exists")
        
        conn.commit()
        cur.close()
        conn.close()
        
    except Exception as e:
        print(f"Error creating tables: {e}")
        raise