import csv
import sys
import re

class GuestRecord:
    def __init__(self, guest_id, last_name, first_name, patronymic, organization, comment, mobile, email, birth_date, photo=None, analytics=None):
        self.guest_id = guest_id
        self.last_name = last_name
        self.first_name = first_name
        self.patronymic = patronymic
        self.organization = organization
        self.comment = comment
        self.mobile = mobile
        self.email = email
        self.birth_date = birth_date
        self.photo = photo
        self.analytics = analytics

def main():
    if len(sys.argv) < 3:
        print("Usage: python script.py <input_csv> <output_sql>")
        return 1

    try:
        with open(sys.argv[1], mode="r", encoding='utf-8') as f:
            reader = csv.reader(f, delimiter=',', quotechar='"')
            rows = list(reader)
    except Exception as e:
        print("Ошибка открытия или чтения CSV:", e)
        return 1

    try:
        with open(sys.argv[2], mode="w", encoding='utf-8') as dump_file:
            dump_file.write("""
            CREATE TABLE evi.guest
            (
                guest_id text COLLATE pg_catalog."default" NOT NULL,
                last_name text COLLATE pg_catalog."default" NOT NULL,
                first_name text COLLATE pg_catalog."default" NOT NULL,
                patronymic text COLLATE pg_catalog."default" NOT NULL,
                organization text COLLATE pg_catalog."default" NOT NULL,
                comment text COLLATE pg_catalog."default" NOT NULL,
                mobile text COLLATE pg_catalog."default",
                email text COLLATE pg_catalog."default",
                birth_date text COLLATE pg_catalog."default"
            );
            \n""")

            dump_file.write("""
            CREATE TABLE IF NOT EXISTS evi.guest_photo
            (
                guest_id text COLLATE pg_catalog."default" NOT NULL,
                photo_id text COLLATE pg_catalog."default" NOT NULL
            );
            \n""")

            for cols in rows:
                if len(cols) < 9:
                    print("Недостаточно столбцов в строке:", cols)
                    return 1

                guest_id = cols[0]
                last_name = cols[1]
                first_name = cols[2]
                patronymic = cols[3]
                organization = cols[4]
                comment_field = cols[5] if len(cols) > 5 else ''
                mobile = cols[6] if len(cols) > 6 and cols[6] != '' else None
                email = cols[7] if len(cols) > 7 and cols[7] != '' else None
                birth_date = cols[8] if len(cols) > 8 and cols[8] != '' else None
                photo = cols[9] if len(cols) > 9 and cols[9] != '' else None

                # извлечение комментария из "[]"
                match = re.match(r'^\[(.*)\]$', comment_field, re.DOTALL)
                comment = match.group(1) if match else comment_field

                record = GuestRecord(
                    guest_id,
                    last_name,
                    first_name,
                    patronymic,
                    organization,
                    comment,
                    mobile,
                    email,
                    birth_date,
                    photo
                )

                def escape_sql_str(s):
                    if s is not None:
                        return "'" + s.replace("'", "''") + "'"
                    else:
                        return "NULL"

                safe_comment = escape_sql_str(record.comment)
                safe_last_name = escape_sql_str(record.last_name)
                safe_first_name = escape_sql_str(record.first_name)
                safe_patronymic = escape_sql_str(record.patronymic)
                safe_organization = escape_sql_str(record.organization)
                mobile_str = escape_sql_str(record.mobile)
                email_str = escape_sql_str(record.email)
                birth_date_str = escape_sql_str(record.birth_date)

                dump_file.write(f"""INSERT INTO evi.guest (guest_id, last_name, first_name, patronymic, organization, comment, mobile, email, birth_date)
                    VALUES ({escape_sql_str(record.guest_id)}, {safe_last_name}, {safe_first_name}, {safe_patronymic}, {safe_organization},
                    {safe_comment}, {mobile_str}, {email_str}, {birth_date_str});\n""")

                if record.photo:
                    photo_str = escape_sql_str(record.photo)
                    guest_id_str = escape_sql_str(record.guest_id)
                    dump_file.write(f"""INSERT INTO evi.guest_photo (guest_id, photo_id)
                        VALUES ({guest_id_str}, {photo_str});\n""")
    except Exception as e:
        print("Ошибка записи в SQL файл:", e)
        return 1

    return 0

if __name__ == "__main__":
    sys.exit(main())