import re
import csv
import sys
import oracledb

csv.field_size_limit(sys.maxsize)

SQL_FILE = "sisvietnamvn_Trang chính thức hiện tại/Database/sisvietnam_db.sql"

def parse_sql_dump(filepath, table_name):
    rows = []
    columns = []
    in_insert = False
    with open(filepath, 'r', encoding='utf-8') as f:
        for line in f:
            if line.startswith(f"INSERT INTO `{table_name}`"):
                col_match = re.search(r'\((.*?)\)', line)
                if col_match:
                    columns = [c.strip('` ') for c in col_match.group(1).split(',')]
                in_insert = True
                continue
            if in_insert:
                if line.startswith('('):
                    val_str = line.strip().rstrip(',;')
                    if val_str.endswith(')'):
                        inner = val_str[1:-1].replace("\\'", "''").replace("\\n", "\n").replace("\\r", "\r").replace("\\t", "\t")
                        try:
                            for parsed_row in csv.reader([inner], quotechar="'", escapechar="\\", skipinitialspace=True):
                                if len(parsed_row) == len(columns):
                                    rows.append(dict(zip(columns, parsed_row)))
                        except Exception:
                            pass
                if line.strip().endswith(';'):
                    in_insert = False
    return rows

print("Loading data...")
posts = parse_sql_dump(SQL_FILE, "sis_posts")

conn = oracledb.connect(user='sisvietnam', password='sisvietnam', dsn='localhost:1521/sisvietnam')
cursor = conn.cursor()

print("Migrating Pages...")
count = 0
for p in posts:
    if p['post_type'] == 'page' and p['post_status'] == 'publish':
        pid = p['ID']
        title = p['post_title']
        slug = p['post_name']
        content = p['post_content']
        
        try:
            cursor.execute("""
                MERGE INTO sis_page dest
                USING (SELECT :id as id, :title as title, :slug as slug, :content as content FROM dual) src
                ON (dest.slug = src.slug)
                WHEN MATCHED THEN UPDATE SET dest.title = src.title, dest.content = src.content
                WHEN NOT MATCHED THEN INSERT (id, title, slug, content, status, page_type, layout, created_by, created_date, last_modified_by, last_modified_date)
                VALUES (src.id, src.title, src.slug, src.content, 'PUBLISHED', 'CUSTOM', 'STANDARD', 'system', sysdate, 'system', sysdate)
            """, id=pid, title=title, slug=slug, content=content)
            count += 1
        except Exception as e:
            print(f"Error inserting page {pid}: {e}")
            pass
            
conn.commit()
print(f"Migration Complete! Inserted/Updated {count} pages.")
conn.close()