import boto3
import re
from urllib.parse import urlparse
import snowflake.connector


def extract_error_text(ack_file):
    with open(ack_file, 'r') as file:
        xml_content = file.read()
        match = re.search(r'<ErrorText>(.*?)</ErrorText>', xml_content, re.DOTALL)
        if match:
            print(match.group(1))
        

boto3.setup_default_session(profile_name='bytedance')
s3 = boto3.client('s3')
sf_query = "SELECT step_function_execution_id, timestamp FROM facts.prod.ORCHARD_SOUND_RECORDING_DELIVERY_HISTORY WHERE service = 'TikTok (Audio Fingerprinting)' AND event_type = 'success' and timestamp > '2025-08-12 14:46:59.532'  ORDER BY timestamp ASC LIMIT 100"


def fetch_history():
    conn = snowflake.connector.connect(
        user='<username>',
        account='orchard',
        authenticator='externalbrowser'
    )
    sfn_ids = []
    for (sfn_id, ts) in conn.cursor().execute(sf_query):
        print(f'Step Function ID: {sfn_id}, Timestamp: {ts}')
        sfn_ids.append(sfn_id)
    
    list_bucket(sfn_ids)


def list_bucket(sfn_ids):
    print('listing bucket')
    acks = []
    PREFIX = 'resso-label-us-east-16/srr-new/reporting/ack'
    S3_BUCKET = 'bytedance-s3-va-projectm-upload'
    
    for sfn_id in sfn_ids:
        response = s3.list_objects_v2(
            Bucket=S3_BUCKET,
            Prefix=f'{PREFIX}/{sfn_id.strip()}/'
        )
        for item in response.get('Contents', []):
            key = item['Key']
            if 'ACK_' in key :
                head = s3.head_object(Bucket=S3_BUCKET,
                            Key=key)
                if head['ContentLength']>999:
                    ack_name = key.split('/').pop()
                    print(ack_name)
                    response = s3.download_file(S3_BUCKET, key, ack_name)
                    acks.append(ack_name)
    
    for ack in acks:
        with open(ack, 'r') as ack_file:
            xml = ack_file.read()
            match = re.search(r'<ErrorText>(.*?)</ErrorText>', xml, re.DOTALL)
            if match:
                change = match.group(1)
                print(change)
                


if __name__ == "__main__":
    fetch_history()