#!/usr/bin/env python3
"""
SCH-016: File Table Full-Scan Elimination
Replaces full-table scans with filtered IN() queries.
Also replaces O(N) permit inner scan with O(1) hash lookups.
"""
import re
import sys

def read_file(path):
    with open(path, 'r') as f:
        return f.read()

def write_file(path, content):
    with open(path, 'w') as f:
        f.write(content)

FILE_CACHE_HELPER = '''
    // SCH-016: Filtered file queries (IN clause instead of full-table scan)
    function _build_file_cache($jobs) {
        $cache = array('pp' => array(), 'pm' => array(), 'permit' => array(), 'permit_exists' => array(), 'sk' => array());
        if (!$jobs || !count($jobs)) return $cache;

        $pids = array();
        foreach ($jobs as $j) {
            if (isset($j['job_pid']) && $j['job_pid']) $pids[] = (int)$j['job_pid'];
            $pids[] = (int)$j['id'];
        }
        $pids = array_unique($pids);
        if (empty($pids)) return $cache;
        $pid_sql = implode(',', $pids);

        $rows = $this->db->query("SELECT * FROM genral_files WHERE job_id IN ($pid_sql) ORDER BY created DESC")->result_array();
        foreach ($rows as $r) {
            if (!isset($cache['pp'][$r['job_id']])) $cache['pp'][$r['job_id']] = $r;
        }

        $rows = $this->db->query("SELECT * FROM presale_files WHERE job_id IN ($pid_sql) ORDER BY created DESC")->result_array();
        foreach ($rows as $r) {
            if (!isset($cache['pm'][$r['job_id']])) $cache['pm'][$r['job_id']] = $r;
        }

        $rows = $this->db->query("SELECT * FROM permit_files WHERE job_id IN ($pid_sql)")->result_array();
        foreach ($rows as $r) {
            $cache['permit'][$r['job_id']][trim($r['type'])] = $r;
            $cache['permit_exists'][$r['job_id']] = true;
        }

        $rows = $this->db->query("SELECT * FROM sketch_files WHERE job_id IN ($pid_sql) ORDER BY created DESC")->result_array();
        foreach ($rows as $r) {
            if (!isset($cache['sk'][$r['job_id']])) $cache['sk'][$r['job_id']] = $r;
        }

        return $cache;
    }

'''

# Pattern to match the file query initialization + genral_files query + foreach
# This covers: $ppfile=array() through tppfile foreach close brace
# But NOT the pp_users block that sits between genral_files and presale_files
INIT_AND_GENRAL_PATTERN = re.compile(
    r'''(\s*)\$ppfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$pmfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$tppfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$tpmfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$tpermitfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$skfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$tskfile\s*=\s*array\(\);\s*\n'''
    r'''\s*\$this->db->order_by\("created","desc"\);\s*\n'''
    r'''\s*\$ppfile\s*=\s*\$this->db->get\("genral_files"\)->result_array\(\);\s*\n'''
    r'''\s*foreach\(\$ppfile as \$ppfilekey => \$ppfilevalue\)\{\s*\n'''
    r'''\s*\$tppfile\[\$ppfilevalue\[\'job_id\'\]\]\s*=\s*\$ppfilevalue;\s*\n'''
    r'''\s*\}\s*\n''',
    re.MULTILINE
)

# Pattern for the presale+permit+sketch block (after pp_users)
PRESALE_PERMIT_SKETCH_PATTERN = re.compile(
    r'''(\s*)\$this->db->order_by\("created","desc"\);\s*\n'''
    r'''\s*\$pmfile\s*=\s*\$this->db->get\("presale_files"\)->result_array\(\);\s*\n'''
    r'''\s*foreach\(\$pmfile as \$pmfilekey => \$pmfilevalue\)\{\s*\n'''
    r'''\s*\$tpmfile\[\$pmfilevalue\[\'job_id\'\]\]\s*=\s*\$pmfilevalue;\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*\$this->db->order_by\("created","desc"\);\s*\n'''
    r'''\s*\$permitfile\s*=\s*\$this->db->get\("permit_files"\)->result_array\(\);\s*\n'''
    r'''\s*foreach\(\$permitfile as \$pmfilekey => \$pmfilevalue\)\{\s*\n'''
    r'''\s*\$tpermitfile\[\$pmfilevalue\[\'job_id\'\]\]\s*=\s*\$pmfilevalue;\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*\$this->db->order_by\("created","desc"\);\s*\n'''
    r'''\s*\$skfile\s*=\s*\$this->db->get\("sketch_files"\)->result_array\(\);\s*\n'''
    r'''\s*foreach\(\$skfile as \$pmfilekey => \$pmfilevalue\)\{\s*\n'''
    r'''\s*\$tskfile\[\$pmfilevalue\[\'job_id\'\]\]\s*=\s*\$pmfilevalue;\s*\n'''
    r'''\s*\}\s*\n''',
    re.MULTILINE
)

# Pattern for the permit inner foreach scan
PERMIT_SCAN_PATTERN = re.compile(
    r'''(\s*)if\(array_key_exists\(\$job\['job_pid'\],\$tpermitfile\)\s*\)\{\s*\n'''
    r'''\s*\n?'''
    r'''\s*foreach\(\$permitfile as \$pmfilekey => \$pmfilevalue\)\{\s*\n'''
    r'''\s*if\(\$pmfilevalue\['job_id'\]\s*==\s*\$job\['job_pid'\]\s*&&\s*trim\(\$pmfilevalue\['type'\]\)=="building"\s*\)\{\s*\n'''
    r'''\s*\$building\s*=\s*true;\s*\n'''
    r'''\s*\$bid\s*=\s*\$pmfilevalue\['id'\];\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*if\(\$pmfilevalue\['job_id'\]\s*==\s*\$job\['job_pid'\]\s*&&\s*trim\(\$pmfilevalue\['type'\]\)=="netmeter"\s*\)\{\s*\n'''
    r'''\s*\$netmeter\s*=\s*true;\s*\n'''
    r'''\s*\$nid\s*=\s*\$pmfilevalue\['id'\];\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*if\(\$pmfilevalue\['job_id'\]\s*==\s*\$job\['job_pid'\]\s*&&\s*trim\(\$pmfilevalue\['type'\]\)=="homeowner"\s*\)\{\s*\n'''
    r'''\s*\$homeowner\s*=\s*true;\s*\n'''
    r'''\s*\$hid\s*=\s*\$pmfilevalue\['id'\];\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*\}\s*\n'''
    r'''\s*\}\s*\n''',
    re.MULTILINE
)

PERMIT_REPLACEMENT = r"""\1if(isset($_fc['permit'][$job['job_pid']])){
\1    $_pf = $_fc['permit'][$job['job_pid']];
\1    if(isset($_pf['building'])) { $building = true; $bid = $_pf['building']['id']; }
\1    if(isset($_pf['netmeter'])) { $netmeter = true; $nid = $_pf['netmeter']['id']; }
\1    if(isset($_pf['homeowner'])) { $homeowner = true; $hid = $_pf['homeowner']['id']; }
\1}
"""

CACHE_CALL_REPLACEMENT = r"""\1$_fc = $this->_build_file_cache($jobs);
\1$tppfile = $_fc['pp'];
\1$tpmfile = $_fc['pm'];
\1$tpermitfile = $_fc['permit_exists'];
\1$tskfile = $_fc['sk'];
"""

def process_file(content, filename):
    """Apply all replacements to file content."""

    # Count occurrences before
    n1 = len(INIT_AND_GENRAL_PATTERN.findall(content))
    n2 = len(PRESALE_PERMIT_SKETCH_PATTERN.findall(content))
    n3 = len(PERMIT_SCAN_PATTERN.findall(content))
    print(f"  Found {n1} init+genral blocks, {n2} presale+permit+sketch blocks, {n3} permit scan blocks")

    # Step 1: Replace permit inner scan (do first since it doesn't affect other patterns)
    content = PERMIT_SCAN_PATTERN.sub(PERMIT_REPLACEMENT, content)

    # Step 2: Replace init + genral_files block (remove it entirely)
    content = INIT_AND_GENRAL_PATTERN.sub('', content)

    # Step 3: Replace presale+permit+sketch block with cache call
    content = PRESALE_PERMIT_SKETCH_PATTERN.sub(CACHE_CALL_REPLACEMENT, content)

    # Verify replacements
    n1_after = len(INIT_AND_GENRAL_PATTERN.findall(content))
    n2_after = len(PRESALE_PERMIT_SKETCH_PATTERN.findall(content))
    n3_after = len(PERMIT_SCAN_PATTERN.findall(content))
    print(f"  After: {n1_after} init+genral, {n2_after} presale+permit+sketch, {n3_after} permit scan")

    # Step 4: Insert helper method before _build_myjobs_cache
    content = content.replace(
        '    function _build_myjobs_cache($jobs) {',
        FILE_CACHE_HELPER + '    function _build_myjobs_cache($jobs) {'
    )

    return content


if __name__ == '__main__':
    base = '/var/www/html/dev_scheduler/SCHEDULER/system/application/controllers/'

    for fname in ['admin.php', 'assign.php']:
        print(f"\nProcessing {fname}...")
        content = read_file(base + fname)
        orig_lines = content.count('\n')
        print(f"  Original: {orig_lines} lines")

        content = process_file(content, fname)

        new_lines = content.count('\n')
        print(f"  Modified: {new_lines} lines (delta: {new_lines - orig_lines})")
        write_file(base + fname, content)

    print("\nDone!")