import mariadb

# Database connection credentials
# (reuse from create_scan_1db.py or update as needed)
db_config = {
    "user": "trasabilitate",
    "password": "Initial01!",
    "host": "localhost",
    "database": "trasabilitate"
}

try:
    conn = mariadb.connect(**db_config)
    cursor = conn.cursor()
    print("Connected to the database successfully!")

    # Create the scanfg_orders table (same structure as scan1_orders)
    create_table_query = """
    CREATE TABLE IF NOT EXISTS scanfg_orders (
        Id INT AUTO_INCREMENT PRIMARY KEY,
        operator_code VARCHAR(4) NOT NULL,
        CP_full_code VARCHAR(15) NOT NULL UNIQUE,
        OC1_code VARCHAR(4) NOT NULL,
        OC2_code VARCHAR(4) NOT NULL,
        CP_base_code VARCHAR(10) GENERATED ALWAYS AS (LEFT(CP_full_code, 10)) STORED,
        quality_code INT(3) NOT NULL,
        date DATE NOT NULL,
        time TIME NOT NULL,
        approved_quantity INT DEFAULT 0,
        rejected_quantity INT DEFAULT 0
    );
    """
    cursor.execute(create_table_query)
    print("Table 'scanfg_orders' created successfully!")

    conn.commit()
    cursor.close()
    conn.close()

except mariadb.Error as e:
    print(f"Error connecting to the database: {e}")