import os
import re

BASELINE_SQL = r"c:\xampp\htdocs\SaaS_V1\backend\templates\hris_baseline.sql"
OUTPUT_SQL = r"c:\xampp\htdocs\SaaS_V1\backend\templates\hris_multitenant_baseline.sql"

def main():
    if not os.path.exists(BASELINE_SQL):
        print(f"Error: {BASELINE_SQL} not found.")
        return

    with open(BASELINE_SQL, 'r', encoding='utf-8') as f:
        content = f.read()

    # 1. Rename CREATE TABLE `table` to CREATE TABLE `_phys_table`
    # Also inject `tenant_id` INT NOT NULL, right after the opening parenthesis
    
    # Pattern to match: CREATE TABLE `table_name` (
    # Group 1: table name
    # Group 2: everything after ( up to end of line or next char
    
    # Let's extract all table names first
    table_names = re.findall(r"CREATE TABLE `([^`]+)`", content)
    
    # We will exclude certain tables if they are SaaS specific, but hris_baseline.sql is HRIS specific.
    # We rename all of them.
    
    modified_content = content
    
    for table in table_names:
        # Replace CREATE TABLE `table` (
        create_pattern = r"CREATE TABLE `" + re.escape(table) + r"` \("
        replacement = f"CREATE TABLE `_phys_{table}` (\n  `tenant_id` int(11) NOT NULL DEFAULT 0,"
        modified_content = re.sub(create_pattern, replacement, modified_content, count=1)
        
        # Replace ALTER TABLE `table`
        alter_pattern = r"ALTER TABLE `" + re.escape(table) + r"`"
        alter_replacement = f"ALTER TABLE `_phys_{table}`"
        modified_content = re.sub(alter_pattern, alter_replacement, modified_content)
        
        # Replace INSERT INTO `table`
        insert_pattern = r"INSERT INTO `" + re.escape(table) + r"`"
        insert_replacement = f"INSERT INTO `_phys_{table}`"
        modified_content = re.sub(insert_pattern, insert_replacement, modified_content)

        # Replace LOCK TABLES `table`
        lock_pattern = r"LOCK TABLES `" + re.escape(table) + r"`"
        lock_replacement = f"LOCK TABLES `_phys_{table}`"
        modified_content = re.sub(lock_pattern, lock_replacement, modified_content)

        # Replace DROP TABLE IF EXISTS `table`
        drop_pattern = r"DROP TABLE IF EXISTS `" + re.escape(table) + r"`"
        drop_replacement = f"DROP TABLE IF EXISTS `_phys_{table}`"
        modified_content = re.sub(drop_pattern, drop_replacement, modified_content)

        # Replace TRIGGER `trigger` ON `table`
        # Handle optional backticks and whitespace/newlines
        trigger_pattern = r"ON\s+`?" + re.escape(table) + r"`?\s+FOR EACH ROW"
        trigger_replacement = f"ON `_phys_{table}` FOR EACH ROW"
        modified_content = re.sub(trigger_pattern, trigger_replacement, modified_content, flags=re.MULTILINE)
        
        # Replace REFERENCES `table`
        ref_pattern = r"REFERENCES `" + re.escape(table) + r"`"
        ref_replacement = f"REFERENCES `_phys_{table}`"
        modified_content = re.sub(ref_pattern, ref_replacement, modified_content)

    # 1.5 Scope all UNIQUE KEY constraints to tenant_id
    # Match: UNIQUE KEY `key_name` (
    # Replace with: UNIQUE KEY `key_name` (`tenant_id`, 
    unique_pattern = r"UNIQUE KEY `([^`]+)` \("
    unique_replacement = r"UNIQUE KEY `\1` (`tenant_id`, "
    modified_content = re.sub(unique_pattern, unique_replacement, modified_content)

        
    # Append Views and Triggers at the end
    views_and_triggers = "\n\n-- ==========================================\n"
    views_and_triggers += "-- MULTI-TENANT VIEWS AND TRIGGERS\n"
    views_and_triggers += "-- ==========================================\n\n"
    
    views_and_triggers += "DELIMITER $$\n"
    views_and_triggers += "DROP FUNCTION IF EXISTS `get_current_tenant_id`$$\n"
    views_and_triggers += "CREATE FUNCTION `get_current_tenant_id`() RETURNS INT\n"
    views_and_triggers += "NO SQL\n"
    views_and_triggers += "BEGIN\n"
    views_and_triggers += "  RETURN @current_tenant_id;\n"
    views_and_triggers += "END$$\n"
    views_and_triggers += "DELIMITER ;\n\n"
    
    for table in table_names:
        # Create View
        views_and_triggers += f"CREATE OR REPLACE VIEW `{table}` AS SELECT * FROM `_phys_{table}` WHERE `tenant_id` = get_current_tenant_id();\n\n"
        
        # Create BEFORE INSERT Trigger
        views_and_triggers += f"DELIMITER $$\n"
        views_and_triggers += f"CREATE TRIGGER `before_insert_{table}_tenant` BEFORE INSERT ON `_phys_{table}`\n"
        views_and_triggers += f"FOR EACH ROW\n"
        views_and_triggers += f"BEGIN\n"
        views_and_triggers += f"  IF @current_tenant_id IS NOT NULL THEN\n"
        views_and_triggers += f"    SET NEW.tenant_id = @current_tenant_id;\n"
        views_and_triggers += f"  END IF;\n"
        views_and_triggers += f"END$$\n"
        views_and_triggers += f"DELIMITER ;\n\n"

    modified_content += views_and_triggers
    
    with open(OUTPUT_SQL, 'w', encoding='utf-8') as f:
        f.write(modified_content)
        
    print(f"Successfully generated {OUTPUT_SQL} with {len(table_names)} multi-tenant views and triggers.")

if __name__ == "__main__":
    main()