import os
import subprocess

# CONFIG
username = "avnadmin"
password = "password"
host = "host"
port = 12691
db = "1062testcustomers"
# END CONFIG

home = os.path.expanduser('~')
dir = f"{home}/mysql-test"
os.makedirs(dir, exist_ok=True)

# Create database and table script
with open(f'{dir}/1_create.sql', 'w') as file:
    file.write(f"""
DROP DATABASE IF EXISTS {db};
CREATE DATABASE {db};
USE {db};
CREATE TABLE "customers" (
"customerid" int unsigned NOT NULL,
"accesskey" varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"password" varchar(64) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"firstname" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"lastname" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"companyname" varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"billingaddress1" varchar(75) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"billingaddress2" varchar(75) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"city" varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"state" varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"postalcode" varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"country" varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"phonenumber" varchar(35) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"faxnumber" varchar(30) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"emailaddress" varchar(75) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"paysstatetax" varchar(3) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"taxid" varchar(64) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"emailsubscriber" varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"lastmodified" datetime DEFAULT NULL,
"websiteaddress" varchar(150) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"lastmodby" int unsigned DEFAULT NULL,
"customer_isanonymous" varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"issuperadmin" varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"news15" varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
"customer_notes" varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"salesrep_customerid" int unsigned DEFAULT NULL,
"id_customers_groups" int unsigned DEFAULT NULL,
"custom_field_custom2" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"custom_field_custom3" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"custom_field_custom4" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"custom_field_custom5" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"custom_field_commercegroup" varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
"vol_cid" int unsigned DEFAULT NULL,
"createddate" datetime DEFAULT NULL,
"created_at" timestamp NULL DEFAULT NULL,
"updated_at" timestamp NULL DEFAULT NULL,
PRIMARY KEY ("customerid"),
KEY "emailaddress" ("emailaddress"),
KEY "firstname" ("firstname"),
KEY "lastname" ("lastname"),
KEY "phonenumber" ("phonenumber"),
KEY "vol_cid" ("vol_cid"),
KEY "lastmodified" ("lastmodified"),
FULLTEXT KEY "firstnameFullText" ("firstname"),
FULLTEXT KEY "lastnameFullText" ("lastname"),
FULLTEXT KEY "emailaddressFullText" ("emailaddress"),
FULLTEXT KEY "customerFullText" ("firstname", "lastname", "emailaddress")
);
""")

# Alter table script
with open(f'{dir}/99_altertable.sql', 'w') as file:
    file.write(f"""
USE {db};
ALTER TABLE `customers` DROP INDEX `customerFullText`;
ALTER TABLE `customers` ADD FULLTEXT `customerFullText` (`firstname`, `lastname`, `emailaddress`);
""")

last = 0
# create 40 files with 100000 rows each script
for j in range(1, 41):
    with open(f'{dir}/50_test{j}.sql', 'w') as file:
        lines = []
        lines.append(f"USE {db};\n")
        lines.append("INSERT INTO customers (`customerid`, `firstname`, `lastname`, `emailaddress`) VALUES\n")
        m = last + 100000
        for i in range(last, m):
            comma = ',' if i != m - 1 else ''
            lines.append(f"({i}, UUID(), UUID(), UUID()){comma}\n")

        lines.append(";")
        file.writelines(lines)
        last = m

# run the scripts
files = sorted([file for file in os.listdir(dir) if os.path.isfile(os.path.join(dir, file)) and file.endswith(".sql")])
for file in files:
    print(f"Running {file} in mysql")
    with subprocess.Popen(f'/usr/bin/mysql -u {username} -p{password} -h {host} -P 12691 < {os.path.join(dir, file)}', shell=True,
        stdin=None, close_fds=True, text=True) as proc:
        proc.wait()