import re
import csv
import sys
import os
import shutil
import oracledb

csv.field_size_limit(sys.maxsize)

SQL_FILE = "sisvietnamvn_Trang chính thức hiện tại/Database/sisvietnam_db.sql"
UPLOADS_DIR = "sisvietnamvn_Trang chính thức hiện tại/sisvietnam.vn/DocumentRoot/wp-content/uploads"
STATIC_UPLOAD = "sisvietnamvn_main/src/main/resources/static/upload"

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 = {r['ID']: r for r in parse_sql_dump(SQL_FILE, "sis_posts")}
postmeta = parse_sql_dump(SQL_FILE, "sis_postmeta")
terms = {r['term_id']: r for r in parse_sql_dump(SQL_FILE, "sis_terms")}
term_taxonomy = {r['term_taxonomy_id']: r for r in parse_sql_dump(SQL_FILE, "sis_term_taxonomy")}
term_relationships = parse_sql_dump(SQL_FILE, "sis_term_relationships")

meta_dict = {}
for m in postmeta:
    pid = m['post_id']
    if pid not in meta_dict: meta_dict[pid] = {}
    meta_dict[pid][m['meta_key']] = m['meta_value']

term_to_tax = {tx['term_id']: tx for tx in term_taxonomy.values()}

post_terms = {}
for rel in term_relationships:
    pid = rel['object_id']
    tax_id = rel['term_taxonomy_id']
    if tax_id in term_taxonomy:
        term_id = term_taxonomy[tax_id]['term_id']
        tax_type = term_taxonomy[tax_id]['taxonomy']
        if pid not in post_terms: post_terms[pid] = {}
        if tax_type not in post_terms[pid]: post_terms[pid][tax_type] = []
        if term_id in terms: post_terms[pid][tax_type].append(terms[term_id])

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

print("Migrating Categories...")
for t in terms.values():
    tid = t['term_id']
    if tid in term_to_tax and term_to_tax[tid]['taxonomy'] == 'category':
        cursor.execute("""
            MERGE INTO sis_category dest
            USING (SELECT :id as id, :name as name, :slug as slug, :description as description FROM dual) src
            ON (dest.slug = src.slug)
            WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.description = src.description
            WHEN NOT MATCHED THEN INSERT (id, name, slug, description, created_by, created_date, last_modified_by, last_modified_date) 
            VALUES (src.id, src.name, src.slug, src.description, 'system', sysdate, 'system', sysdate)
        """, id=tid, name=t['name'], slug=t['slug'], description=term_to_tax[tid]['description'])
conn.commit()

print("Migrating Attachments...")
for pid, p in posts.items():
    if p['post_type'] == 'attachment':
        guid = p.get('guid', '')
        if guid:
            file_name = guid.split('/')[-1]
            rel_path = guid.split('wp-content/uploads/')[-1] if 'wp-content/uploads/' in guid else file_name
            src = os.path.join(UPLOADS_DIR, rel_path)
            dst = os.path.join(STATIC_UPLOAD, rel_path)
            if os.path.exists(src):
                os.makedirs(os.path.dirname(dst), exist_ok=True)
                shutil.copy2(src, dst)
            
            cursor.execute("""
                MERGE INTO sis_media dest
                USING (SELECT :id as id, :name as original_filename, :stored as stored_filename, :url as file_url FROM dual) src
                ON (dest.id = src.id)
                WHEN MATCHED THEN UPDATE SET dest.original_filename = src.original_filename, dest.stored_filename = src.stored_filename, dest.file_url = src.file_url
                WHEN NOT MATCHED THEN INSERT (id, original_filename, stored_filename, file_url, media_type, created_by, created_date, last_modified_by, last_modified_date)
                VALUES (src.id, src.original_filename, src.stored_filename, src.file_url, 'IMAGE', 'system', sysdate, 'system', sysdate)
            """, id=pid, name=file_name, stored=file_name, url=f"/upload/{rel_path}")
conn.commit()

print("Migrating Posts...")
for pid, p in posts.items():
    if p['post_type'] == 'post' and p['post_status'] == 'publish':
        cat_id = None
        if pid in post_terms and 'category' in post_terms[pid]:
            # find mapped category id using slug
            slug = post_terms[pid]['category'][0]['slug']
            cursor.execute("SELECT id FROM sis_category WHERE slug = :slug", slug=slug)
            res = cursor.fetchone()
            if res: cat_id = res[0]
            
        thumb_id = meta_dict.get(pid, {}).get('_thumbnail_id')
        featured_image = ""
        if thumb_id and thumb_id in posts:
            guid = posts[thumb_id].get('guid', '')
            if 'wp-content/uploads/' in guid:
                featured_image = "/upload/" + guid.split('wp-content/uploads/')[-1]
                
        # Limit content/excerpt sizes if needed, or rely on CLOB
        try:
            cursor.execute("""
                MERGE INTO sis_post dest
                USING (SELECT :id as id, :title as title, :slug as slug, :content as content, :excerpt as excerpt, :img as img, :cat as cat FROM dual) src
                ON (dest.id = src.id)
                WHEN MATCHED THEN UPDATE SET dest.title = src.title, dest.slug = src.slug, dest.content = src.content, dest.featured_image_url = src.img
                WHEN NOT MATCHED THEN INSERT (id, title, slug, content, excerpt, featured_image_url, category_id, status, created_by, created_date, last_modified_by, last_modified_date)
                VALUES (src.id, src.title, src.slug, src.content, src.excerpt, src.img, src.cat, 'PUBLISHED', 'system', sysdate, 'system', sysdate)
            """, id=pid, title=p['post_title'], slug=p['post_name'], content=p['post_content'], excerpt=p['post_excerpt'], img=featured_image, cat=cat_id)
        except Exception as e:
            # Duplicate slug error or something else, handle gracefully
            pass
conn.commit()

print("Migrating Doctors...")
for t in terms.values():
    tid = t['term_id']
    if tid in term_to_tax and term_to_tax[tid]['taxonomy'] == 'specialties':
        # we don't have slug on specialty right now, use ID merge
        cursor.execute("""
            MERGE INTO sis_specialty dest
            USING (SELECT :id as id, :name as name, :desc as description FROM dual) src
            ON (dest.id = src.id)
            WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.description = src.description
            WHEN NOT MATCHED THEN INSERT (id, name, description, created_by, created_date, last_modified_by, last_modified_date)
            VALUES (src.id, src.name, src.desc, 'system', sysdate, 'system', sysdate)
        """, id=tid, name=t['name'], desc=term_to_tax[tid]['description'])
conn.commit()

for pid, p in posts.items():
    if p['post_type'] == 'employee' and p['post_status'] == 'publish':
        spec_id = None
        if pid in post_terms and 'specialties' in post_terms[pid]:
            spec_id = post_terms[pid]['specialties'][0]['term_id']
            
        m = meta_dict.get(pid, {})
        position = m.get('position', '')
        gender = m.get('gender', '')
        birthday = m.get('birthday', '')
        phone = m.get('phone_number', '')
        email = m.get('email', '')
        address = m.get('address', '')
        
        raw_title = p['post_title'].strip()
        parts = raw_title.split(' ', 1)
        title = parts[0] if '.' in parts[0] else ''
        name = parts[1] if title and len(parts) > 1 else raw_title
        if not title: name = raw_title
            
        thumb_id = meta_dict.get(pid, {}).get('_thumbnail_id')
        avatar = ""
        if thumb_id and thumb_id in posts:
            guid = posts[thumb_id].get('guid', '')
            if 'wp-content/uploads/' in guid:
                avatar = "/upload/" + guid.split('wp-content/uploads/')[-1]
                
        cursor.execute("""
            MERGE INTO sis_doctor dest
            USING (SELECT :id as id, :name as name, :title as title, :avatar as avatar, :pos as pos, :spec as spec FROM dual) src
            ON (dest.id = src.id)
            WHEN MATCHED THEN UPDATE SET dest.name = src.name, dest.title = src.title, dest.avatar_url = src.avatar, dest.position = src.pos, dest.specialty_id = src.spec
            WHEN NOT MATCHED THEN INSERT (id, name, title, avatar_url, position, specialty_id, active, created_by, created_date, last_modified_by, last_modified_date)
            VALUES (src.id, src.name, src.title, src.avatar, src.pos, src.spec, 1, 'system', sysdate, 'system', sysdate)
        """, id=pid, name=name, title=title, avatar=avatar, pos=position, spec=spec_id)
conn.commit()

print("Migration Complete!")
conn.close()