import sqlite3
import os

def create_roles_and_users_tables(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    # Create users table if not exists
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL
        )
    ''')
    # Insert superadmin user if not exists (default password: 'admin', change after first login)
    cursor.execute('''
        INSERT OR IGNORE INTO users (username, password, role)
        VALUES (?, ?, ?)
    ''', ('superadmin', 'superadmin123', 'superadmin'))
    # Create roles table if not exists
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS roles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            access_level TEXT NOT NULL,
            description TEXT
        )
    ''')
    # Insert superadmin role if not exists
    cursor.execute('''
        INSERT OR IGNORE INTO roles (name, access_level, description)
        VALUES (?, ?, ?)
    ''', ('superadmin', 'full', 'Full access to all app areas and functions'))
    conn.commit()
    conn.close()

if __name__ == "__main__":
    # Default path to users.db in instance folder
    instance_folder = os.path.abspath(os.path.join(os.path.dirname(__file__), '../../instance'))
    if not os.path.exists(instance_folder):
        os.makedirs(instance_folder)
    db_path = os.path.join(instance_folder, 'users.db')
    create_roles_and_users_tables(db_path)
    print("Roles and users tables created. Superadmin user and role initialized.")