---

"""
Импортёр БД АВТОСЕРВИС в MySQL (Open Server) — весь SQL внутри скрипта.
Запуск: python import_db.py
"""
import sys
import pymysql

# Подключение БЕЗ выбора базы — её создаёт сам скрипт
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "",      # в Open Server у root пароль обычно пустой
    "charset": "utf8mb4",
}

DDL = [
    "CREATE DATABASE IF NOT EXISTS autoservice "
    "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
    "USE autoservice",

    "DROP TABLE IF EXISTS Orders",
    "DROP TABLE IF EXISTS MechanicWork",
    "DROP TABLE IF EXISTS Contracts",
    "DROP TABLE IF EXISTS Clients",
    "DROP TABLE IF EXISTS Works",
    "DROP TABLE IF EXISTS Mechanics",

    """CREATE TABLE Mechanics (
        passport     VARCHAR(20) PRIMARY KEY,
        last_name    VARCHAR(50) NOT NULL,
        first_name   VARCHAR(50) NOT NULL,
        middle_name  VARCHAR(50),
        address      VARCHAR(150),
        phone        VARCHAR(20)
    ) ENGINE=InnoDB""",

    """CREATE TABLE Contracts (
        contract_no  INT PRIMARY KEY,
        passport     VARCHAR(20) NOT NULL UNIQUE,
        sign_date    DATE NOT NULL,
        term_years   INT NOT NULL,
        terminated   TINYINT NOT NULL DEFAULT 0,
        term_date    DATE,
        FOREIGN KEY (passport) REFERENCES Mechanics(passport)
    ) ENGINE=InnoDB""",

    """CREATE TABLE Works (
        work_code    INT PRIMARY KEY,
        name         VARCHAR(100) NOT NULL,
        norm_hours   DECIMAL(6,2),
        cost         DECIMAL(10,2),
        price        DECIMAL(10,2),
        wage_fund    DECIMAL(10,2)
    ) ENGINE=InnoDB""",

    """CREATE TABLE MechanicWork (
        passport   VARCHAR(20) NOT NULL,
        work_code  INT NOT NULL,
        PRIMARY KEY (passport, work_code),
        FOREIGN KEY (passport)  REFERENCES Mechanics(passport),
        FOREIGN KEY (work_code) REFERENCES Works(work_code)
    ) ENGINE=InnoDB""",

    """CREATE TABLE Clients (
        client_id  INT AUTO_INCREMENT PRIMARY KEY,
        name       VARCHAR(150) NOT NULL,
        address    VARCHAR(150),
        phone      VARCHAR(20),
        car        VARCHAR(50),
        login      VARCHAR(50) UNIQUE,
        password   VARCHAR(50)
    ) ENGINE=InnoDB""",

    """CREATE TABLE Orders (
        order_no    INT PRIMARY KEY,
        client_id   INT NOT NULL,
        work_code   INT NOT NULL,
        order_date  DATE NOT NULL,
        done_date   DATE,
        qty         INT NOT NULL DEFAULT 1,
        FOREIGN KEY (client_id) REFERENCES Clients(client_id),
        FOREIGN KEY (work_code) REFERENCES Works(work_code)
    ) ENGINE=InnoDB""",
]

# Данные: (запрос, список кортежей)
DATA = [
    ("INSERT INTO Mechanics VALUES (%s,%s,%s,%s,%s,%s)", [
        ('4500111222', 'Иванов', 'Иван', 'Иванович', 'ул. Ленина, 1', '79001112233'),
        ('4500333444', 'Петров', 'Пётр', 'Петрович', 'ул. Мира, 5', '79004445566'),
    ]),
    ("INSERT INTO Contracts (contract_no,passport,sign_date,term_years,terminated,term_date) "
     "VALUES (%s,%s,%s,%s,%s,%s)", [
        (1, '4500111222', '2024-01-10', 2, 0, None),
        (2, '4500333444', '2024-03-01', 1, 0, None),
    ]),
    ("INSERT INTO Works VALUES (%s,%s,%s,%s,%s,%s)", [
        (101, 'Замена масла', 1.0, 300, 1000, 400),
        (102, 'Ремонт тормозов', 3.0, 1500, 4000, 1600),
        (103, 'Диагностика двигателя', 2.0, 800, 2500, 1000),
    ]),
    ("INSERT INTO MechanicWork VALUES (%s,%s)", [
        ('4500111222', 101), ('4500111222', 102),
        ('4500333444', 102), ('4500333444', 103),
    ]),
    ("INSERT INTO Clients (client_id,name,address,phone,car,login,password) "
     "VALUES (%s,%s,%s,%s,%s,%s,%s)", [
        (1, 'Сидоров Алексей', 'ул. Садовая, 7', '79007778899', 'Lada Vesta А123ВС', 'sidorov', '1234'),
        (2, 'ООО Логистика', 'пр. Победы, 20', '78005550000', 'ГАЗель Х777ОР', 'logistika', '5678'),
    ]),
    ("INSERT INTO Orders VALUES (%s,%s,%s,%s,%s,%s)", [
        (1, 1, 101, '2024-05-01', '2024-05-01', 1),
        (2, 1, 102, '2024-05-10', None, 1),
        (3, 2, 103, '2024-05-12', '2024-05-13', 2),
        (4, 2, 101, '2024-05-15', '2024-05-15', 3),
    ]),
]


def main():
    try:
        con = pymysql.connect(**DB_CONFIG)
    except pymysql.MySQLError as e:
        sys.exit(f"Нет подключения к MySQL. Запущен ли Open Server?\n{e}")

    try:
        with con.cursor() as cur:
            for stmt in DDL:
                cur.execute(stmt)
            for query, rows in DATA:
                cur.executemany(query, rows)
        con.commit()
        print("Готово. База autoservice создана и заполнена.")
    except pymysql.MySQLError as e:
        con.rollback()
        sys.exit(f"Ошибка при выполнении SQL:\n{e}")
    finally:
        con.close()


if __name__ == "__main__":
    main()