<?php
/**
 * Schema Manager for dynamic table synchronization
 */

class SchemaManager {
    private Database $db;

    public function __construct() {
        $this->db = Database::getInstance();
    }

    public function getTableSchema(string $table): array {
        $columns = $this->db->fetchAll("SHOW COLUMNS FROM `$table`");
        
        $displayColumns = [];
        $exclude = ['id', 'Created_At', 'Updated_At', 'created_at', 'updated_at'];
        
        foreach ($columns as $col) {
            if (!in_array(strtolower($col['Field']), $exclude)) {
                $aliases = [
                    'Item_Code' => 'item_code',
                    'Item_Description' => 'description',
                    'Item_Group' => 'category',
                    'Stores' => 'location',
                    'Rack' => 'rack',
                    'Level' => 'level',
                    'Bin' => 'bin',
                    'Uom' => 'unit_of_measure',
                    'System_Qty' => 'system_quantity',
                    'Status' => 'status',
                ];
                
                $field = $col['Field'];
                $alias = $aliases[$field] ?? strtolower(str_replace('_', ' ', $field));
                
                $type = $this->parseType($col['Type']);
                $displayColumns[] = [
                    'field' => $field,
                    'alias' => $alias,
                    'type' => $type,
                    'nullable' => $col['Null'] === 'YES',
                    'default' => $col['Default'],
                ];
            }
        }
        return $displayColumns;
    }

    private function parseType(string $type): string {
        $type = strtoupper($type);
        if (strpos($type, 'VARCHAR') !== false || strpos($type, 'TEXT') !== false) return 'text';
        if (strpos($type, 'INT') !== false || strpos($type, 'DECIMAL') !== false) return 'number';
        if (strpos($type, 'DATETIME') !== false || strpos($type, 'DATE') !== false || strpos($type, 'TIMESTAMP') !== false) return 'date';
        if (strpos($type, 'ENUM') !== false) return 'select';
        return 'text';
    }

    public function getSchemaHash(string $table): string {
        $columns = $this->db->fetchAll("SHOW COLUMNS FROM `$table`");
        $hashParts = [];
        foreach ($columns as $col) {
            $hashParts[] = implode('|', [$col['Field'], $col['Type'], $col['Null'] ?? 'NO', $col['Default'] ?? 'NULL']);
        }
        return md5(implode(';', $hashParts));
    }
}

