from django.core.management.base import BaseCommand
from django.db import connection
from role.models import Profil, Role, RoleAssignment, Permission

class Command(BaseCommand):
    help = 'Create role tables manually'

    def handle(self, *args, **options):
        with connection.cursor() as cursor:
            # Create Profil table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS role_profil (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL UNIQUE,
                    nama_lengkap VARCHAR(100) NOT NULL,
                    email VARCHAR(254),
                    no_telepon VARCHAR(20),
                    foto_profil VARCHAR(100),
                    jabatan VARCHAR(100),
                    departemen VARCHAR(100),
                    level_akses VARCHAR(20) NOT NULL DEFAULT 'operator',
                    alamat TEXT,
                    tanggal_lahir DATE,
                    jenis_kelamin VARCHAR(10),
                    is_active BOOLEAN NOT NULL DEFAULT 1,
                    created_at DATETIME NOT NULL,
                    updated_at DATETIME NOT NULL,
                    created_by_id INTEGER,
                    updated_by_id INTEGER,
                    FOREIGN KEY (user_id) REFERENCES auth_user (id),
                    FOREIGN KEY (created_by_id) REFERENCES auth_user (id),
                    FOREIGN KEY (updated_by_id) REFERENCES auth_user (id)
                )
            ''')
            
            # Create Role table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS role_role (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(50) NOT NULL UNIQUE,
                    role_type VARCHAR(20) NOT NULL,
                    description TEXT,
                    permissions TEXT NOT NULL DEFAULT '[]',
                    is_active BOOLEAN NOT NULL DEFAULT 1,
                    created_at DATETIME NOT NULL,
                    updated_at DATETIME NOT NULL
                )
            ''')
            
            # Create RoleAssignment table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS role_roleassignment (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    role_id INTEGER NOT NULL,
                    assigned_by_id INTEGER,
                    assigned_at DATETIME NOT NULL,
                    is_active BOOLEAN NOT NULL DEFAULT 1,
                    notes TEXT,
                    FOREIGN KEY (user_id) REFERENCES auth_user (id),
                    FOREIGN KEY (role_id) REFERENCES role_role (id),
                    FOREIGN KEY (assigned_by_id) REFERENCES auth_user (id),
                    UNIQUE (user_id, role_id)
                )
            ''')
            
            # Create Permission table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS role_permission (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(100) NOT NULL UNIQUE,
                    codename VARCHAR(100) NOT NULL UNIQUE,
                    permission_type VARCHAR(20) NOT NULL,
                    description TEXT,
                    module VARCHAR(50) NOT NULL,
                    is_active BOOLEAN NOT NULL DEFAULT 1,
                    created_at DATETIME NOT NULL
                )
            ''')
            
            self.stdout.write(
                self.style.SUCCESS('Successfully created role tables!')
            )