import sys
import re

class UserRecord:
    def __init__(self, user_id, login, password, comment, status, blocked, activated, role, last_contact):
        self.user_id = user_id
        self.login = login
        self.password = password
        self.comment = comment
        self.status = status
        self.blocked = blocked
        self.activated = activated
        self.role = role
        self.last_contact = last_contact


f = open(sys.argv[1], mode="r")

dump_file = open(sys.argv[2], mode="w")

rows = f.read().split("\n")

dump_file.write("""
CREATE TABLE evi.user (
    user_id      text                     NOT NULL,
    login        text                     NOT NULL,
    password     text                     NOT NULL,
    comment      text                     NOT NULL DEFAULT '',
    status       boolean                  NOT NULL DEFAULT false,
    blocked      boolean                  NOT NULL,
    activated    boolean                  NOT NULL DEFAULT false,
    last_contact timestamp with time zone,
    created_at   timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT user_pkey PRIMARY KEY (user_id),
    CONSTRAINT user_unique_login_cnt UNIQUE (login)
);
\n""")

dump_file.write("""
CREATE TABLE evi.role (
    role_id    text                     NOT NULL,
    name       text                     NOT NULL,
    comment    text,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT role_id_pkey PRIMARY KEY (role_id),
    CONSTRAINT role_unique_name_cnt UNIQUE (name)
);
\n""")

dump_file.write("""
CREATE TABLE evi.user_role (
    user_id text NOT NULL,
    role_id text NOT NULL,

    CONSTRAINT user_role_unique UNIQUE (user_id, role_id),
    CONSTRAINT user_role_user_fkey FOREIGN KEY (user_id) REFERENCES evi."user"(user_id) ON DELETE CASCADE,
    CONSTRAINT user_role_role_fkey FOREIGN KEY (role_id) REFERENCES evi."role"(role_id) ON DELETE CASCADE
);
\n""")

dump_file.write(f"""INSERT INTO evi.role (role_id, name)
                VALUES ('admin', 'admin'),
                       ('security', 'security');\n""")



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

pattern = re.compile(r'^\s*([^,]+)\s*,\s*([^,]+)\s*,\s*([^,]+)\s*,\s*"?\[(.*)\]"?\s*,\s*([^,]+)\s*,\s*([^,]+)\s*,\s*([^,]+)\s*,\s*([^,]+)\s*,\s*(.*)\s*$')


with open(sys.argv[1], mode="r", encoding='utf-8') as f:
    for row in f:
        row = row.strip()
        if not row:
            break

        match_row = pattern.match(row)
        if not match_row:

            print(f"Пропуск строки (неверный формат): {row}")
            break

        col = [c.strip() for c in match_row.groups()]
        last_contact_raw = col[8] if len(col) > 8 and col[8] != '' else None
        comment = col[3].replace('"', '')

        record = UserRecord(
            user_id=col[0],
            login=col[1],
            password=col[2],
            comment=col[3],
            status=col[4],
            blocked=col[5],
            activated=col[6],
            role=col[7],
            last_contact=last_contact_raw
        )

        dump_file.write(f"""INSERT INTO evi.user (user_id, login, password, comment, status, blocked, activated, last_contact)
VALUES ({escape_sql_str(record.user_id)}, {escape_sql_str(record.login)}, {escape_sql_str(record.password)}, {escape_sql_str(record.comment)}, '{record.status}', '{record.blocked}', '{record.activated}', {escape_sql_str(record.last_contact)});\n""")

        dump_file.write(f"""INSERT INTO evi.user_role (user_id, role_id)
VALUES ({escape_sql_str(record.user_id)}, {escape_sql_str(record.role)});\n""")