import os
import mysql.connector
from dotenv import load_dotenv

load_dotenv()

def migrate():
    db = mysql.connector.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASS"),
        database=os.getenv("DB_NAME")
    )
    cursor = db.cursor()

    # Create knowledge_chunks table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS knowledge_chunks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            source_file VARCHAR(255),
            content TEXT,
            FULLTEXT(content)
        ) ENGINE=MyISAM;
    """)
    # Note: Using MyISAM for FULLTEXT index if on older MySQL, or InnoDB if newer.
    # Hostnet usually supports InnoDB with FULLTEXT now, but MyISAM is safer for very old ones.
    # Let's try InnoDB first as it's better for concurrency.
    
    try:
        cursor.execute("ALTER TABLE knowledge_chunks ENGINE = InnoDB;")
        cursor.execute("ALTER TABLE knowledge_chunks ADD FULLTEXT INDEX content_fulltext (content);")
    except:
        pass

    db.commit()
    cursor.close()
    db.close()
    print("✅ Knowledge Base table created!")

if __name__ == "__main__":
    migrate()