<?php
/**
 * DMS Application - Automated Test Suite
 * Version: 1.0
 * Date: 2026-02-10
 * 
 * Purpose: Comprehensive automation testing for DMS application
 * Covers: Multiple companies, user roles, RBAC, all CRUD operations
 * 
 * Usage: php run_tests.php [test_name]
 *        php run_tests.php all        - Run all tests
 *        php run_tests.php setup      - Run only setup/seed data
 *        php run_tests.php company    - Run company tests
 *        php run_tests.php user       - Run user tests
 *        php run_tests.php rbac       - Run RBAC tests
 */

// Bootstrap CodeIgniter
$system_path = realpath(dirname(__FILE__) . '/../../system');
$application_folder = realpath(dirname(__FILE__) . '/..');

define('BASEPATH', $system_path . '/');
define('APPPATH', $application_folder . '/');
define('ENVIRONMENT', 'testing');
define('VIEWPATH', APPPATH . 'views/');

// Load CI core
require_once BASEPATH . 'core/Common.php';
require_once APPPATH . 'config/constants.php';

// Test Configuration
class TestConfig {
    public static $base_url = 'http://localhost/dmsnew/';
    public static $db_host = 'localhost';
    public static $db_name = 'new_dms_type';
    public static $db_user = 'postgres';
    public static $db_pass = 'postgres';
    
    public static $test_results = [];
    public static $passed = 0;
    public static $failed = 0;
}

/**
 * Database Connection Helper
 */
class TestDB {
    private static $conn = null;
    
    public static function connect() {
        if (self::$conn === null) {
            $conn_string = sprintf(
                "host=%s dbname=%s user=%s password=%s",
                TestConfig::$db_host,
                TestConfig::$db_name,
                TestConfig::$db_user,
                TestConfig::$db_pass
            );
            self::$conn = pg_connect($conn_string);
            if (!self::$conn) {
                die("ERROR: Could not connect to database\n");
            }
        }
        return self::$conn;
    }
    
    public static function query($sql) {
        $conn = self::connect();
        $result = pg_query($conn, $sql);
        if (!$result) {
            echo "SQL Error: " . pg_last_error($conn) . "\n";
            echo "Query: " . substr($sql, 0, 200) . "...\n";
            return false;
        }
        return $result;
    }
    
    public static function fetchAll($sql) {
        $result = self::query($sql);
        return $result ? pg_fetch_all($result) : [];
    }
    
    public static function fetchOne($sql) {
        $result = self::query($sql);
        return $result ? pg_fetch_assoc($result) : null;
    }
    
    public static function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $values = implode(', ', array_map(function($v) {
            return is_null($v) ? 'NULL' : "'" . pg_escape_string(self::connect(), $v) . "'";
        }, array_values($data)));
        
        $sql = "INSERT INTO $table ($columns) VALUES ($values) RETURNING id";
        $result = self::query($sql);
        if ($result) {
            $row = pg_fetch_assoc($result);
            return $row['id'] ?? true;
        }
        return false;
    }
    
    public static function lastError() {
        return pg_last_error(self::connect());
    }
}

/**
 * HTTP Request Helper for API Testing
 */
class TestHttp {
    private static $cookies = [];
    
    public static function get($url, $params = []) {
        $full_url = TestConfig::$base_url . $url;
        if (!empty($params)) {
            $full_url .= '?' . http_build_query($params);
        }
        return self::request('GET', $full_url);
    }
    
    public static function post($url, $data = []) {
        $full_url = TestConfig::$base_url . $url;
        return self::request('POST', $full_url, $data);
    }
    
    public static function login($email, $password) {
        self::$cookies = []; // Clear cookies for new session
        return self::post('login/secureLogin', [
            'user_email' => $email,
            'user_password' => $password
        ]);
    }
    
    private static function request($method, $url, $data = []) {
        $ch = curl_init();
        
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($ch, CURLOPT_HEADER, true);
        curl_setopt($ch, CURLOPT_COOKIEJAR, '/tmp/dms_test_cookies.txt');
        curl_setopt($ch, CURLOPT_COOKIEFILE, '/tmp/dms_test_cookies.txt');
        
        if ($method === 'POST') {
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
        }
        
        $response = curl_exec($ch);
        $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        
        curl_close($ch);
        
        $headers = substr($response, 0, $header_size);
        $body = substr($response, $header_size);
        
        return [
            'code' => $http_code,
            'headers' => $headers,
            'body' => $body
        ];
    }
}

/**
 * Test Result Logging
 */
class TestLogger {
    public static function pass($test_name, $message = '') {
        TestConfig::$passed++;
        $msg = "✅ PASS: $test_name" . ($message ? " - $message" : "");
        echo $msg . "\n";
        TestConfig::$test_results[] = ['status' => 'pass', 'test' => $test_name, 'message' => $message];
    }
    
    public static function fail($test_name, $message = '') {
        TestConfig::$failed++;
        $msg = "❌ FAIL: $test_name" . ($message ? " - $message" : "");
        echo $msg . "\n";
        TestConfig::$test_results[] = ['status' => 'fail', 'test' => $test_name, 'message' => $message];
    }
    
    public static function info($message) {
        echo "ℹ️  INFO: $message\n";
    }
    
    public static function section($title) {
        echo "\n" . str_repeat("=", 60) . "\n";
        echo "📋 $title\n";
        echo str_repeat("=", 60) . "\n";
    }
    
    public static function summary() {
        echo "\n" . str_repeat("=", 60) . "\n";
        echo "📊 TEST SUMMARY\n";
        echo str_repeat("=", 60) . "\n";
        echo "Total Tests: " . (TestConfig::$passed + TestConfig::$failed) . "\n";
        echo "Passed: " . TestConfig::$passed . " ✅\n";
        echo "Failed: " . TestConfig::$failed . " ❌\n";
        echo "Success Rate: " . round(TestConfig::$passed / max(1, TestConfig::$passed + TestConfig::$failed) * 100, 2) . "%\n";
        echo str_repeat("=", 60) . "\n";
    }
}

// =====================================================
// TEST DATA GENERATORS
// =====================================================

class TestDataGenerator {
    private static $counter = 0;
    
    public static function uniqueId() {
        return ++self::$counter . '_' . time();
    }
    
    public static function companyData($name_suffix = '') {
        $id = self::uniqueId();
        return [
            'company_name' => "Test Company $name_suffix $id",
            'alias_name' => "TC$id",
            'email_id' => "company$id@test.com",
            'mobile_number' => "9" . rand(100000000, 999999999),
            'address' => "Test Address $id, Test City",
            'status' => '1'
        ];
    }
    
    public static function userData($role_id, $name_suffix = '') {
        $id = self::uniqueId();
        return [
            'first_name' => "Test$name_suffix",
            'last_name' => "User$id",
            'email_id' => "testuser$id@test.com",
            'password' => md5('Test@123'),
            'mobile_number' => "9" . rand(100000000, 999999999),
            'role_id' => $role_id,
            'parent_id' => 1,
            'status' => '1',
            'created_at' => date('Y-m-d H:i:s')
        ];
    }
    
    public static function authorityData($name_suffix = '') {
        $id = self::uniqueId();
        return [
            'authority_name' => "Test Authority $name_suffix $id",
            'alias_name' => "TA$id",
            'status' => '1',
            'created_at' => date('Y-m-d')
        ];
    }
    
    public static function documentHeadData($authority_id, $name_suffix = '') {
        $id = self::uniqueId();
        return [
            'type_name' => "Test Document Head $name_suffix $id",
            'alias_name' => "TDH$id",
            'authority_id' => $authority_id,
            'frequency' => 'Monthly',
            'frequency_start_date' => '01',
            'due_in_same_next_month' => 'N',
            'status' => '1',
            'created_at' => date('Y-m-d')
        ];
    }
    
    public static function documentData($type_id, $name_suffix = '') {
        $id = self::uniqueId();
        return [
            'document_name' => "Test Document $name_suffix $id",
            'type_id' => $type_id,
            'status' => '1',
            'is_mandatory' => '1',
            'created_at' => date('Y-m-d')
        ];
    }
    
    public static function holidayData($year = null) {
        $year = $year ?? date('Y');
        $month = rand(1, 12);
        $day = rand(1, 28);
        $id = self::uniqueId();
        return [
            'holiday_name' => "Test Holiday $id",
            'holiday_date' => "$year-" . str_pad($month, 2, '0', STR_PAD_LEFT) . "-" . str_pad($day, 2, '0', STR_PAD_LEFT),
            'created_at' => date('Y-m-d H:i:s')
        ];
    }
}

// =====================================================
// TEST SUITES
// =====================================================

/**
 * Setup Test Data
 */
class SetupTests {
    public static function run() {
        TestLogger::section("SETUP: Creating Test Data");
        
        // Create test companies
        self::createCompanies();
        
        // Create authorities
        self::createAuthorities();
        
        // Create document heads
        self::createDocumentHeads();
        
        // Create documents
        self::createDocuments();
        
        // Create users with different roles
        self::createUsers();
        
        // Create holidays
        self::createHolidays();
    }
    
    private static function createCompanies() {
        TestLogger::info("Creating test companies...");
        
        $companies = [
            ['name' => 'Alpha Corp', 'alias' => 'ALPHA'],
            ['name' => 'Beta Industries', 'alias' => 'BETA'],
            ['name' => 'Gamma Solutions', 'alias' => 'GAMMA'],
            ['name' => 'Delta Technologies', 'alias' => 'DELTA'],
            ['name' => 'Epsilon Services', 'alias' => 'EPSILON']
        ];
        
        foreach ($companies as $index => $company) {
            $data = [
                'company_name' => $company['name'],
                'alias_name' => $company['alias'],
                'email_id' => strtolower($company['alias']) . '@test.com',
                'mobile_number' => '9' . str_pad($index + 1, 9, '0', STR_PAD_LEFT),
                'address' => $company['name'] . ' Address, Test City',
                'status' => '1'
            ];
            
            $id = TestDB::insert('company', $data);
            if ($id) {
                TestLogger::pass("Create Company: {$company['name']}", "ID: $id");
            } else {
                TestLogger::fail("Create Company: {$company['name']}", TestDB::lastError());
            }
        }
    }
    
    private static function createAuthorities() {
        TestLogger::info("Creating test authorities...");
        
        $authorities = [
            ['name' => 'Tax Authority', 'alias' => 'TAX'],
            ['name' => 'Labor Department', 'alias' => 'LABOR'],
            ['name' => 'Social Security', 'alias' => 'SS'],
            ['name' => 'Health & Safety', 'alias' => 'HS'],
            ['name' => 'Environmental Agency', 'alias' => 'ENV']
        ];
        
        foreach ($authorities as $authority) {
            $data = [
                'authority_name' => $authority['name'],
                'alias_name' => $authority['alias'],
                'status' => '1',
                'created_at' => date('Y-m-d')
            ];
            
            $id = TestDB::insert('authority', $data);
            if ($id) {
                TestLogger::pass("Create Authority: {$authority['name']}", "ID: $id");
            } else {
                TestLogger::fail("Create Authority: {$authority['name']}", TestDB::lastError());
            }
        }
    }
    
    private static function createDocumentHeads() {
        TestLogger::info("Creating test document heads...");
        
        // Get authority IDs
        $authorities = TestDB::fetchAll("SELECT id, authority_name FROM authority WHERE status = '1' ORDER BY id");
        
        $frequencies = ['Monthly', 'Quarterly', 'Half-Yearly', 'Yearly'];
        $doc_heads = [
            ['name' => 'Monthly Return', 'freq' => 'Monthly'],
            ['name' => 'Quarterly Filing', 'freq' => 'Quarterly'],
            ['name' => 'Annual Report', 'freq' => 'Yearly'],
            ['name' => 'Half-Year Statement', 'freq' => 'Half-Yearly'],
            ['name' => 'Monthly Compliance', 'freq' => 'Monthly']
        ];
        
        foreach ($authorities as $authority) {
            foreach ($doc_heads as $index => $dh) {
                $data = [
                    'type_name' => $authority['authority_name'] . ' - ' . $dh['name'],
                    'alias_name' => strtoupper(substr($authority['authority_name'], 0, 3)) . '_' . ($index + 1),
                    'authority_id' => $authority['id'],
                    'frequency' => $dh['freq'],
                    'frequency_start_date' => '01',
                    'due_in_same_next_month' => $dh['freq'] === 'Monthly' ? 'N' : 'S',
                    'status' => '1',
                    'created_at' => date('Y-m-d')
                ];
                
                $id = TestDB::insert('sub_type', $data);
                if ($id) {
                    TestLogger::pass("Create Document Head: {$data['type_name']}", "ID: $id");
                } else {
                    TestLogger::fail("Create Document Head: {$data['type_name']}", TestDB::lastError());
                }
            }
        }
    }
    
    private static function createDocuments() {
        TestLogger::info("Creating test documents...");
        
        // Get document head IDs
        $doc_heads = TestDB::fetchAll("SELECT id, type_name FROM sub_type WHERE status = '1' ORDER BY id");
        
        $doc_templates = [
            'Form A', 'Form B', 'Certificate', 'Declaration', 'Statement'
        ];
        
        foreach ($doc_heads as $dh) {
            foreach ($doc_templates as $index => $template) {
                $data = [
                    'document_name' => $template . ' - ' . substr($dh['type_name'], 0, 20),
                    'type_id' => $dh['id'],
                    'status' => '1',
                    'is_mandatory' => $index < 2 ? '1' : '0',
                    'created_at' => date('Y-m-d')
                ];
                
                $id = TestDB::insert('documents', $data);
                if ($id) {
                    TestLogger::pass("Create Document: {$data['document_name']}", "ID: $id");
                } else {
                    // May fail due to unique constraint, which is OK
                    if (strpos(TestDB::lastError(), 'unique') === false) {
                        TestLogger::fail("Create Document: {$data['document_name']}", TestDB::lastError());
                    }
                }
            }
        }
    }
    
    private static function createUsers() {
        TestLogger::info("Creating test users with different roles...");
        
        // Get companies
        $companies = TestDB::fetchAll("SELECT id, company_name FROM company WHERE status = '1' ORDER BY id");
        $authorities = TestDB::fetchAll("SELECT id FROM authority WHERE status = '1' ORDER BY id");
        $doc_heads = TestDB::fetchAll("SELECT id FROM sub_type WHERE status = '1' ORDER BY id");
        
        // User configurations
        $users = [
            // Super Admin (already exists as ID 1)
            
            // Admin Users - each assigned to specific companies
            [
                'first_name' => 'Admin',
                'last_name' => 'Alpha',
                'email_id' => 'admin.alpha@test.com',
                'password' => md5('Admin@123'),
                'mobile_number' => '9111111111',
                'role_id' => 2,
                'parent_id' => 1,
                'status' => '1',
                'companies' => [0, 1], // Alpha, Beta
                'can_view' => true,
                'can_add_edit' => true,
                'can_delete' => true
            ],
            [
                'first_name' => 'Admin',
                'last_name' => 'Gamma',
                'email_id' => 'admin.gamma@test.com',
                'password' => md5('Admin@123'),
                'mobile_number' => '9222222222',
                'role_id' => 2,
                'parent_id' => 1,
                'status' => '1',
                'companies' => [2, 3], // Gamma, Delta
                'can_view' => true,
                'can_add_edit' => true,
                'can_delete' => false // Admin without delete
            ],
            
            // Regular Users - with limited access
            [
                'first_name' => 'User',
                'last_name' => 'ViewOnly',
                'email_id' => 'user.viewonly@test.com',
                'password' => md5('User@123'),
                'mobile_number' => '9333333333',
                'role_id' => 3,
                'parent_id' => 1,
                'status' => '1',
                'companies' => [0], // Alpha only
                'authorities' => [0, 1], // First 2 authorities
                'can_view' => true,
                'can_add_edit' => false,
                'can_delete' => false
            ],
            [
                'first_name' => 'User',
                'last_name' => 'Editor',
                'email_id' => 'user.editor@test.com',
                'password' => md5('User@123'),
                'mobile_number' => '9444444444',
                'role_id' => 3,
                'parent_id' => 1,
                'status' => '1',
                'companies' => [1, 2], // Beta, Gamma
                'authorities' => [0, 1, 2], // First 3 authorities
                'can_view' => true,
                'can_add_edit' => true,
                'can_delete' => false // Users never have delete
            ],
            [
                'first_name' => 'User',
                'last_name' => 'MultiCompany',
                'email_id' => 'user.multi@test.com',
                'password' => md5('User@123'),
                'mobile_number' => '9555555555',
                'role_id' => 3,
                'parent_id' => 1,
                'status' => '1',
                'companies' => [0, 1, 2, 3], // Multiple companies
                'authorities' => [0, 1, 2, 3, 4], // All authorities
                'can_view' => true,
                'can_add_edit' => true,
                'can_delete' => false
            ]
        ];
        
        foreach ($users as $user) {
            $company_indices = $user['companies'] ?? [];
            $authority_indices = $user['authorities'] ?? [];
            unset($user['companies'], $user['authorities']);
            
            $can_view = $user['can_view'] ?? true;
            $can_add_edit = $user['can_add_edit'] ?? false;
            $can_delete = $user['can_delete'] ?? false;
            unset($user['can_view'], $user['can_add_edit'], $user['can_delete']);
            
            $user['created_at'] = date('Y-m-d H:i:s');
            
            $user_id = TestDB::insert('users', $user);
            if (!$user_id) {
                TestLogger::fail("Create User: {$user['email_id']}", TestDB::lastError());
                continue;
            }
            
            TestLogger::pass("Create User: {$user['email_id']}", "ID: $user_id, Role: {$user['role_id']}");
            
            // Add permissions
            TestDB::insert('user_permissions', [
                'user_id' => $user_id,
                'can_view' => $can_view ? 't' : 'f',
                'can_add_edit' => $can_add_edit ? 't' : 'f',
                'can_delete' => $can_delete ? 't' : 'f'
            ]);
            TestLogger::pass("  → Permissions", "View: $can_view, Edit: $can_add_edit, Delete: $can_delete");
            
            // Assign companies
            foreach ($company_indices as $idx) {
                if (isset($companies[$idx])) {
                    TestDB::insert('user_companies', [
                        'user_id' => $user_id,
                        'company_id' => $companies[$idx]['id']
                    ]);
                    TestLogger::pass("  → Company Assignment", $companies[$idx]['company_name']);
                }
            }
            
            // Assign authorities (for User role only)
            if ($user['role_id'] == 3) {
                foreach ($authority_indices as $idx) {
                    if (isset($authorities[$idx])) {
                        TestDB::insert('user_authorities', [
                            'user_id' => $user_id,
                            'authority_id' => $authorities[$idx]['id']
                        ]);
                    }
                }
                TestLogger::pass("  → Authority Assignments", count($authority_indices) . " authorities");
                
                // Auto-assign document types based on authorities
                foreach ($authority_indices as $idx) {
                    if (isset($authorities[$idx])) {
                        $auth_doc_heads = TestDB::fetchAll(
                            "SELECT id FROM sub_type WHERE authority_id = '{$authorities[$idx]['id']}'"
                        );
                        foreach ($auth_doc_heads as $dh) {
                            TestDB::insert('user_document_types', [
                                'user_id' => $user_id,
                                'type_id' => $dh['id']
                            ]);
                        }
                    }
                }
            }
        }
    }
    
    private static function createHolidays() {
        TestLogger::info("Creating test holidays...");
        
        $year = date('Y');
        $holidays = [
            ['name' => 'New Year', 'date' => "$year-01-01"],
            ['name' => 'Independence Day', 'date' => "$year-07-04"],
            ['name' => 'Christmas', 'date' => "$year-12-25"],
            ['name' => 'Labor Day', 'date' => "$year-05-01"],
            ['name' => 'Test Holiday', 'date' => "$year-06-15"]
        ];
        
        foreach ($holidays as $holiday) {
            $data = [
                'holiday_name' => $holiday['name'],
                'holiday_date' => $holiday['date'],
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            $id = TestDB::insert('holidays', $data);
            if ($id) {
                TestLogger::pass("Create Holiday: {$holiday['name']}", "Date: {$holiday['date']}");
            } else {
                if (strpos(TestDB::lastError(), 'unique') === false) {
                    TestLogger::fail("Create Holiday: {$holiday['name']}", TestDB::lastError());
                }
            }
        }
    }
}

/**
 * Company Tests
 */
class CompanyTests {
    public static function run() {
        TestLogger::section("COMPANY TESTS");
        
        self::testCompanyList();
        self::testCompanyCreate();
        self::testCompanyUpdate();
        self::testCompanyDocumentHeadAssignment();
    }
    
    private static function testCompanyList() {
        $companies = TestDB::fetchAll("SELECT * FROM company WHERE status = '1' ORDER BY id");
        
        if (count($companies) >= 5) {
            TestLogger::pass("Company List", "Found " . count($companies) . " companies");
        } else {
            TestLogger::fail("Company List", "Expected at least 5 companies, found " . count($companies));
        }
    }
    
    private static function testCompanyCreate() {
        $data = TestDataGenerator::companyData('CRUD');
        $id = TestDB::insert('company', $data);
        
        if ($id) {
            TestLogger::pass("Company Create (CRUD Test)", "ID: $id");
            
            // Verify data
            $company = TestDB::fetchOne("SELECT * FROM company WHERE id = $id");
            if ($company && $company['company_name'] === $data['company_name']) {
                TestLogger::pass("Company Create Verification", "Data matches");
            } else {
                TestLogger::fail("Company Create Verification", "Data mismatch");
            }
        } else {
            TestLogger::fail("Company Create (CRUD Test)", TestDB::lastError());
        }
    }
    
    private static function testCompanyUpdate() {
        $company = TestDB::fetchOne("SELECT * FROM company ORDER BY id DESC LIMIT 1");
        
        if ($company) {
            $new_name = $company['company_name'] . ' Updated';
            $result = TestDB::query("UPDATE company SET company_name = '$new_name' WHERE id = {$company['id']}");
            
            if ($result) {
                $updated = TestDB::fetchOne("SELECT * FROM company WHERE id = {$company['id']}");
                if ($updated['company_name'] === $new_name) {
                    TestLogger::pass("Company Update", "Name updated successfully");
                } else {
                    TestLogger::fail("Company Update", "Name not updated");
                }
            } else {
                TestLogger::fail("Company Update", TestDB::lastError());
            }
        } else {
            TestLogger::fail("Company Update", "No company found for update test");
        }
    }
    
    private static function testCompanyDocumentHeadAssignment() {
        $company = TestDB::fetchOne("SELECT id FROM company WHERE status = '1' ORDER BY id LIMIT 1");
        $doc_head = TestDB::fetchOne("SELECT id FROM sub_type WHERE status = '1' ORDER BY id LIMIT 1");
        
        if ($company && $doc_head) {
            // Check if already exists
            $existing = TestDB::fetchOne(
                "SELECT * FROM company_document_heads WHERE company_id = {$company['id']} AND type_id = {$doc_head['id']}"
            );
            
            if (!$existing) {
                $id = TestDB::insert('company_document_heads', [
                    'company_id' => $company['id'],
                    'type_id' => $doc_head['id'],
                    'is_enabled' => 't',
                    'created_by' => 1
                ]);
                
                if ($id) {
                    TestLogger::pass("Company Document Head Assignment", "Assigned doc head {$doc_head['id']} to company {$company['id']}");
                } else {
                    TestLogger::fail("Company Document Head Assignment", TestDB::lastError());
                }
            } else {
                TestLogger::pass("Company Document Head Assignment", "Already assigned");
            }
        } else {
            TestLogger::fail("Company Document Head Assignment", "No company or doc head found");
        }
    }
}

/**
 * User Tests
 */
class UserTests {
    public static function run() {
        TestLogger::section("USER TESTS");
        
        self::testUserList();
        self::testUserRoles();
        self::testUserPermissions();
        self::testUserCompanyAssignment();
    }
    
    private static function testUserList() {
        $users = TestDB::fetchAll("SELECT * FROM users WHERE status = '1' ORDER BY id");
        
        if (count($users) >= 5) {
            TestLogger::pass("User List", "Found " . count($users) . " users");
        } else {
            TestLogger::fail("User List", "Expected at least 5 users, found " . count($users));
        }
    }
    
    private static function testUserRoles() {
        // Check Super Admin
        $super_admin = TestDB::fetchOne("SELECT * FROM users WHERE role_id = 1 AND status = '1'");
        if ($super_admin) {
            TestLogger::pass("Super Admin Exists", "ID: {$super_admin['id']}");
        } else {
            TestLogger::fail("Super Admin Exists", "No super admin found");
        }
        
        // Check Admin
        $admins = TestDB::fetchAll("SELECT * FROM users WHERE role_id = 2 AND status = '1'");
        if (count($admins) >= 2) {
            TestLogger::pass("Admin Users Exist", "Found " . count($admins) . " admins");
        } else {
            TestLogger::fail("Admin Users Exist", "Expected at least 2 admins");
        }
        
        // Check Users
        $users = TestDB::fetchAll("SELECT * FROM users WHERE role_id = 3 AND status = '1'");
        if (count($users) >= 3) {
            TestLogger::pass("Regular Users Exist", "Found " . count($users) . " users");
        } else {
            TestLogger::fail("Regular Users Exist", "Expected at least 3 users");
        }
    }
    
    private static function testUserPermissions() {
        // Check permissions for each role type
        $roles = [
            ['role_id' => 1, 'expected_delete' => true, 'name' => 'Super Admin'],
            ['role_id' => 2, 'expected_delete' => null, 'name' => 'Admin'], // Variable
            ['role_id' => 3, 'expected_delete' => false, 'name' => 'User']
        ];
        
        foreach ($roles as $role) {
            $user = TestDB::fetchOne("SELECT u.*, up.can_view, up.can_add_edit, up.can_delete 
                                      FROM users u 
                                      LEFT JOIN user_permissions up ON u.id = up.user_id 
                                      WHERE u.role_id = {$role['role_id']} AND u.status = '1' 
                                      LIMIT 1");
            
            if ($user) {
                $permissions = "View: {$user['can_view']}, Edit: {$user['can_add_edit']}, Delete: {$user['can_delete']}";
                
                if ($role['role_id'] == 3 && ($user['can_delete'] === 't' || $user['can_delete'] === true)) {
                    TestLogger::fail("{$role['name']} Permissions", "User should not have delete permission");
                } else {
                    TestLogger::pass("{$role['name']} Permissions", $permissions);
                }
            } else {
                TestLogger::fail("{$role['name']} Permissions", "No user found with permissions");
            }
        }
    }
    
    private static function testUserCompanyAssignment() {
        $users = TestDB::fetchAll("SELECT u.id, u.email_id, u.role_id, 
                                          (SELECT COUNT(*) FROM user_companies uc WHERE uc.user_id = u.id) as company_count
                                   FROM users u 
                                   WHERE u.status = '1' AND u.role_id IN (2, 3)");
        
        foreach ($users as $user) {
            if ($user['company_count'] > 0) {
                TestLogger::pass("User Company Assignment: {$user['email_id']}", "{$user['company_count']} companies");
            } else {
                TestLogger::fail("User Company Assignment: {$user['email_id']}", "No companies assigned");
            }
        }
    }
}

/**
 * RBAC Tests - Role Based Access Control
 */
class RBACTests {
    public static function run() {
        TestLogger::section("RBAC (Role-Based Access Control) TESTS");
        
        self::testSuperAdminAccess();
        self::testAdminAccess();
        self::testUserAccess();
        self::testCrossCompanyAccess();
    }
    
    private static function testSuperAdminAccess() {
        TestLogger::info("Testing Super Admin access...");
        
        $super_admin = TestDB::fetchOne("SELECT * FROM users WHERE role_id = 1 AND status = '1' LIMIT 1");
        
        if (!$super_admin) {
            TestLogger::fail("Super Admin Access", "No super admin found");
            return;
        }
        
        // Super Admin should see all companies
        $all_companies = TestDB::fetchAll("SELECT * FROM company WHERE status = '1'");
        TestLogger::pass("Super Admin - All Companies Access", count($all_companies) . " companies accessible");
        
        // Super Admin should have full permissions
        $permissions = TestDB::fetchOne("SELECT * FROM user_permissions WHERE user_id = {$super_admin['id']}");
        if ($permissions && $permissions['can_delete'] === 't') {
            TestLogger::pass("Super Admin - Delete Permission", "Has delete access");
        } else {
            TestLogger::fail("Super Admin - Delete Permission", "Should have delete access");
        }
    }
    
    private static function testAdminAccess() {
        TestLogger::info("Testing Admin access...");
        
        $admins = TestDB::fetchAll("SELECT u.*, up.can_view, up.can_add_edit, up.can_delete 
                                    FROM users u 
                                    LEFT JOIN user_permissions up ON u.id = up.user_id 
                                    WHERE u.role_id = 2 AND u.status = '1'");
        
        foreach ($admins as $admin) {
            // Get assigned companies
            $companies = TestDB::fetchAll(
                "SELECT c.* FROM company c 
                 INNER JOIN user_companies uc ON c.id = uc.company_id 
                 WHERE uc.user_id = {$admin['id']}"
            );
            
            if (count($companies) > 0) {
                TestLogger::pass("Admin {$admin['email_id']} - Company Access", count($companies) . " companies");
            } else {
                TestLogger::fail("Admin {$admin['email_id']} - Company Access", "No companies assigned");
            }
            
            // Admin should NOT see other companies (unless all assigned)
            $all_companies = TestDB::fetchAll("SELECT * FROM company WHERE status = '1'");
            if (count($companies) < count($all_companies)) {
                TestLogger::pass("Admin {$admin['email_id']} - Restricted Access", "Cannot see all companies");
            }
        }
    }
    
    private static function testUserAccess() {
        TestLogger::info("Testing User (role 3) access...");
        
        $users = TestDB::fetchAll("SELECT u.*, up.can_view, up.can_add_edit, up.can_delete 
                                   FROM users u 
                                   LEFT JOIN user_permissions up ON u.id = up.user_id 
                                   WHERE u.role_id = 3 AND u.status = '1'");
        
        foreach ($users as $user) {
            // Users should NEVER have delete permission
            if ($user['can_delete'] === 't' || $user['can_delete'] === true) {
                TestLogger::fail("User {$user['email_id']} - Delete Permission", "Users should not have delete access");
            } else {
                TestLogger::pass("User {$user['email_id']} - No Delete Permission", "Correctly restricted");
            }
            
            // Check authority assignments
            $authorities = TestDB::fetchAll(
                "SELECT a.* FROM authority a 
                 INNER JOIN user_authorities ua ON a.id = ua.authority_id 
                 WHERE ua.user_id = {$user['id']}"
            );
            
            if (count($authorities) > 0) {
                TestLogger::pass("User {$user['email_id']} - Authority Access", count($authorities) . " authorities");
            } else {
                TestLogger::fail("User {$user['email_id']} - Authority Access", "No authorities assigned");
            }
            
            // Check document type assignments
            $doc_types = TestDB::fetchAll(
                "SELECT st.* FROM sub_type st 
                 INNER JOIN user_document_types udt ON st.id = udt.type_id 
                 WHERE udt.user_id = {$user['id']}"
            );
            
            TestLogger::pass("User {$user['email_id']} - Document Types", count($doc_types) . " document types");
        }
    }
    
    private static function testCrossCompanyAccess() {
        TestLogger::info("Testing cross-company access restrictions...");
        
        // Get two different admins
        $admins = TestDB::fetchAll("SELECT u.* FROM users u WHERE u.role_id = 2 AND u.status = '1' LIMIT 2");
        
        if (count($admins) < 2) {
            TestLogger::info("Not enough admins for cross-company test");
            return;
        }
        
        $admin1_companies = TestDB::fetchAll(
            "SELECT company_id FROM user_companies WHERE user_id = {$admins[0]['id']}"
        );
        $admin2_companies = TestDB::fetchAll(
            "SELECT company_id FROM user_companies WHERE user_id = {$admins[1]['id']}"
        );
        
        $admin1_ids = array_column($admin1_companies, 'company_id');
        $admin2_ids = array_column($admin2_companies, 'company_id');
        
        // Check for non-overlapping companies
        $only_admin1 = array_diff($admin1_ids, $admin2_ids);
        $only_admin2 = array_diff($admin2_ids, $admin1_ids);
        
        if (!empty($only_admin1) || !empty($only_admin2)) {
            TestLogger::pass("Cross-Company Isolation", "Admins have different company access");
        } else {
            TestLogger::info("Cross-Company Isolation", "Admins have same companies (may be intentional)");
        }
    }
}

/**
 * Master Data Tests
 */
class MasterDataTests {
    public static function run() {
        TestLogger::section("MASTER DATA TESTS");
        
        self::testAuthorities();
        self::testDocumentHeads();
        self::testDocuments();
        self::testHolidays();
    }
    
    private static function testAuthorities() {
        $authorities = TestDB::fetchAll("SELECT * FROM authority WHERE status = '1'");
        
        if (count($authorities) >= 5) {
            TestLogger::pass("Authorities Count", count($authorities) . " authorities");
        } else {
            TestLogger::fail("Authorities Count", "Expected at least 5, found " . count($authorities));
        }
        
        // Test unique alias
        $duplicate = TestDB::fetchOne(
            "SELECT alias_name, COUNT(*) as cnt FROM authority GROUP BY alias_name HAVING COUNT(*) > 1 LIMIT 1"
        );
        
        if (!$duplicate) {
            TestLogger::pass("Authority Alias Uniqueness", "All aliases unique");
        } else {
            TestLogger::fail("Authority Alias Uniqueness", "Duplicate: {$duplicate['alias_name']}");
        }
    }
    
    private static function testDocumentHeads() {
        $doc_heads = TestDB::fetchAll("SELECT * FROM sub_type WHERE status = '1'");
        
        if (count($doc_heads) >= 20) {
            TestLogger::pass("Document Heads Count", count($doc_heads) . " document heads");
        } else {
            TestLogger::fail("Document Heads Count", "Expected at least 20, found " . count($doc_heads));
        }
        
        // Test authority linkage
        $orphan = TestDB::fetchOne(
            "SELECT st.* FROM sub_type st 
             LEFT JOIN authority a ON st.authority_id::int = a.id 
             WHERE a.id IS NULL AND st.status = '1' LIMIT 1"
        );
        
        if (!$orphan) {
            TestLogger::pass("Document Head Authority Link", "All linked to authorities");
        } else {
            TestLogger::fail("Document Head Authority Link", "Orphan found: {$orphan['type_name']}");
        }
        
        // Test frequency values
        $valid_frequencies = ['Monthly', 'Quarterly', 'Half-Yearly', 'Yearly'];
        $invalid = TestDB::fetchOne(
            "SELECT * FROM sub_type WHERE frequency NOT IN ('" . implode("','", $valid_frequencies) . "') AND status = '1' LIMIT 1"
        );
        
        if (!$invalid) {
            TestLogger::pass("Document Head Frequencies", "All valid frequencies");
        } else {
            TestLogger::fail("Document Head Frequencies", "Invalid: {$invalid['frequency']}");
        }
    }
    
    private static function testDocuments() {
        $documents = TestDB::fetchAll("SELECT * FROM documents WHERE status = '1'");
        
        if (count($documents) >= 50) {
            TestLogger::pass("Documents Count", count($documents) . " documents");
        } else {
            TestLogger::info("Documents Count", count($documents) . " documents (may need more for thorough testing)");
        }
        
        // Test document head linkage
        $orphan = TestDB::fetchOne(
            "SELECT d.* FROM documents d 
             LEFT JOIN sub_type st ON d.type_id = st.id 
             WHERE st.id IS NULL AND d.status = '1' LIMIT 1"
        );
        
        if (!$orphan) {
            TestLogger::pass("Document Type Link", "All linked to document heads");
        } else {
            TestLogger::fail("Document Type Link", "Orphan found: {$orphan['document_name']}");
        }
    }
    
    private static function testHolidays() {
        $year = date('Y');
        $holidays = TestDB::fetchAll("SELECT * FROM holidays WHERE EXTRACT(YEAR FROM holiday_date) = $year");
        
        if (count($holidays) >= 5) {
            TestLogger::pass("Holidays for $year", count($holidays) . " holidays");
        } else {
            TestLogger::fail("Holidays for $year", "Expected at least 5, found " . count($holidays));
        }
        
        // Test unique dates
        $duplicate = TestDB::fetchOne(
            "SELECT holiday_date, COUNT(*) as cnt FROM holidays GROUP BY holiday_date HAVING COUNT(*) > 1 LIMIT 1"
        );
        
        if (!$duplicate) {
            TestLogger::pass("Holiday Date Uniqueness", "All dates unique");
        } else {
            TestLogger::fail("Holiday Date Uniqueness", "Duplicate: {$duplicate['holiday_date']}");
        }
    }
}

/**
 * Favourite Tests
 */
class FavouriteTests {
    public static function run() {
        TestLogger::section("FAVOURITE TESTS");
        
        self::testFavouriteLimit();
        self::testMonthLevelFavourite();
    }
    
    private static function testFavouriteLimit() {
        // Test that favourite limit is enforced (max 10)
        $user = TestDB::fetchOne("SELECT * FROM users WHERE status = '1' ORDER BY id LIMIT 1");
        
        if ($user) {
            $count = TestDB::fetchOne("SELECT COUNT(*) as cnt FROM favourites WHERE user_id = {$user['id']}");
            TestLogger::pass("Favourite Count for User {$user['id']}", "{$count['cnt']} favourites");
        }
    }
    
    private static function testMonthLevelFavourite() {
        // Check if month-level favourites table structure exists
        $columns = TestDB::fetchAll(
            "SELECT column_name FROM information_schema.columns WHERE table_name = 'favourites'"
        );
        
        $column_names = array_column($columns, 'column_name');
        $required = ['favourite_type', 'document_month', 'document_year'];
        
        $missing = array_diff($required, $column_names);
        if (empty($missing)) {
            TestLogger::pass("Month-Level Favourite Schema", "All required columns exist");
        } else {
            TestLogger::fail("Month-Level Favourite Schema", "Missing: " . implode(', ', $missing));
        }
    }
}

/**
 * Data Integrity Tests
 */
class DataIntegrityTests {
    public static function run() {
        TestLogger::section("DATA INTEGRITY TESTS");
        
        self::testForeignKeys();
        self::testRequiredFields();
        self::testStatusValues();
    }
    
    private static function testForeignKeys() {
        // Test user_companies foreign keys
        $orphan_uc = TestDB::fetchOne(
            "SELECT uc.* FROM user_companies uc 
             LEFT JOIN users u ON uc.user_id = u.id 
             LEFT JOIN company c ON uc.company_id = c.id 
             WHERE u.id IS NULL OR c.id IS NULL LIMIT 1"
        );
        
        if (!$orphan_uc) {
            TestLogger::pass("User-Company FK Integrity", "All references valid");
        } else {
            TestLogger::fail("User-Company FK Integrity", "Orphan record found");
        }
        
        // Test user_authorities foreign keys
        $orphan_ua = TestDB::fetchOne(
            "SELECT ua.* FROM user_authorities ua 
             LEFT JOIN users u ON ua.user_id = u.id 
             LEFT JOIN authority a ON ua.authority_id = a.id 
             WHERE u.id IS NULL OR a.id IS NULL LIMIT 1"
        );
        
        if (!$orphan_ua) {
            TestLogger::pass("User-Authority FK Integrity", "All references valid");
        } else {
            TestLogger::fail("User-Authority FK Integrity", "Orphan record found");
        }
    }
    
    private static function testRequiredFields() {
        // Test users have required fields
        $invalid_user = TestDB::fetchOne(
            "SELECT * FROM users WHERE email_id IS NULL OR email_id = '' OR role_id IS NULL LIMIT 1"
        );
        
        if (!$invalid_user) {
            TestLogger::pass("User Required Fields", "All users have required data");
        } else {
            TestLogger::fail("User Required Fields", "User {$invalid_user['id']} missing data");
        }
        
        // Test companies have required fields
        $invalid_company = TestDB::fetchOne(
            "SELECT * FROM company WHERE company_name IS NULL OR company_name = '' LIMIT 1"
        );
        
        if (!$invalid_company) {
            TestLogger::pass("Company Required Fields", "All companies have required data");
        } else {
            TestLogger::fail("Company Required Fields", "Company {$invalid_company['id']} missing name");
        }
    }
    
    private static function testStatusValues() {
        // Test status values are valid
        $invalid_status = TestDB::fetchOne(
            "SELECT * FROM users WHERE status NOT IN ('0', '1') AND status IS NOT NULL LIMIT 1"
        );
        
        if (!$invalid_status) {
            TestLogger::pass("User Status Values", "All valid (0 or 1)");
        } else {
            TestLogger::fail("User Status Values", "Invalid status: {$invalid_status['status']}");
        }
    }
}

// =====================================================
// MAIN TEST RUNNER
// =====================================================

function runTests($test_name = 'all') {
    echo "\n";
    echo "╔══════════════════════════════════════════════════════════╗\n";
    echo "║       DMS APPLICATION - AUTOMATED TEST SUITE            ║\n";
    echo "║                    Version 1.0                          ║\n";
    echo "╚══════════════════════════════════════════════════════════╝\n";
    echo "\n";
    echo "Started at: " . date('Y-m-d H:i:s') . "\n";
    echo "Database: " . TestConfig::$db_name . "\n";
    echo "\n";
    
    // Connect to database
    TestDB::connect();
    
    switch (strtolower($test_name)) {
        case 'setup':
            SetupTests::run();
            break;
        case 'company':
            CompanyTests::run();
            break;
        case 'user':
            UserTests::run();
            break;
        case 'rbac':
            RBACTests::run();
            break;
        case 'master':
            MasterDataTests::run();
            break;
        case 'favourite':
            FavouriteTests::run();
            break;
        case 'integrity':
            DataIntegrityTests::run();
            break;
        case 'all':
        default:
            SetupTests::run();
            CompanyTests::run();
            UserTests::run();
            RBACTests::run();
            MasterDataTests::run();
            FavouriteTests::run();
            DataIntegrityTests::run();
            break;
    }
    
    TestLogger::summary();
    
    echo "\nCompleted at: " . date('Y-m-d H:i:s') . "\n";
}

// Run tests
$test_arg = isset($argv[1]) ? $argv[1] : 'all';
runTests($test_arg);

