<?php
/**
 * DMS Complete Test Suite
 * Version: 2.1
 * Date: 2026-02-10
 * 
 * This script provides comprehensive automated testing for the DMS application.
 * 
 * IMPORTANT: Run truncate using psql BEFORE running this script:
 *   psql -U postgres -d new_dms_type -f complete_truncate.sql
 * 
 * Usage:
 *   php complete_test_suite.php [test_name]
 *   php complete_test_suite.php --seed
 *   php complete_test_suite.php --all
 */

// Configuration
define('BASE_URL', 'http://localhost/dmsnew/');
define('DB_HOST', 'localhost');
define('DB_NAME', 'new_dms_type');
define('DB_USER', 'postgres');
define('DB_PASS', 'postgres');
define('DB_PORT', '5432');

// Test credentials
define('SUPER_ADMIN_EMAIL', 'superadmin@dms.com');
define('SUPER_ADMIN_PASS', 'Admin@123');

// Colors for console output
class Console {
    public static function success($msg) { echo "\033[32m✓ $msg\033[0m\n"; }
    public static function error($msg) { echo "\033[31m✗ $msg\033[0m\n"; }
    public static function info($msg) { echo "\033[36mℹ $msg\033[0m\n"; }
    public static function warning($msg) { echo "\033[33m⚠ $msg\033[0m\n"; }
    public static function header($msg) { echo "\n\033[1;35m=== $msg ===\033[0m\n"; }
    public static function subheader($msg) { echo "\033[1;34m--- $msg ---\033[0m\n"; }
}

// Database connection
class DB {
    private static $conn = null;
    
    public static function connect() {
        if (self::$conn === null) {
            $connStr = sprintf(
                "host=%s port=%s dbname=%s user=%s password=%s",
                DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASS
            );
            self::$conn = @pg_connect($connStr);
            if (!self::$conn) {
                die("Database connection failed! Check your database settings.\n");
            }
        }
        return self::$conn;
    }
    
    public static function query($sql, $params = []) {
        $conn = self::connect();
        if (empty($params)) {
            $result = @pg_query($conn, $sql);
        } else {
            $result = @pg_query_params($conn, $sql, $params);
        }
        if (!$result) {
            // Silently return false - let calling code handle it
            return false;
        }
        return $result;
    }
    
    public static function fetchAll($sql, $params = []) {
        $result = self::query($sql, $params);
        if (!$result) return [];
        $rows = pg_fetch_all($result);
        return $rows ?: [];
    }
    
    public static function fetchOne($sql, $params = []) {
        $result = self::query($sql, $params);
        if (!$result) return null;
        return pg_fetch_assoc($result);
    }
    
    public static function execute($sql, $params = []) {
        return self::query($sql, $params) !== false;
    }
    
    public static function lastInsertId($table, $column = 'id') {
        $result = self::fetchOne("SELECT currval(pg_get_serial_sequence('$table', '$column')) as id");
        return $result ? $result['id'] : null;
    }
}

// HTTP Client for API testing
class HttpClient {
    private $cookies = [];
    private $lastResponse = null;
    
    public function login($email, $password) {
        $response = $this->post('index.php/login/secureLogin', [
            'user_email' => $email,
            'user_password' => $password
        ]);
        return strpos($response['body'], 'dashboard') !== false || 
               $response['http_code'] == 302;
    }
    
    public function get($url, $params = []) {
        $fullUrl = BASE_URL . $url;
        if (!empty($params)) {
            $fullUrl .= '?' . http_build_query($params);
        }
        return $this->request('GET', $fullUrl);
    }
    
    public function post($url, $data = []) {
        return $this->request('POST', BASE_URL . $url, $data);
    }
    
    private 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_COOKIEFILE, '');
        curl_setopt($ch, CURLOPT_COOKIEJAR, '');
        
        if (!empty($this->cookies)) {
            curl_setopt($ch, CURLOPT_COOKIE, implode('; ', $this->cookies));
        }
        
        if ($method === 'POST') {
            curl_setopt($ch, CURLOPT_POST, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
        }
        
        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $headerSize = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        
        $headers = substr($response, 0, $headerSize);
        $body = substr($response, $headerSize);
        
        // Extract cookies
        preg_match_all('/Set-Cookie:\s*([^;]+)/i', $headers, $matches);
        foreach ($matches[1] as $cookie) {
            $this->cookies[] = $cookie;
        }
        
        curl_close($ch);
        
        $this->lastResponse = [
            'http_code' => $httpCode,
            'headers' => $headers,
            'body' => $body
        ];
        
        return $this->lastResponse;
    }
    
    public function getLastResponse() {
        return $this->lastResponse;
    }
}

// Test Result Tracker
class TestResults {
    private static $passed = 0;
    private static $failed = 0;
    private static $skipped = 0;
    private static $errors = [];
    
    public static function pass($test) {
        self::$passed++;
        Console::success($test);
    }
    
    public static function fail($test, $reason = '') {
        self::$failed++;
        self::$errors[] = ['test' => $test, 'reason' => $reason];
        Console::error($test . ($reason ? ": $reason" : ''));
    }
    
    public static function skip($test, $reason = '') {
        self::$skipped++;
        Console::warning("SKIP: $test" . ($reason ? " - $reason" : ''));
    }
    
    public static function summary() {
        Console::header("TEST SUMMARY");
        echo "\n";
        Console::success("Passed: " . self::$passed);
        if (self::$failed > 0) {
            Console::error("Failed: " . self::$failed);
        } else {
            echo "Failed: 0\n";
        }
        if (self::$skipped > 0) {
            Console::warning("Skipped: " . self::$skipped);
        }
        echo "\nTotal: " . (self::$passed + self::$failed + self::$skipped) . "\n";
        
        if (!empty(self::$errors)) {
            Console::header("FAILED TESTS");
            foreach (self::$errors as $error) {
                Console::error($error['test']);
                if ($error['reason']) {
                    echo "   Reason: {$error['reason']}\n";
                }
            }
        }
        
        return self::$failed === 0;
    }
}

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

class TestDataGenerator {
    private static $companyCounter = 0;
    private static $authorityCounter = 0;
    private static $docHeadCounter = 0;
    private static $documentCounter = 0;
    private static $userCounter = 0;
    
    public static function company() {
        self::$companyCounter++;
        return [
            'company_name' => 'Test Company ' . self::$companyCounter,
            'short_name' => 'TC' . self::$companyCounter,
            'address' => '123 Test Street, City ' . self::$companyCounter,
            'country' => 'Tanzania',
            'fiscal_year' => 'jan-dec',
            'status' => '1'
        ];
    }
    
    public static function authority() {
        self::$authorityCounter++;
        return [
            'authority_name' => 'Test Authority ' . self::$authorityCounter,
            'alias_name' => 'TA' . self::$authorityCounter,
            'status' => '1'
        ];
    }
    
    public static function documentHead($authorityId) {
        self::$docHeadCounter++;
        $frequencies = ['monthly', 'quarterly', 'half-yearly', 'yearly'];
        return [
            'type_name' => 'Test Document Head ' . self::$docHeadCounter,
            'alias_name' => 'TDH' . self::$docHeadCounter,
            'authority_id' => (string)$authorityId,
            'frequency' => $frequencies[self::$docHeadCounter % 4],
            'status' => '1'
        ];
    }
    
    public static function document() {
        self::$documentCounter++;
        return [
            'document_name' => 'Test Document ' . self::$documentCounter,
            'status' => '1'
        ];
    }
    
    public static function user($roleId, $companyIds = [], $authorityIds = []) {
        self::$userCounter++;
        $roles = ['1' => 'superadmin', '2' => 'admin', '3' => 'user'];
        $roleKey = (string)$roleId;
        return [
            'first_name' => 'Test',
            'last_name' => ucfirst($roles[$roleKey] ?? 'user') . self::$userCounter,
            'email_id' => "test" . ($roles[$roleKey] ?? 'user') . self::$userCounter . "@test.com",
            'password' => md5('Test@123'),
            'role_id' => (string)$roleId,  // Keep as string for varchar column
            'mobile_number' => '123456789' . self::$userCounter,
            'status' => '1',
            'company_ids' => $companyIds,
            'authority_ids' => $authorityIds
        ];
    }
    
    public static function holiday($year = null) {
        $year = $year ?? date('Y');
        return [
            'holiday_name' => 'Test Holiday ' . rand(1, 100),
            'holiday_date' => $year . '-' . sprintf('%02d', rand(1, 12)) . '-' . sprintf('%02d', rand(1, 28)),
            'country' => 'Tanzania',
            'holiday_year' => $year
        ];
    }
}

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

class MasterDataTests {
    
    public static function run() {
        Console::header("MASTER DATA TESTS");
        
        self::testCompanyCreation();
        self::testAuthorityCreation();
        self::testDocumentHeadCreation();
        self::testDocumentCreation();
        self::testCompanyAuthorityLinking();
        self::testCompanyDocumentHeadLinking();
    }
    
    public static function testCompanyCreation() {
        Console::subheader("Company Creation Tests");
        
        // Create 3 test companies
        for ($i = 1; $i <= 3; $i++) {
            $data = TestDataGenerator::company();
            $sql = "INSERT INTO company (company_name, short_name, address, country, fiscal_year, status) 
                    VALUES ($1, $2, $3, $4, $5, $6) RETURNING id";
            $result = DB::fetchOne($sql, [
                $data['company_name'],
                $data['short_name'],
                $data['address'],
                $data['country'],
                $data['fiscal_year'],
                $data['status']
            ]);
            
            if ($result && $result['id']) {
                TestResults::pass("Created company: {$data['company_name']} (ID: {$result['id']})");
            } else {
                TestResults::fail("Failed to create company: {$data['company_name']}");
            }
        }
        
        // Verify companies exist
        $count = DB::fetchOne("SELECT COUNT(*) as cnt FROM company WHERE status = '1'");
        if ($count && $count['cnt'] >= 3) {
            TestResults::pass("Verified {$count['cnt']} active companies exist");
        } else {
            TestResults::fail("Expected at least 3 companies, found: " . ($count['cnt'] ?? 0));
        }
    }
    
    public static function testAuthorityCreation() {
        Console::subheader("Authority Creation Tests");
        
        // Create 5 test authorities
        for ($i = 1; $i <= 5; $i++) {
            $data = TestDataGenerator::authority();
            $sql = "INSERT INTO authority (authority_name, alias_name, status) 
                    VALUES ($1, $2, $3) RETURNING id";
            $result = DB::fetchOne($sql, [
                $data['authority_name'],
                $data['alias_name'],
                $data['status']
            ]);
            
            if ($result && $result['id']) {
                TestResults::pass("Created authority: {$data['authority_name']} (ID: {$result['id']})");
            } else {
                TestResults::fail("Failed to create authority: {$data['authority_name']}");
            }
        }
        
        // Verify authorities exist
        $count = DB::fetchOne("SELECT COUNT(*) as cnt FROM authority WHERE status = '1'");
        if ($count && $count['cnt'] >= 5) {
            TestResults::pass("Verified {$count['cnt']} active authorities exist");
        } else {
            TestResults::fail("Expected at least 5 authorities, found: " . ($count['cnt'] ?? 0));
        }
    }
    
    public static function testDocumentHeadCreation() {
        Console::subheader("Document Head Creation Tests");
        
        // Get authorities
        $authorities = DB::fetchAll("SELECT id FROM authority WHERE status = '1' ORDER BY id LIMIT 5");
        if (empty($authorities)) {
            TestResults::skip("Document head creation - no authorities found");
            return;
        }
        
        // Create document heads for each authority
        foreach ($authorities as $auth) {
            $data = TestDataGenerator::documentHead($auth['id']);
            $sql = "INSERT INTO sub_type (type_name, alias_name, authority_id, frequency, status) 
                    VALUES ($1, $2, $3, $4, $5) RETURNING id";
            $result = DB::fetchOne($sql, [
                $data['type_name'],
                $data['alias_name'],
                $data['authority_id'],
                $data['frequency'],
                $data['status']
            ]);
            
            if ($result && $result['id']) {
                TestResults::pass("Created document head: {$data['type_name']} (ID: {$result['id']}, Freq: {$data['frequency']})");
            } else {
                TestResults::fail("Failed to create document head: {$data['type_name']}");
            }
        }
    }
    
    public static function testDocumentCreation() {
        Console::subheader("Document Creation Tests");
        
        // Create 10 test documents
        for ($i = 1; $i <= 10; $i++) {
            $data = TestDataGenerator::document();
            $sql = "INSERT INTO documents (document_name, status) 
                    VALUES ($1, $2) RETURNING id";
            $result = DB::fetchOne($sql, [
                $data['document_name'],
                $data['status']
            ]);
            
            if ($result && $result['id']) {
                TestResults::pass("Created document: {$data['document_name']} (ID: {$result['id']})");
            } else {
                TestResults::fail("Failed to create document: {$data['document_name']}");
            }
        }
    }
    
    public static function testCompanyAuthorityLinking() {
        Console::subheader("Company-Authority Linking Tests");
        
        $companies = DB::fetchAll("SELECT id FROM company WHERE status = '1'");
        $authorities = DB::fetchAll("SELECT id FROM authority WHERE status = '1'");
        
        if (empty($companies) || empty($authorities)) {
            TestResults::skip("Company-Authority linking - missing data");
            return;
        }
        
        // Link all authorities to all companies
        foreach ($companies as $company) {
            foreach ($authorities as $auth) {
                $sql = "INSERT INTO company_authorities (company_id, authority_id, is_enabled) 
                        VALUES ($1, $2, TRUE) 
                        ON CONFLICT (company_id, authority_id) DO NOTHING";
                DB::execute($sql, [$company['id'], $auth['id']]);
            }
        }
        
        $count = DB::fetchOne("SELECT COUNT(*) as cnt FROM company_authorities WHERE is_enabled = TRUE");
        TestResults::pass("Created {$count['cnt']} company-authority links");
    }
    
    public static function testCompanyDocumentHeadLinking() {
        Console::subheader("Company-Document Head Linking Tests");
        
        $companies = DB::fetchAll("SELECT id FROM company WHERE status = '1'");
        $docHeads = DB::fetchAll("SELECT id FROM sub_type WHERE status = '1'");
        
        if (empty($companies) || empty($docHeads)) {
            TestResults::skip("Company-Document Head linking - missing data");
            return;
        }
        
        // Link all document heads to all companies
        foreach ($companies as $company) {
            foreach ($docHeads as $dh) {
                $sql = "INSERT INTO company_document_heads (company_id, type_id, is_enabled) 
                        VALUES ($1, $2, TRUE) 
                        ON CONFLICT (company_id, type_id) DO NOTHING";
                DB::execute($sql, [$company['id'], $dh['id']]);
            }
        }
        
        $count = DB::fetchOne("SELECT COUNT(*) as cnt FROM company_document_heads WHERE is_enabled = TRUE");
        TestResults::pass("Created {$count['cnt']} company-document head links");
    }
}

class UserManagementTests {
    
    public static function run() {
        Console::header("USER MANAGEMENT TESTS");
        
        self::testSuperAdminExists();
        self::testAdminCreation();
        self::testUserCreation();
        self::testUserPermissions();
        self::testUserCompanyAssignment();
        self::testUserAuthorityAssignment();
    }
    
    public static function testSuperAdminExists() {
        Console::subheader("Super Admin Verification");
        
        // role_id is varchar, so use string comparison
        $superAdmin = DB::fetchOne("SELECT * FROM users WHERE role_id = '1' AND status = '1'");
        if ($superAdmin) {
            TestResults::pass("Super Admin exists: {$superAdmin['email_id']}");
            
            // Check permissions
            $perms = DB::fetchOne("SELECT * FROM user_permissions WHERE user_id = $1", [$superAdmin['id']]);
            if ($perms && ($perms['can_view'] === 't' || $perms['can_view'] === true) && 
                ($perms['can_add_edit'] === 't' || $perms['can_add_edit'] === true) && 
                ($perms['can_delete'] === 't' || $perms['can_delete'] === true)) {
                TestResults::pass("Super Admin has full permissions");
            } else {
                TestResults::fail("Super Admin missing permissions");
            }
        } else {
            TestResults::fail("Super Admin not found");
        }
    }
    
    public static function testAdminCreation() {
        Console::subheader("Admin User Creation Tests");
        
        $companies = DB::fetchAll("SELECT id FROM company WHERE status = '1' ORDER BY id");
        if (empty($companies)) {
            TestResults::skip("Admin creation - no companies found");
            return;
        }
        
        // Create Admin 1 - Access to Company 1 only (View + Add/Edit)
        $admin1 = TestDataGenerator::user('2', [$companies[0]['id']]);
        $sql = "INSERT INTO users (first_name, last_name, email_id, password, role_id, mobile_number, status) 
                VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id";
        $result = DB::fetchOne($sql, [
            $admin1['first_name'],
            $admin1['last_name'],
            $admin1['email_id'],
            $admin1['password'],
            $admin1['role_id'],
            $admin1['mobile_number'],
            $admin1['status']
        ]);
        
        if ($result && $result['id']) {
            $adminId = $result['id'];
            TestResults::pass("Created Admin 1: {$admin1['email_id']} (ID: $adminId)");
            
            // Set permissions (View + Add/Edit, NO Delete)
            DB::execute("INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete) VALUES ($1, TRUE, TRUE, FALSE)", [$adminId]);
            TestResults::pass("Admin 1 permissions set: View=YES, Add/Edit=YES, Delete=NO");
            
            // Assign company
            DB::execute("INSERT INTO user_companies (user_id, company_id) VALUES ($1, $2)", [$adminId, $companies[0]['id']]);
            TestResults::pass("Admin 1 assigned to Company ID: {$companies[0]['id']}");
        } else {
            TestResults::fail("Failed to create Admin 1");
        }
        
        // Create Admin 2 - Access to Company 2 and 3 (View + Add/Edit + Delete)
        if (count($companies) >= 3) {
            $admin2 = TestDataGenerator::user('2', [$companies[1]['id'], $companies[2]['id']]);
            $result = DB::fetchOne($sql, [
                $admin2['first_name'],
                $admin2['last_name'],
                $admin2['email_id'],
                $admin2['password'],
                $admin2['role_id'],
                $admin2['mobile_number'],
                $admin2['status']
            ]);
            
            if ($result && $result['id']) {
                $adminId = $result['id'];
                TestResults::pass("Created Admin 2: {$admin2['email_id']} (ID: $adminId)");
                
                // Set permissions (Full access)
                DB::execute("INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete) VALUES ($1, TRUE, TRUE, TRUE)", [$adminId]);
                TestResults::pass("Admin 2 permissions set: View=YES, Add/Edit=YES, Delete=YES");
                
                // Assign companies
                DB::execute("INSERT INTO user_companies (user_id, company_id) VALUES ($1, $2)", [$adminId, $companies[1]['id']]);
                DB::execute("INSERT INTO user_companies (user_id, company_id) VALUES ($1, $2)", [$adminId, $companies[2]['id']]);
                TestResults::pass("Admin 2 assigned to Companies: {$companies[1]['id']}, {$companies[2]['id']}");
            } else {
                TestResults::fail("Failed to create Admin 2");
            }
        }
    }
    
    public static function testUserCreation() {
        Console::subheader("Regular User Creation Tests");
        
        $companies = DB::fetchAll("SELECT id FROM company WHERE status = '1' ORDER BY id");
        $authorities = DB::fetchAll("SELECT id FROM authority WHERE status = '1' ORDER BY id");
        
        if (empty($companies) || empty($authorities)) {
            TestResults::skip("User creation - missing companies or authorities");
            return;
        }
        
        // Create User 1 - View Only access to Company 1, Authority 1
        $user1 = TestDataGenerator::user('3', [$companies[0]['id']], [$authorities[0]['id']]);
        $sql = "INSERT INTO users (first_name, last_name, email_id, password, role_id, mobile_number, status) 
                VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id";
        $result = DB::fetchOne($sql, [
            $user1['first_name'],
            $user1['last_name'],
            $user1['email_id'],
            $user1['password'],
            $user1['role_id'],
            $user1['mobile_number'],
            $user1['status']
        ]);
        
        if ($result && $result['id']) {
            $userId = $result['id'];
            TestResults::pass("Created User 1 (View Only): {$user1['email_id']} (ID: $userId)");
            
            // Set permissions (View Only)
            DB::execute("INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete) VALUES ($1, TRUE, FALSE, FALSE)", [$userId]);
            TestResults::pass("User 1 permissions set: View=YES, Add/Edit=NO, Delete=NO");
            
            // Assign company and authority
            DB::execute("INSERT INTO user_companies (user_id, company_id) VALUES ($1, $2)", [$userId, $companies[0]['id']]);
            DB::execute("INSERT INTO user_authorities (user_id, authority_id) VALUES ($1, $2)", [$userId, $authorities[0]['id']]);
            TestResults::pass("User 1 assigned to Company: {$companies[0]['id']}, Authority: {$authorities[0]['id']}");
        } else {
            TestResults::fail("Failed to create User 1");
        }
        
        // Create User 2 - View + Add/Edit access to Company 1, Authorities 1 and 2
        if (count($authorities) >= 2) {
            $user2 = TestDataGenerator::user('3', [$companies[0]['id']], [$authorities[0]['id'], $authorities[1]['id']]);
            $result = DB::fetchOne($sql, [
                $user2['first_name'],
                $user2['last_name'],
                $user2['email_id'],
                $user2['password'],
                $user2['role_id'],
                $user2['mobile_number'],
                $user2['status']
            ]);
            
            if ($result && $result['id']) {
                $userId = $result['id'];
                TestResults::pass("Created User 2 (View + Add/Edit): {$user2['email_id']} (ID: $userId)");
                
                // Set permissions
                DB::execute("INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete) VALUES ($1, TRUE, TRUE, FALSE)", [$userId]);
                TestResults::pass("User 2 permissions set: View=YES, Add/Edit=YES, Delete=NO");
                
                // Assign company and authorities
                DB::execute("INSERT INTO user_companies (user_id, company_id) VALUES ($1, $2)", [$userId, $companies[0]['id']]);
                DB::execute("INSERT INTO user_authorities (user_id, authority_id) VALUES ($1, $2)", [$userId, $authorities[0]['id']]);
                DB::execute("INSERT INTO user_authorities (user_id, authority_id) VALUES ($1, $2)", [$userId, $authorities[1]['id']]);
                TestResults::pass("User 2 assigned to Company: {$companies[0]['id']}, Authorities: {$authorities[0]['id']}, {$authorities[1]['id']}");
            } else {
                TestResults::fail("Failed to create User 2");
            }
        }
    }
    
    public static function testUserPermissions() {
        Console::subheader("User Permission Verification Tests");
        
        // Verify all users have permissions set
        $usersWithoutPerms = DB::fetchAll("
            SELECT u.id, u.email_id, u.role_id 
            FROM users u 
            LEFT JOIN user_permissions up ON u.id = up.user_id 
            WHERE up.id IS NULL AND u.status = '1'
        ");
        
        if (empty($usersWithoutPerms)) {
            TestResults::pass("All active users have permissions set");
        } else {
            foreach ($usersWithoutPerms as $user) {
                TestResults::fail("User {$user['email_id']} (ID: {$user['id']}) missing permissions");
            }
        }
        
        // Verify Admin users don't have delete permission (except those explicitly granted)
        $adminsWithDelete = DB::fetchAll("
            SELECT u.id, u.email_id, up.can_delete 
            FROM users u 
            JOIN user_permissions up ON u.id = up.user_id 
            WHERE u.role_id = '2' AND u.status = '1'
        ");
        
        foreach ($adminsWithDelete as $admin) {
            $hasDelete = $admin['can_delete'] === 't' || $admin['can_delete'] === true;
            Console::info("Admin {$admin['email_id']}: Delete permission = " . ($hasDelete ? 'YES' : 'NO'));
        }
        
        // Verify regular users don't have delete permission
        $usersWithDelete = DB::fetchAll("
            SELECT u.id, u.email_id 
            FROM users u 
            JOIN user_permissions up ON u.id = up.user_id 
            WHERE u.role_id = '3' AND up.can_delete = TRUE AND u.status = '1'
        ");
        
        if (empty($usersWithDelete)) {
            TestResults::pass("No regular users have delete permission");
        } else {
            foreach ($usersWithDelete as $user) {
                TestResults::fail("Regular user {$user['email_id']} should not have delete permission");
            }
        }
    }
    
    public static function testUserCompanyAssignment() {
        Console::subheader("User Company Assignment Tests");
        
        // Check Admin users have company assignments
        $adminsWithoutCompanies = DB::fetchAll("
            SELECT u.id, u.email_id 
            FROM users u 
            LEFT JOIN user_companies uc ON u.id = uc.user_id 
            WHERE u.role_id = '2' AND u.status = '1' AND uc.id IS NULL
        ");
        
        if (empty($adminsWithoutCompanies)) {
            TestResults::pass("All Admin users have company assignments");
        } else {
            foreach ($adminsWithoutCompanies as $admin) {
                TestResults::fail("Admin {$admin['email_id']} has no company assignment");
            }
        }
        
        // Check regular users have company assignments
        $usersWithoutCompanies = DB::fetchAll("
            SELECT u.id, u.email_id 
            FROM users u 
            LEFT JOIN user_companies uc ON u.id = uc.user_id 
            WHERE u.role_id = '3' AND u.status = '1' AND uc.id IS NULL
        ");
        
        if (empty($usersWithoutCompanies)) {
            TestResults::pass("All regular users have company assignments");
        } else {
            foreach ($usersWithoutCompanies as $user) {
                TestResults::fail("User {$user['email_id']} has no company assignment");
            }
        }
    }
    
    public static function testUserAuthorityAssignment() {
        Console::subheader("User Authority Assignment Tests");
        
        // Check regular users have authority assignments
        $usersWithoutAuthorities = DB::fetchAll("
            SELECT u.id, u.email_id 
            FROM users u 
            LEFT JOIN user_authorities ua ON u.id = ua.user_id 
            WHERE u.role_id = '3' AND u.status = '1' AND ua.id IS NULL
        ");
        
        if (empty($usersWithoutAuthorities)) {
            TestResults::pass("All regular users have authority assignments");
        } else {
            foreach ($usersWithoutAuthorities as $user) {
                TestResults::fail("User {$user['email_id']} has no authority assignment");
            }
        }
    }
}

class HolidayTests {
    
    public static function run() {
        Console::header("HOLIDAY MANAGEMENT TESTS");
        
        self::testHolidayCreation();
        self::testHolidayDuplicateCheck();
    }
    
    public static function testHolidayCreation() {
        Console::subheader("Holiday Creation Tests");
        
        $year = date('Y');
        
        // Create holidays
        $holidays = [
            ['name' => 'New Year', 'date' => "$year-01-01"],
            ['name' => 'Independence Day', 'date' => "$year-12-09"],
            ['name' => 'Christmas', 'date' => "$year-12-25"],
            ['name' => 'Union Day', 'date' => "$year-04-26"],
            ['name' => 'Workers Day', 'date' => "$year-05-01"],
        ];
        
        foreach ($holidays as $h) {
            // Check if already exists
            $exists = DB::fetchOne("SELECT id FROM holidays WHERE holiday_date = $1 AND country = 'Tanzania'", [$h['date']]);
            if ($exists) {
                Console::info("Holiday already exists: {$h['name']} ({$h['date']})");
                continue;
            }
            
            $sql = "INSERT INTO holidays (holiday_name, holiday_date, country, holiday_year) 
                    VALUES ($1, $2, 'Tanzania', $3) RETURNING id";
            $result = DB::fetchOne($sql, [$h['name'], $h['date'], $year]);
            
            if ($result && $result['id']) {
                TestResults::pass("Created holiday: {$h['name']} ({$h['date']})");
            } else {
                TestResults::fail("Failed to create holiday: {$h['name']}");
            }
        }
    }
    
    public static function testHolidayDuplicateCheck() {
        Console::subheader("Holiday Duplicate Check Tests");
        
        $duplicates = DB::fetchAll("
            SELECT holiday_date, COUNT(*) as cnt 
            FROM holidays 
            WHERE is_deleted = 0 OR is_deleted IS NULL
            GROUP BY holiday_date 
            HAVING COUNT(*) > 1
        ");
        
        if (empty($duplicates)) {
            TestResults::pass("No duplicate holidays found");
        } else {
            foreach ($duplicates as $dup) {
                TestResults::fail("Duplicate holiday on date: {$dup['holiday_date']} ({$dup['cnt']} entries)");
            }
        }
    }
}

class RBACTests {
    
    public static function run() {
        Console::header("ROLE-BASED ACCESS CONTROL TESTS");
        
        self::testSuperAdminAccess();
        self::testAdminCompanyRestriction();
        self::testUserAuthorityRestriction();
        self::testViewOnlyPermission();
    }
    
    public static function testSuperAdminAccess() {
        Console::subheader("Super Admin Access Tests");
        
        $superAdmin = DB::fetchOne("SELECT id FROM users WHERE role_id = '1' AND status = '1' LIMIT 1");
        if (!$superAdmin) {
            TestResults::skip("Super Admin access test - no super admin found");
            return;
        }
        
        // Super Admin should see all companies
        $allCompanies = DB::fetchOne("SELECT COUNT(*) as cnt FROM company WHERE status = '1'");
        TestResults::pass("Super Admin can access all {$allCompanies['cnt']} companies");
        
        // Super Admin should have full permissions
        $perms = DB::fetchOne("SELECT * FROM user_permissions WHERE user_id = $1", [$superAdmin['id']]);
        if ($perms) {
            $canView = $perms['can_view'] === 't' || $perms['can_view'] === true;
            $canEdit = $perms['can_add_edit'] === 't' || $perms['can_add_edit'] === true;
            $canDelete = $perms['can_delete'] === 't' || $perms['can_delete'] === true;
            
            if ($canView && $canEdit && $canDelete) {
                TestResults::pass("Super Admin has full permissions (View, Add/Edit, Delete)");
            } else {
                TestResults::fail("Super Admin missing some permissions");
            }
        }
    }
    
    public static function testAdminCompanyRestriction() {
        Console::subheader("Admin Company Restriction Tests");
        
        $admins = DB::fetchAll("SELECT id, email_id FROM users WHERE role_id = '2' AND status = '1'");
        
        if (empty($admins)) {
            TestResults::skip("Admin company restriction - no admins found");
            return;
        }
        
        foreach ($admins as $admin) {
            $assignedCompanies = DB::fetchAll("
                SELECT c.id, c.company_name 
                FROM company c 
                JOIN user_companies uc ON c.id = uc.company_id 
                WHERE uc.user_id = $1
            ", [$admin['id']]);
            
            $count = count($assignedCompanies);
            if ($count > 0) {
                $companyNames = array_column($assignedCompanies, 'company_name');
                TestResults::pass("Admin {$admin['email_id']} restricted to $count companies: " . implode(', ', $companyNames));
            } else {
                TestResults::fail("Admin {$admin['email_id']} has no company assignments");
            }
        }
    }
    
    public static function testUserAuthorityRestriction() {
        Console::subheader("User Authority Restriction Tests");
        
        $users = DB::fetchAll("SELECT id, email_id FROM users WHERE role_id = '3' AND status = '1'");
        
        if (empty($users)) {
            TestResults::skip("User authority restriction - no users found");
            return;
        }
        
        foreach ($users as $user) {
            $assignedAuthorities = DB::fetchAll("
                SELECT a.id, a.authority_name 
                FROM authority a 
                JOIN user_authorities ua ON a.id = ua.authority_id 
                WHERE ua.user_id = $1
            ", [$user['id']]);
            
            $count = count($assignedAuthorities);
            if ($count > 0) {
                $authNames = array_column($assignedAuthorities, 'authority_name');
                TestResults::pass("User {$user['email_id']} restricted to $count authorities: " . implode(', ', $authNames));
            } else {
                TestResults::fail("User {$user['email_id']} has no authority assignments");
            }
        }
    }
    
    public static function testViewOnlyPermission() {
        Console::subheader("View Only Permission Tests");
        
        // Find users with view-only permission
        $viewOnlyUsers = DB::fetchAll("
            SELECT u.id, u.email_id, u.role_id 
            FROM users u 
            JOIN user_permissions up ON u.id = up.user_id 
            WHERE up.can_view = TRUE AND up.can_add_edit = FALSE AND up.can_delete = FALSE
            AND u.status = '1'
        ");
        
        if (!empty($viewOnlyUsers)) {
            foreach ($viewOnlyUsers as $user) {
                TestResults::pass("User {$user['email_id']} correctly set as View Only");
            }
        } else {
            Console::info("No view-only users found (this may be expected)");
        }
    }
}

class DataIntegrityTests {
    
    public static function run() {
        Console::header("DATA INTEGRITY TESTS");
        
        self::testForeignKeyIntegrity();
        self::testRequiredFields();
        self::testStatusValues();
        self::testDuplicateChecks();
    }
    
    public static function testForeignKeyIntegrity() {
        Console::subheader("Foreign Key Integrity Tests");
        
        // Check user_companies references valid users
        $orphanedUserCompanies = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM user_companies uc 
            LEFT JOIN users u ON uc.user_id = u.id 
            WHERE u.id IS NULL
        ");
        if ($orphanedUserCompanies && $orphanedUserCompanies['cnt'] == 0) {
            TestResults::pass("All user_companies reference valid users");
        } else {
            TestResults::fail("Found " . ($orphanedUserCompanies['cnt'] ?? 'unknown') . " orphaned user_companies records");
        }
        
        // Check user_companies references valid companies
        $orphanedCompanyRefs = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM user_companies uc 
            LEFT JOIN company c ON uc.company_id = c.id 
            WHERE c.id IS NULL
        ");
        if ($orphanedCompanyRefs && $orphanedCompanyRefs['cnt'] == 0) {
            TestResults::pass("All user_companies reference valid companies");
        } else {
            TestResults::fail("Found " . ($orphanedCompanyRefs['cnt'] ?? 'unknown') . " orphaned company references");
        }
        
        // Check sub_type references valid authorities
        $orphanedDocHeads = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM sub_type st 
            LEFT JOIN authority a ON st.authority_id::int = a.id 
            WHERE a.id IS NULL AND st.authority_id IS NOT NULL AND st.authority_id != ''
        ");
        if ($orphanedDocHeads && $orphanedDocHeads['cnt'] == 0) {
            TestResults::pass("All document heads reference valid authorities");
        } else {
            TestResults::fail("Found " . ($orphanedDocHeads['cnt'] ?? 'unknown') . " document heads with invalid authority references");
        }
    }
    
    public static function testRequiredFields() {
        Console::subheader("Required Fields Tests");
        
        // Check users have required fields
        $invalidUsers = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM users 
            WHERE email_id IS NULL OR email_id = '' OR role_id IS NULL
        ");
        if ($invalidUsers && $invalidUsers['cnt'] == 0) {
            TestResults::pass("All users have required fields (email_id, role_id)");
        } else {
            TestResults::fail("Found " . ($invalidUsers['cnt'] ?? 'unknown') . " users with missing required fields");
        }
        
        // Check companies have required fields
        $invalidCompanies = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM company 
            WHERE company_name IS NULL OR company_name = ''
        ");
        if ($invalidCompanies && $invalidCompanies['cnt'] == 0) {
            TestResults::pass("All companies have required fields (company_name)");
        } else {
            TestResults::fail("Found " . ($invalidCompanies['cnt'] ?? 'unknown') . " companies with missing required fields");
        }
    }
    
    public static function testStatusValues() {
        Console::subheader("Status Value Tests");
        
        // Check users have valid status
        $invalidStatus = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM users 
            WHERE status NOT IN ('0', '1') AND status IS NOT NULL
        ");
        if ($invalidStatus && $invalidStatus['cnt'] == 0) {
            TestResults::pass("All users have valid status values");
        } else {
            TestResults::fail("Found " . ($invalidStatus['cnt'] ?? 'unknown') . " users with invalid status");
        }
    }
    
    public static function testDuplicateChecks() {
        Console::subheader("Duplicate Check Tests");
        
        // Check for duplicate emails
        $dupEmails = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM (
                SELECT email_id FROM users WHERE status = '1' GROUP BY email_id HAVING COUNT(*) > 1
            ) as dups
        ");
        if ($dupEmails && $dupEmails['cnt'] == 0) {
            TestResults::pass("No duplicate email addresses found");
        } else {
            TestResults::fail("Found " . ($dupEmails['cnt'] ?? 'unknown') . " duplicate email addresses");
        }
        
        // Check for duplicate authority aliases
        $dupAuthAliases = DB::fetchOne("
            SELECT COUNT(*) as cnt FROM (
                SELECT alias_name FROM authority WHERE status = '1' AND alias_name IS NOT NULL AND alias_name != ''
                GROUP BY alias_name HAVING COUNT(*) > 1
            ) as dups
        ");
        if ($dupAuthAliases && $dupAuthAliases['cnt'] == 0) {
            TestResults::pass("No duplicate authority aliases found");
        } else {
            TestResults::fail("Found " . ($dupAuthAliases['cnt'] ?? 'unknown') . " duplicate authority aliases");
        }
    }
}

class APITests {
    private static $client;
    
    public static function run() {
        Console::header("API/HTTP TESTS");
        
        self::$client = new HttpClient();
        
        self::testLoginPage();
        self::testSuperAdminLogin();
        self::testDashboardAccess();
    }
    
    public static function testLoginPage() {
        Console::subheader("Login Page Tests");
        
        $response = self::$client->get('login');
        if ($response['http_code'] == 200) {
            TestResults::pass("Login page accessible (HTTP 200)");
        } else {
            TestResults::fail("Login page not accessible (HTTP {$response['http_code']})");
        }
    }
    
    public static function testSuperAdminLogin() {
        Console::subheader("Super Admin Login Tests");
        
        $superAdmin = DB::fetchOne("SELECT email_id FROM users WHERE role_id = '1' AND status = '1' LIMIT 1");
        if (!$superAdmin) {
            TestResults::skip("Super Admin login - no super admin found");
            return;
        }
        
        if (self::$client->login($superAdmin['email_id'], 'Admin@123')) {
            TestResults::pass("Super Admin login successful");
        } else {
            TestResults::fail("Super Admin login failed");
        }
    }
    
    public static function testDashboardAccess() {
        Console::subheader("Dashboard Access Tests");
        
        $response = self::$client->get('dashboard');
        if ($response['http_code'] == 200 || $response['http_code'] == 302) {
            TestResults::pass("Dashboard accessible after login");
        } else {
            TestResults::fail("Dashboard not accessible (HTTP {$response['http_code']})");
        }
    }
}

// =====================================================
// MAIN EXECUTION
// =====================================================

function printUsage() {
    echo "\nDMS Complete Test Suite\n";
    echo "========================\n\n";
    echo "Usage: php complete_test_suite.php [option]\n\n";
    echo "Options:\n";
    echo "  --seed        Seed test data and run all tests\n";
    echo "  --all         Same as --seed (seed + run all tests)\n";
    echo "  --master      Run master data tests only\n";
    echo "  --users       Run user management tests only\n";
    echo "  --rbac        Run RBAC tests only\n";
    echo "  --holidays    Run holiday tests only\n";
    echo "  --integrity   Run data integrity tests only\n";
    echo "  --api         Run API tests only\n";
    echo "  (no option)   Run all tests without seeding\n\n";
    echo "IMPORTANT: Run truncate using psql BEFORE running this script:\n";
    echo "  psql -U postgres -d new_dms_type -f complete_truncate.sql\n\n";
}

function runSeed() {
    Console::header("SEEDING TEST DATA");
    
    MasterDataTests::testCompanyCreation();
    MasterDataTests::testAuthorityCreation();
    MasterDataTests::testDocumentHeadCreation();
    MasterDataTests::testDocumentCreation();
    MasterDataTests::testCompanyAuthorityLinking();
    MasterDataTests::testCompanyDocumentHeadLinking();
    UserManagementTests::testAdminCreation();
    UserManagementTests::testUserCreation();
    HolidayTests::testHolidayCreation();
    
    Console::success("Test data seeded successfully");
}

function runAllTests() {
    MasterDataTests::run();
    UserManagementTests::run();
    HolidayTests::run();
    RBACTests::run();
    DataIntegrityTests::run();
    APITests::run();
}

// Parse command line arguments
$option = isset($argv[1]) ? strtolower($argv[1]) : '';

Console::header("DMS COMPLETE TEST SUITE");
echo "Database: " . DB_NAME . "\n";
echo "Base URL: " . BASE_URL . "\n";
echo "Date: " . date('Y-m-d H:i:s') . "\n";

// Connect to database
DB::connect();

switch ($option) {
    case '--seed':
    case '--all':
        runSeed();
        runAllTests();
        break;
        
    case '--master':
        MasterDataTests::run();
        break;
        
    case '--users':
        UserManagementTests::run();
        break;
        
    case '--rbac':
        RBACTests::run();
        break;
        
    case '--holidays':
        HolidayTests::run();
        break;
        
    case '--integrity':
        DataIntegrityTests::run();
        break;
        
    case '--api':
        APITests::run();
        break;
        
    case '--help':
    case '-h':
        printUsage();
        exit(0);
        
    default:
        if (!empty($option)) {
            Console::warning("Unknown option: $option");
            printUsage();
            exit(1);
        }
        runAllTests();
        break;
}

// Print summary
$success = TestResults::summary();
exit($success ? 0 : 1);
