import csv
import re
import sys
import uuid

ADMIN_ROLE = "admin"


def esc(s):
    if s is None:
        return ""
    return str(s).replace("'", "''")

def load_csv(path, encoding="utf-8"):
    try:
        with open(path, "r", encoding=encoding) as f:
            return list(csv.reader(f))
    except FileNotFoundError:
        return []
    except Exception:
        return []

def process_views(views_rows):
    views_by_id = {}
    for row in views_rows:
        if len(row) >= 5:
            view_id = row[0].strip()
            x_s, y_s = row[2].strip(), row[3].strip()
            x_size = x_s if x_s else "0"
            y_size = y_s if y_s else "0"
            name = row[1].strip()

            comment_field = row[4] if len(row) > 4 else ""
            match = re.match(r'^\[(.*)\]$', comment_field, re.DOTALL)
            comment = match.group(1) if match else comment_field

            views_by_id[view_id] = (name, x_size, y_size, comment)
    return views_by_id

def process_users(user_rows):
    users = {}
    for row in user_rows:
        if len(row) == 2:
            user_id = row[0].strip()
            role = row[1].strip()

            if not user_id:
                continue

            users[user_id] = role
    return users

def process_admins(user_rows):
    admins = []
    for row in user_rows:
        if len(row) == 2:
            user_id = row[0].strip()
            role = row[1].strip()
            if role == ADMIN_ROLE:
                admins.append(user_id)
    return admins

def process_access(access_views_rows):
    access_by_view = {}
    for row in access_views_rows:
        if len(row) >= 2:
            view, user = row[0].strip(), row[1].strip()
            access_by_view.setdefault(view, []).append(user)
    return access_by_view

def process_cells(cell_views_rows):
    cells_by_view = {}
    for row in cell_views_rows:
        if len(row) >= 3:
            v = row[0].strip()
            merged_cells = ",".join(row[2:]).strip() if len(row) > 2 else ""
            cells_by_view.setdefault(v, []).append((row[1].strip(), merged_cells))
    return cells_by_view

def main():
    if len(sys.argv) < 6:
        print("Usage: python3 gen_access_view.py <access_views.csv> <views.csv> <cell_views.csv> <user.csv> <output.sql>", file=sys.stderr)
        return 1

    access_views_path = sys.argv[1]
    views_path = sys.argv[2]
    cell_views_path = sys.argv[3]
    user_path = sys.argv[4]
    output_path = sys.argv[5]

    access_views_rows = load_csv(access_views_path)
    views_rows = load_csv(views_path)
    cell_views_rows = load_csv(cell_views_path)
    user_rows = load_csv(user_path)

    views_by_id = process_views(views_rows)
    users = process_users(user_rows)
    admin_users = process_admins(user_rows)
    access_by_view = process_access(access_views_rows)
    cells_by_view = process_cells(cell_views_rows)

    dump_file = open(output_path, "w", encoding="utf-8")

    dump_file.write("""CREATE TABLE IF NOT EXISTS evi.views (
        view_id text NOT NULL,
        name text NOT NULL,
        x_size integer NOT NULL,
        y_size integer NOT NULL,
        comment text NOT NULL,
        owner_id text REFERENCES evi."user" (user_id) ON DELETE CASCADE,
        CONSTRAINT views_pkey PRIMARY KEY (view_id)
    );\n\n""")

    dump_file.write("""CREATE TABLE IF NOT EXISTS evi.cell_views (
        view text REFERENCES evi.views (view_id) ON DELETE CASCADE,
        camera text REFERENCES evi.camera (camera_id) ON DELETE CASCADE,
        merged_cells integer[] NOT NULL
    );\n\n""")

    for view_id, (name, x_size, y_size, comment) in views_by_id.items():
        access_users = access_by_view.get(view_id, [])
        if len(access_users) != 0:
            for uid in access_users:
                if uid not in users:
                    continue
                role = users[uid]
                if role == ADMIN_ROLE:
                    continue

                new_view_id = str(uuid.uuid4())
                dump_file.write(
                    f"INSERT INTO evi.views (view_id, name, x_size, y_size, comment, owner_id) "
                    f"VALUES ('{esc(new_view_id)}', '{esc(name)}', {x_size}, {y_size}, '{esc(comment)}', '{esc(uid)}');\n"
                )

                for camera, merged_cells in cells_by_view.get(view_id, []):
                    camera_val = (f"'{esc(camera)}'" if (camera and camera.strip()) else "NULL")
                    dump_file.write(
                        f"INSERT INTO evi.cell_views (view, camera, merged_cells) "
                        f"VALUES ('{esc(new_view_id)}', {camera_val}, '{esc(merged_cells)}');\n"
                    )
        else:
            for uid in admin_users:
                new_view_id = str(uuid.uuid4())
                dump_file.write(
                    f"INSERT INTO evi.views (view_id, name, x_size, y_size, comment, owner_id) "
                    f"VALUES ('{esc(new_view_id)}', '{esc(name)}', {x_size}, {y_size}, '{esc(comment)}', '{esc(uid)}');\n"
                )

                for camera, merged_cells in cells_by_view.get(view_id, []):
                    camera_val = (f"'{esc(camera)}'" if (camera and camera.strip()) else "NULL")
                    dump_file.write(
                        f"INSERT INTO evi.cell_views (view, camera, merged_cells) "
                        f"VALUES ('{esc(new_view_id)}', {camera_val}, '{esc(merged_cells)}');\n"
                    )

    dump_file.close()
    return 0

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